Single database instance

Question

I have a controller and need to access 2 or more tables from my database, ex:

 $userModel = loadModel('userModel');
   $userModel->setTable('users');
   $userModel->getAllUser();

---

   $outroModel = loadModel('outroModel');
   $outroModel->setTable('outros');
   $outroModel->getAllOutros();

Each being an object of this, classes that extend the Model, which connects to the database, Ex User:

class userModel extends Model {

    private $table;

    function __construct() {
        parent::__construct();
    }

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

    public function getTable() {
        return $this -> table;
    }

    public function getAllUsers() {

        $this -> setSql("SELECT * FROM {$this->getTable()}");
        $getAll = $this -> getAll();
        return $getAll;

    }

Model:

abstract class Model {

    protected $db;
    protected $sql;

    function __construct() {

        $options = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ, PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING);

        $dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME;
        try {
            $this -> db = new PDO($dsn, DB_USER, DB_PASS, $options);
            return $this -> db;

        } catch (exception $e) {
            echo $e -> getMessage();
        }

    }
....

In doing so, will I be opening more than one connection with the bank? If so, what is the best way to do this?

Answer 1

You end up creating two instances (or more) of the PDO class, in which case one would be sufficient for bank operations. There is a model and I will share, with Injection of Dependencies and layer DAL.

Interfaces

interface ConnectionInterface {
    public function Close();
    public function Connection();
}
interface ClienteInterface {
    public function setId($value);
    public function setNome($value);
    public function getId();
    public function getNome();
}
interface FornecedorInterface {
    public function setId($value);
    public function setRazaoSocial($value);
    public function getId();
    public function getRazaoSocial();
}

---

Implementing these Interfaces

class Connection implements ConnectionInterface {
    private $db;
    public function _construct(){
        $options = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ, PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING);
        $dsn = 'mysql:host=localhost;dbname=dbtest';
        $this->db = new PDO($dsn, 'root', 'senha', $options);
    }
    public function Close(){
        unset($this->db);
    }
    public function Connection(){
        return $this->db;
    }
}
class Cliente implements ClienteInterface {
    private $id;
    private $nome;
    public function __construct($id = 0, $nome = ''){
        $this->id = $id;
        $this->nome = $nome;
    }
    public function setId($value){
        $this->id = $value;
    }
    public function setNome($value){
        $this->nome = $value;
    }
    public function getId(){
        return $this->id;
    }
    public function getNome(){
        return $this->nome;
    }
}

class Fornecedor implements FornecedorInterface {
    private $id;
    private $razaoSocial;
    public function __construct($id = 0, $razaoSocial = ''){
        $this->id = $id;
        $this->razaoSocial = $razaoSocial;
    }
    public function setId($value){
        $this->id = $value;
    }
    public function setRazaoSocial($value){
        $this->razaoSocial = $value;
    }
    public function getId(){
        return $this->id;
    }
    public function getRazaoSocial(){
        return $this->razaoSocial;
    }
}

---

DAL

class DalCliente {
    private $Connection;
    public function _construct(ConnectionInterface $Connection){
        $this->Connection = $Connection;            
    }
    public function Insert(ClienteInterface $cliente){
        $sts = $this->Connection->prepare('INSERT INTO tbcliente(nome) values(?)');
        $sts->bindValue(1, $cliente->getNome(),PDO::PARAM_STR);
        $sts->execute();
        $cliente->setId($this->Connection->lastInsertId());         
        return $cliente;
    }
    public function Edit(ClienteInterface $cliente){
        $sts = $this->Connection->prepare('UPDATE tbcliente SET nome=? WHERE id=?');
        $sts->bindValue(1, $cliente->getNome(),PDO::PARAM_STR);
        $sts->bindValue(2, $cliente->getId(),PDO::PARAM_INT);
        $sts->execute();
    }
}

class DalFornecedor {
    private $Connection;
    public function _construct(ConnectionInterface $Connection){
        $this->Connection = $Connection;            
    }
    public function Insert(FornecedorInterface $fornecedor){
        $sts = $this->Connection->prepare('INSERT INTO tbfornecedor(nome) values(?)');
        $sts->bindValue(1, $fornecedor->getRazaoSocial(),PDO::PARAM_STR);
        $sts->execute();
        $cliente->setId($this->Connection->lastInsertId());         
        return $fornecedor;
    }
    public function Edit(FornecedorInterface $fornecedor){
        $sts = $this->Connection->prepare('UPDATE tbfornecedor SET razaosocial=? WHERE id=?');
        $sts->bindValue(1, $fornecedor->getRazaoSocial(),PDO::PARAM_STR);
        $sts->bindValue(2, $fornecedor->getId(),PDO::PARAM_INT);
        $sts->execute();
    }
}

---

How to use

Let’s say that you in a code block, or in a controller have to open two different DAL SQL, do:

$connection    = new Connection();
$dalcliente    = new DalCliente($connection);
$dalfornecedor = new DalFornecedor($connection);

---

Insert operation

$fornecedor = new Fornecedor(0, "Fornecedor 1");
$fornecedor = $dalfornecedor->Insert($fornecedor); // inserindo fornecedor

Right now you are injecting a Class that is responsible for connection with bank in two other classes, eliminating the duplication of instances, improving performance, standardizing its software to facilitate new implementations and corrections.

Folder organization

Folder Connection: Connection.php

DAL folder: DalCliente.php and DalFornecedor.php

Folder Interfaces: ConnectionInterface.php, ClienteInterface.php and FornecedorInterface.php

Pasta Poco: Cliente.php and Fornecedor.php

To call !!!

Create the files at the root and include what you need:

include 'Interfaces/ConnectionInterface.php';
include 'Connection/Connection.php';

include 'Interfaces/ClienteInterface.php';
include 'Interfaces/FornecedorInterface.php';
include 'Poco/Cliente.php';
include 'Poco/Fornecedor.php';

include 'Dal/DalCliente.php';
include 'Dal/DalFornecedor.php';

Answer 2

Just include the Finally and close the connection within this.