Unique (dynamic) or custom query?

Question

I’m developing a system and I’m trying to simplify my life with querys basics of insert and update, and I came across a question:

I must create a query for each case, or one that meets all the cases, where they have the same logic?

As querys that I developed are these:

//$nome_tabela  : nome da tabela sem o 'tb_'
//$info         : vetor ordenado de informações a serem inseridas          
//
//obs.: os indices do vetor devem ser o nome do respectivo campo
//      sem o '_tabela'
//
private function pdo_cadastro($nome_tabela, $info) {

    //montagem da query dinâmica
    $cont_param = 0;
    $sql = "INSERT INTO tb_$nome_tabela(";

    foreach ($info as $index => $key) {
        $sql .= $index . "_" . $nome_tabela . ", ";
        $cont_param += 1;
    }

    $sql = substr_replace($sql, "", -2);
    $sql .= ") VALUES (";

    for ($i = 0; $i < $cont_param; $i++) {
        $sql .= "?,";
    }

    $sql = substr_replace($sql, "", -1);
    $sql .= ")";
    //---------------------------------

    //execução da query
    try {
        $prepara = $this->pdo->prepare($sql);

        $controle = 1;
        foreach ($info as $index => $key) {
            $prepara->bindParam($controle, $info[$index], PDO::PARAM_INT);
            $controle += 1;
        }

        $prepara->execute();
    } catch (PDOException $e) {
        print "Error!: " . $e->getMessage() . "<br/>";
        die();
    }
}

//$nome_tabela  : nome da tabela sem o 'tb_'
//$info         : vetor ordenado de informações a serem inseridas          
//$campo_id     : nome do campo do código identificador
//
//obs.: os indices do vetor devem ser o nome do respectivo campo
//      sem o '_tabela'
//
private function pdo_edit($nome_tabela, $info, $campo_id) {

    //montagem da query dinâmica
    $cont_param = 0;
    $sql = "UPDATE tb_$nome_tabela SET ";

    foreach ($info as $index => $key) {
        if($index!==$campo_id){
            $sql .= $index . "_" . $nome_tabela . " = ?, ";
            $cont_param += 1;
        }
    }

    $sql = substr_replace($sql, "", -2);
    $sql .= " WHERE ". $campo_id . "_" . $nome_tabela . " = ?" ;
    //---------------------------------

    try {
        $prepara = $this->pdo->prepare($sql);

        $controle = 1;
        foreach ($info as $index => $key) {
            $prepara -> bindParam($controle, $info[$index], PDO::PARAM_INT);
            $controle += 1;
        }

        $prepara -> execute();

    } catch (PDOException $e) {
        print "Error!: " . $e -> getMessage() . "<br/>";
        die();
    }

}  

I inform everything I need at the call, telling you that the forms were built by following the names of the columns in DB. With this added the table name and the identification field (the id in my case e.g. id_cliente AUTO-INCREMENT PRIMARY), I can perform these actions only with these two functions.

Is that a good practice or should I actually make one for each case?

Answer 1

Friend, I don’t know if I understand very well what you want, but it seems that what you need is a class with persistence with the database that already has the predefined methods where you use it simply whenever you need.

If so, I advise you to research about "CRUD" which means (Create=INSERT, Read=SELECT, Update=UPDATE, Delete=DELETE), is nothing less than you create a class with all the methods needed to work with the database.

There are currently several options of ready-made tools that save you the trouble of calling ORM (Object Relational Mapping), I’ll list the ones I know (Doctrine ORM, Eloquent ORM, Adodb Active Record) but there is so much more than that and maybe you can find some that might please you, I say this because to build a relationship with the database consistently effectively requires a lot of work and dedication.

If you are interested in learning more about the subject, do a little more research on CRUD and ORM that will help you a lot.

In my learning era I started with something simpler to understand more about CRUD and the PDO class (because it has many very important security features that are worth learning).

Ex:

Configuration file: (Config.php)

/*
 * Definição do BD
 */
 define('HOST', 'localhost');
 define('USER', 'root');
 define('PASS', '');
 define('BD', 'data_bdTeste');
 define('DSN', 'mysql:host='.HOST.';dbname='.BD);

Connection File: (DBO.php)

class Dbo
{
/**
 * ---------------------------------
 *  Atributo de acesso à conexão
 *   estabelecida pelo sistema
 * ---------------------------------
 * @var $conn type arrayObject()
 */
static protected $conn;



/**
 * -------------------------------------------------
 * Método Construtor
 * responsável pela iniciação automática
 * após a instanciação ou herança da classe
 * -------------------------------------------------
 * @return $conn
 * @type arrayObject
 *
 */
public function __construct(){

    /**
     * verifica se a variável não está vazia
     *
     * Este método garante apenas uma conexão
     * com o banco de dados e nada mais!
     */
    if(is_null(self::$conn)){


        /**
         * Começa aqui a instanciação da Classe PDO
         * ------------------------------------------------------------------
         * ->Utilizamos aqui as constantes definidas no arquivo Config.php
         */
        self::$conn = new PDO(DSN, USER, PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES UTF8"));

        /**
         * Define para que o PDO lance exceções caso ocorra erros
         * Preferencial para tratamento de erros
         */
        self::$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    //retorna a variável com o valor da conexão estabelecida
    return self::$conn;
   }
 }

CRUD file: (CRUD.php)

class CRUD extends Dbo
{
public $sql;

//Método para a consulta na tabela
/**
 * [listar]
 * @param  [type] $tabela   string
 * @param  [type] $coluna   string
 * @param  [type] $condicao string
 * @return [type]           array
 */
public function listar($tabela, $coluna, $condicao){
    return $this->sql = Dbo::$conn->prepare("SELECT {$coluna} FROM {$tabela} {$condicao}");
}

//Método para inserir registros na tabela
/**
 * [inserir]
 * @param  [type] $tabela   string
 * @param  [type] $coluna   string
 * @param  [type] $condicao string
 * @return [type]           boleano
 */
public function inserir($tabela, $coluna, $condicao){
    return $this->sql = Dbo::$conn->prepare("INSERT INTO $tabela ($coluna) VALUES ($condicao)");
}

//Método para o Update de registros na tabela
/**
 * [atualizar]
 * @param  [type] $tabela   string
 * @param  [type] $coluna   string
 * @param  [type] $condicao string
 * @return [type]           boleano
 */
public function atualizar($tabela, $coluna, $condicao){
    return $this->sql = Dbo::$conn->prepare("UPDATE $tabela SET $coluna WHERE $condicao LIMIT 1");
}

//Método para excluir registros
/**
 * [excluir]
 * @param  [type] $tabela string
 * @param  [type] $coluna string
 * @return [type]         boleano
 */
public function excluir($tabela, $coluna){
    return $this->sql = Dbo::$conn->prepare("DELETE FROM $tabela WHERE $coluna LIMIT 1");
  }
}

Usage: (index.php)

include "Config.php";
include "DBO.php";
include "CRUD.php";

$crud = new CRUD;
//LISTAR
$crud->listar('table', '*', 'WHERE id=:id ORDER BY id');
$crud->sql->bindValue(':id', 3805, PDO::PARAM_INT);
$crud->sql->execute();
print_r($crud->sql->fetchAll(PDO::FETCH_OBJ));

//Inserir
$crud->inserir('table', "email", ":email");
$crud->sql->bindValue(':email', '[email protected]', \PDO::PARAM_STR);
var_dump($crud->sql->execute());

//Atualizar
$crud->atualizar('table', "email=:email", 'id=:id');
$crud->sql->bindValue(':id',3805, PDO::PARAM_INT);
$crud->sql->bindValue(':email', '[email protected]', \PDO::PARAM_STR);
var_dump($crud->sql->execute());

//Deletar
$crud->excluir('table', 'id=:id');
$crud->sql->bindValue(':id', 3805, PDO::PARAM_INT);
var_dump($crud->sql->execute());

Hugs!