select Pdo com oo

Question

I have this class that manages the database :

<?php
abstract class database{
    /*Método construtor do banco de dados*/
    private function __construct(){}

    /*Evita que a classe seja clonada*/
    private function __clone(){}

    /*Método que destroi a conexão com banco de dados e remove da memória todas as variáveis setadas*/
    public function __destruct() {
        $this->disconnect();
        foreach ($this as $key => $value) {
            unset($this->$key);
        }
    }

    private static $dbtype   = "mysql";
    private static $host     = "localhost";
    private static $port     = "3306";
    private static $user     = "root";
    private static $password = "";
    private static $db       = "PDO";

    /*Metodos que trazem o conteudo da variavel desejada
    @return   $xxx = conteudo da variavel solicitada*/
    private function getDBType()  {return self::$dbtype;}
    private function getHost()    {return self::$host;}
    private function getPort()    {return self::$port;}
    private function getUser()    {return self::$user;}
    private function getPassword(){return self::$password;}
    private function getDB()      {return self::$db;}

    private function connect(){
        try
        {
            $this->conexao = new PDO($this->getDBType().":host=".$this->getHost().";port=".$this->getPort().";dbname=".$this->getDB(), $this->getUser(), $this->getPassword());
        }
        catch (PDOException $i)
        {
            //se houver exceção, exibe
            die("Erro: <code>" . $i->getMessage() . "</code>");
        }

        return ($this->conexao);
    }

    private function disconnect(){
        $this->conexao = null;
    }

    /*Método select que retorna um VO ou um array de objetos*/
    public function selectDB($sql,$params=null,$class=null){
        $query=$this->connect()->prepare($sql);
        $query->execute($params);

        if(isset($class)){
            $rs = $query->fetchAll(PDO::FETCH_CLASS,$class) or die(print_r($query->errorInfo(), true));
        }else{
            $rs = $query->fetchAll(PDO::FETCH_OBJ) or die(print_r($query->errorInfo(), true));
        }
        self::__destruct();
        return $rs;
    }

    /*Método insert que insere valores no banco de dados e retorna o último id inserido*/
    public function insertDB($sql,$params=null){
        $conexao=$this->connect();
        $query=$conexao->prepare($sql);
        $query->execute($params);
        $rs = $conexao->lastInsertId() or die(print_r($query->errorInfo(), true));
        self::__destruct();
        return $rs;
    }

    /*Método update que altera valores do banco de dados e retorna o número de linhas afetadas*/
    public function updateDB($sql,$params=null){
        $query=$this->connect()->prepare($sql);
        $query->execute($params);
        $rs = $query->rowCount() or die(print_r($query->errorInfo(), true));
        self::__destruct();
        return $rs;
    }

    /*Método delete que excluí valores do banco de dados retorna o número de linhas afetadas*/
    public function deleteDB($sql,$params=null){
        $query=$this->connect()->prepare($sql);
        $query->execute($params);
        $rs = $query->rowCount() or die(print_r($query->errorInfo(), true));
        self::__destruct();
        return $rs;
    }
}
?>

I wonder how I can:

1. Perform a select with Where
2. access the value returned for a while or for
3. A function that returns the quantity returned to me as mysql_num_rows

Answer 1

Do it this way using the Book class as an example I created in another issue using this same database class:

class LivroDAO extends database {

  public function __construct(){}

  public function insertLivro($data)
  {

    $sql = "INSERT INTO `tablivro` (`id`, `titulo`, `autor`, `editora`, `anoedicao`, `localizacao`) VALUES (?, ?, ?, ?, ?, ?)";
    parent::insertDB($sql, $data);

  }

  public function deleteLivro($data)
  {

    //...

  }

  public function updateLivro($data)
  {

    //...

  }

  public function selectLivro($data)
  {

    $sql = "SELECT * FROM `tablivro` WHERE `id` = ? AND `titulo` = ? AND `anoedicao` = ?";
    $result = parent::selectDB($sql, $data);

    return $result;

  }

}

Test of use:

<?php

require_once 'database.php';
require_once 'LivroDAO.php';

$dadosLivro = array(
  '10',
  'Livro PHP',
  2015,
);

$livroDAO = new LivroDAO();

$result = $livroDAO->selectLivro($dadosLivro);

foreach($result as $r){
    var_dump($r);
}

foreach($result as $r){

    echo 'id: ' . $r->id . '<br>';
    echo 'titulo: ' . $r->titulo . '<br>';
    echo 'autor: ' . $r->autor . '<br>';
    echo 'editora: ' . $r->editora . '<br>';
    echo 'anoedicao: ' . $r->anoedicao . '<br>';
    echo 'localizacao: ' . $r->localizacao . '<br>';

}

//Quantidade de itens retornados
echo 'Quantidade de itens: ' . count($result);

Upshot:

object(stdClass)[4]
  public 'id' => string '10' (length=2)
  public 'titulo' => string 'Livro PHP' (length=9)
  public 'autor' => string 'João' (length=5)
  public 'editora' => string 'Novatec' (length=7)
  public 'anoedicao' => string '2015' (length=4)
  public 'localizacao' => string 'São Paulo' (length=10)

Quantidade de itens: 1

Answer 2

To get the number of rows returned I suggest you create a property $rowCount it will have changed value whenever a select is run.

What changes from a select with Where to a without is whether some argument will be given to execute() in that case an if resolves.

$public $rowCount;

function select($sql, $params=null, $class=null){
    $query = $this->conexao->prepare($sql);
    if($params){
        $query->execute($params);
    }else{
        $query->execute();
    }

    $this->rowCount = $query->rowCount();
    return $query->fetchAll();
}

How should the new code call.

$sql = "SELECT * FROM tabela WHERE c1 = ? AND c2 = ? AND c3 = ?";
$param = array(51, 'userName', 2015);
$registros = select($sql, $param);

foreach($registros as $item){
    echo $item['campo'] .'<br>';
}

Your class already has a property to store the connection (PDO object) avoid creating new ones without need.

public function selectDB($sql,$params=null,$class=null){
   $query=$this->connect()->prepare($sql);