Crud Energy with PDO

Question

The connection part is ready:

<?php

/*
* O padrão Singleton
*/

abstract class Conexao {

const USER = "root";
const PASS = "";

private static $instance = null;

private static function conectar() {

    try {   ##Tenta conectar ao banco se não funcionar cai no catch
        if (self::$instance == null): ##se não existir conexão com PDO receber a new   PDO() | a conexão
            $dsn = "mysql:host=localhost;dbname=phpoo";
            self::$instance = new PDO($dsn, self::USER, self::PASS);
        endif;
    } catch (PDOException $e) {
        echo "Erro: " . $e->getMessage(); #
    }
    return self::$instance; ## se ja exixtir uma conexão ele retorna a conexão
}

protected static function getDB() {
    return self::conectar();
} 

}

create an Abstract.php class (do the Generic CRUD here) with:

<?php

abstract class Abstrata extends Conexao {

protected function listar($where) {
    //implemente aqui seu código padrão para fazer update
    $db = parent::getDB();
}

protected function alterar($id, $data) {
     //implemente aqui seu código padrão para fazer update
    $db = parent::getDB();
}

protected function deletar($id) {
    //implemente aqui seu código padrão para fazer delete
    $db = parent::getDB();
}

protected function cadastrar($data) {
    //implemente aqui seu código padrão para fazer insert
    $db = parent::getDB();
    //outros códigos
}

}

And deposi only call external methods to the other classes. Will that way of implementing like this ?

Answer 1

Very sensible what you’re trying to do, but my first tip is: just do it for didactic reasons. Calm down, I already explain why, there are packages ready that do what you want and in a very good and tested way and with community support, for DBA Layer I use the Doctrine DBAL that I will teach to use here.

Installation

This is the installation using commiserate (recommend). Add the following dependency to your Composer.json and update its dependencies.

"require": {
        "doctrine/dbal": "2.3.4"
    },

Doctrine DBAL is already configured to work with the autoloader of Poser, so you don’t need to do ANYTHING else to install it. Easy doesn’t ?

Using

Now you have a DBA Layer installed, you just need to configure the connection data to be able to exit using.

Configuring connection

use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\DriverManager;

$config = new Configuration();

$connectionParams = array(
    'dbname' => 'mydb',
    'user' => 'user',
    'password' => 'secret',
    'host' => 'localhost',
    'driver' => 'pdo_mysql',
);

$conn = DriverManager::getConnection($connectionParams, $config);

Performing updates

$conn->delete('user', array('id' => 1));
// DELETE FROM user WHERE id = ? (1)

$conn->insert('user', array('username' => 'jwage'));
// INSERT INTO user (username) VALUES (?) (jwage)

$conn->update('user', array('username' => 'jwage'), array('id' => 1));
// UPDATE user (username) VALUES (?) WHERE id = ? (jwage, 1)

Conducting consultations

$statement = $conn->prepare('SELECT * FROM user');
$statement->execute();
$users = $statement->fetchAll();

Performing queries with query Builder

The Querybuilder is a very good feature, it ALMOST abstracts SQL, it makes its much easier to change base if there is such a need. Here are some examples:

$queryBuilder = $conn->createQueryBuilder();

$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->where('email = ?')
    ->setParameter(0, $userInputEmail)
;
//SELECT


$queryBuilder
    ->insert('users')
;
//INSERT

$queryBuilder
    ->update('users')
;
//UPDATE

$queryBuilder
    ->delete('users')
;
//DELETE

Everything here is in official documentation which is very good!

Answer 2

If you want to use PDO because it is safer, then stop now.

The benefits of using PDO are:

• Object-oriented interface to interact with the database (counterpoint the functions)
• Unified database interface (instead of using specific functions for each database, you always use the same classes with PDO)

If you don’t have the need to interact with more than one database type in these classes, don’t mess with them. In both PDO and functions there are mechanisms that you will have to use to escape the values of the fields.

SOURCE: http://php.net/manual/en/intro.pdo.php

What you are trying to implement is a Tabledatagateway(source 1, source 2).

There are several implementations you can base on.

But we will continue with its implementation.

One way is, let your connection class be concrete (not abstract) and the getDb method as public.

Afterward:

<?php

 //include classe Conexao    

class TableDataGateway
{

    protected $_table;  

    protected function getDb()
    {
        return Conexao::getDb();
    }

    public function __construct($table)
    {
        $this->_table = $table;
    }

    public function insert($data)
    {
       //implemente aqui seu código padrão para fazer insert
       $db = $this->getDb();
       //outros códigos
    }

    public function delete($id)
    {
       //implemente aqui seu código padrão para fazer delete
    }

    public function update($id, $data)
    {
        //implemente aqui seu código padrão para fazer update
    }

    public  function select($where)
    {
        //implemente aqui seu código padrão para fazer update
    }
}

If you only need the basics, instantiating a Tabledatagateway('name_da_table') should be enough for most cases. If you want to link more different select or Insert methods, you can extend the class.

It would be good if you researched autoloaders and dependency injection to make your code a little more modern.