Database Connection with Static Method

There seems to be two main concerns that lead people to wrap PDO code in classes.

The first is a need to call the database once and reuse the database connection. For instance, you don't want to call the database inside each Model function as it will generate too many database connections, often more connections than your database server will allow.

The next is to prevent having to pass the database connection variable (e.g., $conn) to functions. This can be illustrated by example as the code ´select($table,$where)´ is preferred over ´select($conn,$table,$where)´.

There are several micro-frameworks / classes / libraries that does this for you. Some of the better include:

• https://github.com/auraphp/Aura.Sql - PDO wrapper
• http://www.imavex.com/php-pdo-wrapper-class/
• https://github.com/colshrapnel/safemysql - uses MySQLi
• https://github.com/Xeoncross/dbyte
• http://fluentpdo.com/ - https://github.com/lichtner/fluentpdo

Or - generally - how is this done?

Personally I have come to prefer using procedural code (with a global $dbh variable to hold the database connection) that use "almost" plain PDO calls.

To do this, I first build a db() function to hold the database connection:

function db($dsn=null)
{
    global $dbh;
    if( isset($dbh) ) {
        return $dbh;
    } else {
        $dbh = new PDO($dsn);
        $dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $dbh;
    }
}

Usage:

db("sqlite:articles.db");

$stmt = db()->prepare("INSERT INTO `article` (`id`,`title`,`body`) VALUES (:id, :title, :body)");
$stmt->execute([ ':id'=>$id, ':title'=>$title, ':body'=>$body ]);

Or:

$stmt = db()->prepare("SELECT * FROM `article` WHERE `id`=:id");
$stmt->execute([':id'=>542]);
$row = $stmt->fetch(PDO::FETCH_OBJ);
echo $row->id;
...

It even works within functions allowing me to create my own Model functions using a simple syntax:

db("sqlite:articles.db");

print_r( select_article("542") );

function select_article($id) {
    // Note that we use db() inside the function, without adding a $conn variable
    $stmt = db()->prepare("SELECT * FROM `article` WHERE `id`=:id AND published=1");
    $stmt->execute([':id'=>$id]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

function insert($table,$data) {
    ...
    $stmt = db()->prepare("INSERT INTO `article` (`id`,`title`,`body`) VALUES (:id, :title, :body)");
    $stmt->execute([ ':id'=>$id, ':title'=>$title, ':body'=>$body ]);
}

If you want to use a class in static way then you have to reconstruct all the properties and methods of that class to static. This is a class template your class should be, imho:

class Element
{
    protected static $db;

    public static function setConnection(PDO $pdo)
    {
        self::$db = $pdo;
    }

    public static function getElementByID($id)
    {
        // use self::$db to retrieve information
    }
}

// then you can use it directly without instantiate an instance
$element = Element::getElementByID($id);

Base on what you want to implicitly or explicitly call setConnection() to inject a database connection. You can use it explicitly like:

Element::setConnection($pdo);
$element = Element::getElementByID($id)

Or hiding all the setup by calling it implicitly (you may want to make use of singleton pattern in this by setting up a singleton Database class to provide Pdo instance to every class)

class Database
{
    protected static $db;

    private function __construct(){};

    public static function getPdo()
    {
        if (!$db) self::$db = new Pdo();
        return self::$db;
    }
}

class Element
{
    public static function getElementByID($id)
    {
        // all the code like above

        // call setConnection implicitly
        self::setConnection(Database::getPdo());
        // use self::$db to retrieve information
    }
}
// and just simply use
$element = Element::getElementByID($id);

you can also use Database::getPdo() every time a class need a connection, so the only one connection can be reused many time.

Regards,

I have coded a simple class you can add functions to that and you can also make it better and more efficient .

<?php
class Database
    {
        private $conn;
        private $handle;

        public static function __construct()
        {
        $dbhost = "localhost";
        $dbname = "pdo_test";
        $dbuser = "root";
        $dbpass = "";
        $charset = "utf8";

        /**
        * -database driver, host, db ( schema ) name and charset, as well as less frequently used port and unix_socket are going to DSN
        * -username and passwword are going to constructor
        * -al other options go to options array
        */

        $dsn = "mysql:host=$dbhost;dbname=$dbname;charset=$charset"; // DSN is a semicolon-delimited string consist of param = vale pairs.( DSN = Data Source Name )
        try
        {
            $this->conn = new PDO( $dsn, $dbuser, $dbpass, $opt );
            //echo "Database connection successful.<br />";
        }
        catch(PDOException $e)
        {
            echo "Error in database connection !!!<br />";
            echo $e->getMessage();
        }
        }

        public static function test($test)
        {
            echo "<br />".$test;
        }

        public  static function __destruct()
        {
            $this->conn = null;
            if(!$this->conn)
            {
        //  echo "<br />database disconnected !!";
            }
        }
    }

    Database::test("your query or table name and  condition according to your functions");