select pdo with oo

Question

select pdo with oo

Navigation

#1 by (4 votes)
#2 by (2 votes)

3

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'd like to know how I can:

Perform a select with Where

Access the returned value for a while or for

A function that returns the quantity returned as mysql_num_rows

php pdo

asked by anonymous 09.11.2015 / 13:18

2 answers

2

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

What changes from a select with where to a without is whether an argument will be given to execute() in this 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 be called.

$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 hold the connection (PDO object), avoid creating new ones without need.

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

09.11.2015 / 13:39

How to check if a value is present inside an array? Customize css link as url

score 4 · Accepted Answer

Do as follows using the BookData class as an example I created in another question 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 usage:

<?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);

Result:

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