Class PHP / PDO

sql.php

<?php
    /*
    +--------------------------------+
    |           !IMPORANTE!          |
    | Classe sem muitos comentarios. |
    |  Se tiver dúvidas, ler sobre   |
    |    PDO (PHP Data Objects) e    |
    |      Prepared Statements       |
    +--------------------------------+
                     Anderson Araujo (CoderN)
                                    */

class Obj {
    private $db;
    private $query;
    private $table;
    private $fields;
    public $meta;

        function __construct($table, $id = NULL) {
            $this->table = $table;
            try {
                    $this->Connect();
                    $this->query = $this->db->prepare("SHOW COLUMNS FROM ".$table."");
                    $this->query->execute();
                    foreach($this->query->fetchAll() as $row) {
                        $this->{$row['Field']} = '';
                        $strpos                = strpos($row['Type'], '(');
                        $meta[$row['Field']]   = ($strpos) ? substr($row['Type'], 0, $strpos) : $metatype = $row['Type'];
                        $fields[]              = $row['Field'];
                        $this->meta            = $meta;
                    }
                    $this->fields = $fields;
                    if($id != NULL) {
                        self::Select($id);  
                    }   
                } catch (Exception $e) {
                    $e->getMessage();
            }
        }

        public function __get($var) {

            if (in_array($var, $this->fields)) {
                return $var;
            }
        }


       public function Connect() {
            try {
                $encode = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8');
                 $this->db = new PDO("mysql:host=localhost;dbname=trousses","root","",$encode);
            } catch(Exception $e) {
                echo $e->getMessage();
            }
        }


    public function Select($id = NULL, $where = NULL, $obj = false) {
        $this->Connect();
        if ($id == NULL) {
            if($where == NULL) {
                $this->query = $this->db->prepare("SELECT * FROM ".$this->table." WHERE ic_ativo = '' AND ic_excluido = '' ");
            } else {
                $this->query = $this->db->prepare("SELECT * FROM ".$this->table." WHERE ".$where."");
            }
            $this->query->execute();
            $retorna = $this->query->fetchAll();
            if($obj == false) {
                return $retorna; 
            } else {
                $i = 0;
                foreach($retorna as $result) {
                    foreach($this->fields as $campos) {
                         $this->$campos = $result[$campos];
                     }
                }
            }
        } else {
            $this->query = $this->db->prepare("SELECT * FROM ".$this->table." WHERE ".$this->fields[0]." = :id");
            $this->query->bindValue(':id', $id);
            $this->query->execute();
            $retorna = $this->query->fetchAll();
            $i = 0;
            foreach($retorna as $result) {
                foreach($this->fields as $campos) {
                    $this->$campos = $result[$campos];
                 }
            }
        }   
    }

    public function Insert($return = NULL) {
        $this->Connect();
        foreach($this->fields as $field) {
                $dados[$field] .= $this->$field;
         }
		 
        $cont = count($dados);
        $i = 0;
        foreach($dados as $campo => $valor) {
            $i++;
            if($i == $cont) {
                $campos .= $campo;
                $valores .= '"'.$valor.'"';
                $prepared .= ':'.$campo;
            } else {
                $campos .= $campo.',';
                $valores .= '"'.$valor.'",';
                $prepared .= ':'.$campo.',';
            }
        }
            $sql = 'INSERT INTO '.$this->table.' ('.$campos.') VALUES ('.$prepared.')';
            $this->query = $this->db->prepare($sql); 
            foreach($dados as $campox => $valorx) {
                $this->query->bindValue(":".$campox, "".$valorx.""); // 12 SQL Injection's? Não em um sistema com Prepared Statements!
            }
            $this->query->execute();
            if($return != NULL) {
                return $this->db->lastInsertId();
            }
    }

    public function Update() {
        $this->Connect();
        foreach($this->fields as $field) {
                $dados[$field] .= $this->$field;
         }
        $cont = count($dados);
        $i = 0;
        foreach($dados as $campo => $valor) {
            $i++;
            if($i == $cont) {
                $valores .= ''.$campo.' = :'.$campo.'';
            } else {
                $valores .= ''.$campo.' = :'.$campo.', ';
            }
        }
            $sql = 'UPDATE '.$this->table.' SET '.$valores.' WHERE '.$this->fields[0].' = '.$dados[$this->fields[0]].' '; // Montando a Query de Update
            $this->query = $this->db->prepare($sql);
            foreach($dados as $campox => $valorx) {
                $this->query->bindValue(":".$campox, "".$valorx."");
            }
            $this->query->execute();
    }

    public function Delete() {
        $this->Connect();
        foreach($this->fields as $field) {
            $dados[$field] .= $this->$field;
        }
        $sql = 'UPDATE '.$this->table.' SET ic_excluido = "" WHERE '.$this->fields[0].' = '.$dados[$this->fields[0]].'';
        $this->query = $this->db->prepare($sql);
        $this->query->execute();
    }

    public function specialQuery($query) {
        $this->Connect();
        $this->query = $this->db->prepare($query);
        $this->query->execute();
        return $this->query->fetchAll();
    }
    

    /* Por questão de segurança, algumas consultas são feitas separadamente */
    public function RealizaLogin($usuario, $senha, $admin = false) {
        $this->Connect();
   
        $sql = "SELECT * FROM ".$this->table." WHERE nm_usuario = :login AND cd_senha = :senha AND ic_ativo = ''";
      
        $this->query = $this->db->prepare($sql);
        $this->query->bindValue(':login', $usuario);
        $this->query->bindValue(':senha', $senha);
        $this->query->execute();
        $contagem = $this->query->rowCount();
        if($contagem == 0) {
            return false;
        } else {
            $return = $this->query->fetchAll();
            foreach($return as $r) {
                $admin = $r['id_usuario'];
            }

            return $admin;
        }
    }
}
?>