How to connect to Mysql only once, without having to instantiate every time?

Question

How do I connect to Mysql only once, without writing it again in every function?

Currently I do so:

class Site {
    var $driver;
    var $host;
    var $dbname;
    var $user;
    var $pass;

    public function __construct($driver, $host, $dbname, $user, $pass) {
        $this->driver = $driver;
        $this->host = $host;
        $this->dbname = $dbname;
        $this->user = $user;
        $this->pass = $pass;        
    }
    public function jazz() {
        $d = new PDO("$this->driver:host=$this->host;dbname=$this->dbname", "$this->user","$this->pass");
        $d->query('SELECT name, price FROM products ORDER BY id ASC');
    }
    public function buzz() {
        $c = new PDO("$this->driver:host=$this->host;dbname=$this->dbname", "$this->user","$this->pass");
        $c->query('SELECT name, age FROM clients ORDER BY name ASC');
    }
}

Answer 1

The ideal would be to break this code in two classes one only for the connection of the bank (if it changes the change would be only in one place) and another for the site, in the latter the builder would receive the connection in place of user, password, bank etc.

The keyword var was used in php4 to define various class members in php5 using access modifiers:

• private These members can only be accessed by the class.
• protected These members are visible in objects and decentedes(inheritance).
• Public Anyone can those attributes.

---

class Site {
    private $db;

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

    public function jazz() {
        return $this->db->query('SELECT name, price FROM products ORDER BY id ASC');
    }

    public function buzz() {
        return $this->db->query('SELECT name, age FROM clients ORDER BY name ASC');
    }
}

Answer 2

There are two possibilities to solve this problem, one of them being less appropriate from the point of view of Object Orientation but perhaps more usable for your particular case.

The properties of a class serve to store all that the class, Upon its object, may need during the Request in which it was instantiated.

If you have several methods that act as Wrappers for the repetitive part of the PDO and they all use the same object, instead of opening a connection for each method, you store the PDO instance in a class, thus:

class Site {

    private $conn;

    public function __construct($driver, $host, $dbname, $user, $pass) {
        $this -> conn = new PDO( /** ... */ );
    }

    public function jazz() {
        return $this -> conn -> query( /** ... */ );
    }
}

The second alternative is a little more complex, but much more timely from the point of view of Object Orientation because, in the ideal scenario, you should not restrict access to your data to just one database, let alone force the use of PDO.

In such cases, using a Design Pattern called Registry you have an Object-Oriented implementation of basically a large box, where you can save whatever you want to use later:

class Registry {

    private $registry = array();

    /**
     * Add/Set a Registry Entry value
     *
     * @param string $key
     *  Registry Key
     *
     * @param mixed $value
     *  Value to be stored
     *
     * @return Registry
     *  Registry Object (Fluent Interface)
     */
    public function set( $key, $value ) {

        $key = trim( $key );

        if( ! empty( $key ) ) {

            $this -> registry[ $key ] = $value;
        }

        return $this;
    }

    /**
     * Get a Registry Entry value
     *
     * @param string $key
     *  Registry Key
     *
     * @return mixed
     *  Registry Entry
     */
    public function get( $key ) {

        $key   = trim( $key );

        return ( array_key_exists( $key, $this -> registry ) ? $this -> registry[ $key ] : FALSE );
    }
}

However, alone, Registry does not solve the problem of the object’s state, which is precisely what concerns its problem.

Without solving this problem, you would see yourself:

• Duplicating code, as it should currently be doing
• Create a global variable and access it in the local scope of the methods used the keyword global or the superglobal array $GLOBALS, which is extremely wrong

To solve this "problem" of Registry, since it is not exactly a problem it does not solve that was not designed to solve, just integrate it to another Design Pattern, the Singleton.

Singleton is a pattern that aims to make the instance of a single object. No matter where in the code you invoke the object, it will always be the same, including with the same properties filled in the previous "levels".

With levels I refer to the increasing and Verical flow of the Application. The lower levels would be first instance files, such as index php. or in more elaborate systems Froncontroller and with higher levels, the Action/Page Controllers or the Models.

Singleton a very useful pattern, if used correctly.

The unrestrained use of this pattern in the past has given it a bad reputation among experienced programmers because when you abuse Singleton, you simply leave global variables "cute".

But allied to Registry, creating what we call the Singleton Registry, you have a large box accessible in any scope to store everything that needs to be used in any part of the code.

Let’s see how it looks:

/**
 * Registry Class
 *
 * @author        Bruno Augusto
 *
 * @copyright     Copyright (c) 2010 Next Studios
 * @license       http://creativecommons.org/licenses/by/3.0/   Attribution 3.0 Unported
 */
class Registry {

    /**
     * Registry Instance
     *
     * @staticvar Registry $_instance
     */
    private static $_instance;

    /**
     * Registry Storage
     *
     * @var array $registry
     */
    private $registry = array();

    /**
     * Enforcing Singleton. Disallow Cloning
     */
    private function __clone() {}

    /**
     * Enforcing Singleton. Disallow Direct Constructor
     */
    private function __construct() {}

    /**
     * Get Registry Instance
     *
     * @return Registry
     *  Registry Instance
     */
    public static function getInstance() {

        if( NULL === self::$_instance ) {

            self::$_instance = new Registry;
        }

        return self::$_instance;
    }

    /**
     * Add/Set a Registry Entry value
     *
     * @param string $key
     *  Registry Key
     *
     * @param mixed $value
     *  Value to be stored
     *
     * @return Next\Components\Registry
     *  Registry Object (Fluent Interface)
     */
    public function set( $key, $value ) {

        $key = trim( $key );

        if( ! empty( $key ) ) {

            $this -> registry[ $key ] = $value;
        }

        return $this;
    }

    /**
     * Get a Registry Entry value
     *
     * @param string $key
     *  Registry Key
     *
     * @return mixed
     *  Registry Entry
     */
    public function get( $key ) {

        $key   = trim( $key );

        return ( array_key_exists( $key, $this -> registry ) ? $this -> registry[ $key ] : FALSE );
    }
}

Realized the difference?

We block access to the object constructor, so it cannot be instantiated with the operator new, which is what makes the object instance change.

Extra Credit: To see a representation of the variation of an object instance, pass the instance as argument to spl_object_hash()

But to work with objects you need to instill it and you will yes instantiate the Registry class, but indirectly through the static method Registry::getInstance().

This method queries a static property and checks if there is an instance of the object stored in it. If it does not exist, it means that the registry is being used for the first time, then it is created.

At other times, at the higher levels, this instance already exists and instead of being created again, it is returned the way it is.

And by "the way you are," I mean whatever’s inside that big box, in your case, your connecting object, which you arrow through Registry:() and rescues through Registry::get(). Let’s see?

$link = new Site( /** ... */ );
Registry::getInstance() -> set( 'link', $link );

// Em alguma outra parte do código DEPOIS de adicionar o objeto à caixa

$link = Registry::get( 'link' );

$link -> prepare( '/** ... */' );

// ...

:D

Answer 3

For this to happen you would need to return the PDO object created directly by the class, try something like:

class DB{
    private static $conn = null;
    private static $tns = "Connection String";
    private static $db_username = '<USERNAME>';
    private static $db_password = '<SENHA>';

    private function __construct(){

    }

    public static function conn(){
        if(is_null(self::$conn)){
            try{
                self::$conn = new PDO("oci:dbname=".self::$tns,self::$db_username,self::$db_password);
            } catch(PDOException $e){
                self::$conn = die($e->getMessage());
            }
        }

        return self::$conn;
    }

    public function __destruct(){
        self::$conn = null;
    }
}

Note that in the Conn() method, it does a check to know whether the object is null or not, if it is not null, means that there is already an instantiated connection by the static class, so it returns the object itself, without instantiating a new class.