Generic Crud with PDO

2

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 Abstrata.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 I just called external methods for the other classes. Is it any way to implement this?

    
asked by anonymous 02.09.2014 / 15:21

2 answers

3

Very sensible what you want to do, but my first tip is: just do it for didactic reasons. Calm, I already explained why there are ready-made packages that do what you want and in a very good and tested way and with community support, for DBA Layer I use #

Installation

This is the installation using composer (I recommend). Add the following dependency in your composer.json and update its dependencies.

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

DBAL Doctrine is already configured to work with the composer autoloader, so you do not have to do anything besides to install. Easy does not it?

Using

Now you have a DBA Layer installed, you just need to configure the connection data to be able to quit 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 queries

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

Querying with query builder

The QueryBuilder is a very good resource , it ALMOST abstracting SQL, this makes it much easier to switch 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 that is here can be found at the official documentation that It's very good!

    
03.09.2014 / 05:49
1

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

The benefits of using PDOs are:

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

If you do not have to interact with more than one type of database in these classes, do not touch it. Both in the PDO and in the functions there are mechanisms that you will have to use to escape the values of the fields.

SOURCE: link

What you are trying to implement is a TableDataGateway ( font 1 , font 2 ).

There are several implementations you can build on.

But let's continue with your implementation.

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

Then:

<?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 ('table_name ') should be enough for most cases. If you want to bind more select or insert different methods, you can extend the class.

It would be good to search for autoloaders and dependency injection to make your code a bit more modern.

    
02.09.2014 / 17:01