Simple Pdo Wrapper

Simple PDO wrapper

How about creating a class with methods that you can chain (for clarity, I've removed error checking):

class DB {

    private $dbh;
    private $stmt;

    public function __construct($user, $pass, $dbname) {
        $this->dbh = new PDO(
            "mysql:host=localhost;dbname=$dbname",
            $user,
            $pass,
            array( PDO::ATTR_PERSISTENT => true )
        );
    }

    public function query($query) {
        $this->stmt = $this->dbh->prepare($query);
        return $this;
    }

    public function bind($pos, $value, $type = null) {

        if( is_null($type) ) {
            switch( true ) {
                case is_int($value):
                    $type = PDO::PARAM_INT;
                    break;
                case is_bool($value):
                    $type = PDO::PARAM_BOOL;
                    break;
                case is_null($value):
                    $type = PDO::PARAM_NULL;
                    break;
                default:
                    $type = PDO::PARAM_STR;
            }
        }

        $this->stmt->bindValue($pos, $value, $type);
        return $this;
    }

    public function execute() {
        return $this->stmt->execute();
    }

    public function resultset() {
        $this->execute();
        return $this->stmt->fetchAll();
    }

    public function single() {
        $this->execute();
        return $this->stmt->fetch();
    }
}

You can then use it like this:

// Establish a connection.
$db = new DB('user', 'password', 'database');

// Create query, bind values and return a single row.
$row = $db->query('SELECT col1, col2, col3 FROM mytable WHERE id > ? LIMIT ?')
   ->bind(1, 2)
   ->bind(2, 1)
   ->single();

// Update the LIMIT and get a resultset.
$db->bind(2,2);
$rs = $db->resultset();

// Create a new query, bind values and return a resultset.
$rs = $db->query('SELECT col1, col2, col3 FROM mytable WHERE col2 = ?')
   ->bind(1, 'abc')
   ->resultset();

// Update WHERE clause and return a resultset.
$db->bind(1, 'def');
$rs = $db->resultset();

You could alter the bind method to accept an array or associative array if you prefer, but I find this syntax quite clear - it avoids having to build an array. The parameter type checking is optional, as PDO::PARAM_STR works for most values, but be aware of potential issues when passing null values (see comment in PDOStatement->bindValue documentation).

Simple PDO Wrapper not working

Ok, now it works perfectly. And here is the code:

<?php // database.php
class DB extends PDO {

    protected $_connection = array();         // to map the connection

    protected $_dbh;                        // property which keeps the database handler

    protected $_database;                   // property to keep the database name

    protected $_table;                      // property to keep the table name

    protected $_query;                       // property to keep a query statement

    public function __construct() {

        // require the database configurations as an array from config.php
        $this->_connection = require_once 'config.php';
        // define which database driver to use (mysql?) and extract the values as variables
        extract($this->_connection['connection']['mysql']);

        // make the database and table name available to the class
        $this->_database = $database;
        $this->_table    = $table_name;

        // build the dsn string value for the PDO connection
        $dsn = $driver.":host=".$host.";dbname=".$database;

        // Check for PDO driver and create the connection to the database.
        try {
            parent::__construct($dsn, $username, $password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
        } catch (PDOException $e) {
            die ('Connection failed: ' . $e->getMessage());
        }
    }

    public function input_query($query) {
        $this->_query = parent::prepare($query);
        return $this;
    }

    public function execute() {
        return $this->_query->execute();
    }

    public function single() {
        $this->execute();
        return $this->_query->fetch();
    }

    public function result_set() {
        $this->execute();
        return $this->_query->fetchAll();
    }
}

And the index.php file:

<?php
require_once 'database.php';

$db = new DB();

$db->input_query("SELECT `name` FROM `names`");

try {
    $r = $db->result_set();
} catch (PDOException $e) {
    die ($e->getMessage());
}
var_dump($r);

And var_dump($r) gave me a whole bunch of names.

Thank you guys so much for your help!

How to wrapper PDO Class?

Your query() if the original call succeeds to $this->init->query doesn't return anything. But also if there is an exception and the exception isn't set (which doesn't make sense) then it will re-execute the query and return the result.

public function query($sqlStatement){
    if(isset($this->init)){
        try{
            return $this->init->query($sqlStatement);
        }catch(PDOException $e){
            array_push($this->error, $e->getCode());
            array_push($this->error, $e->getMessage());
            array_push($this->error, time());
            return false;
        }
    }
}

Not sure what you want to return if an exception happens, or if you want to re-raise some exception. But this returns false, which will cause problems with...

$rows = $aegis->query("SELECT `market` FROM `result`")->fetchAll();

as the fetchAll() will fail (it will be trying to call fetchAll() on false).

Alternative use

if ($result = $aegis->query("SELECT `market` FROM `result`") ) {
    $rows = $result->fetchAll();

    // Output data
}

How to return the value of execute in PDO wrapper

Since PDOStatement::execute returns true/false and your current run method is returning a PDOStatement on success and false on failure. I suggest checking prepare and execute are not false and return the PDOStatement on success or false otherwise, as is the case with PDO::prepare and PDO::query.

Functional Example https://3v4l.org/OnDdn

/**
 * @return PDOStatement|false
 */
public function run($sql, $args = [])
{
    if (!$args) {
        return $this->pdo->query($sql);
    }
    if ($stmt = $this->pdo->prepare($sql)) {
        if ($stmt->execute($args)) {
            return $stmt;
        }
    }

    return false; //either prepare or execute failed
}

$db = Database::instance();
var_dump($db->run('SELECT ?', ['foo', 'bar'])); //false

---

An alternative approach would be to store the last execute value in a property, for later retrieval.

Example https://3v4l.org/UbM1N

class Database
{

    protected $lastExecute;

   //...

    /**
     * @return PDOStatement|false
     */
    public function run($sql, $args = [])
    {
        if (!$args) {
            return $this->pdo->query($sql);
        }
        if ($stmt = $this->pdo->prepare($sql)) {
            $this->lastExecute = $stmt->execute($args);
        }

        return $stmt;
    }

    /**
     * @return null|bool
     */
    public function getLastExecute()
    {
       return $this->lastExecute;
    }
}

$db = Database::instance();
$db->run('SELECT ?', ['foo', 'bar']);
var_dump($db->getLastExecute()); //false

---

To address the best-practices comments below in regard to the issue of determining when PDO::execute method specifically fails from within the Database::run method by using Exception handling.

Please keep in mind Best-Practices are not about right or wrong, "they are simply recommended methods of writing code." Referring to an approach of programming that is commonly preferred in professional application development. Always use what works best for you, the environment you are developing for and your application requirements.

Generally speaking StackOverlow is not an appropriate place to discuss or evaluate an author's application of
best-practices. Those types of discussions or critiques should be reserved for CodeReview. StackOverflow is
intended to answer the author's specific
question, or provide a viable alternative method to accomplish what the user is asking for. Not infer the user has asked the wrong question.

To use exceptions you need to enable PDO::ERRMODE_EXCEPTION (see below Database class).

The issue with using try/catch with a PDO wrapper method, is that PDO will only throw a single Exception object PDOException, which does not provide you with the ability to determine which PDO method call specifically failed. Leaving you to read the PDOException::getMessage() or PDOException::getTrace(), to determine the cause.

A simple approach would be to check the PDOException::trace for the function name that caused the exception.

Functional Example (PHP 5.6+): https://3v4l.org/fDnBI

try {
   $db = Database::instance();
   var_dump($db->run('SELECT ?', ['foo', 'bar'])->fetch());
} catch(\PDOException $e) {
   if ('execute' === $e->getTrace()[0]['function']) {
       echo 'PDO::execute() failed';
       //Handle the execute exception
   }
   throw $e;
}

Please see the answer on PDO mysql: How to know if insert was
successful by Your
Common Sense for a more generalized approach to PDOException
handling.

The above approach prevents you from handling only specific exception types within the Database::run method, requiring you to use throw $e; after your conditional, when the exception is unexpected.

To account for this issue, another approach would be to create custom Exception classes. You can do this by extending the base PDOException class to be compliant with other exception handling methods or to catch any of them.

In order to catch any of the run specific exceptions, an empty interface can be used that is then implemented on the extended PDOException classes.

interface DatabaseRunException{}

Then create a new exception class for each of the specific PDO methods you would like to handle, that implements the DatabaseRunException interface.

class PDOPrepareException extends PDOException implements DatabaseRunException{}

class PDOExecuteException extends PDOException implements DatabaseRunException{}

class PDOQueryException extends PDOException implements DatabaseRunException{}

To use the custom exceptions to determine which PDO method failed, you need to handle the PDOException(s) within the Database::run() method and throw one of the custom exceptions.

I have removed certain portions for brevity, commented out things that would alter your current configuration, made some best-practices and optimization changes for PHP 5.6+.

class Database 
{

    private const OPTIONS = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        // PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
        // PDO::ATTR_EMULATE_PREPARES => false
    ];

    //...

    protected function __construct()
    {
        $this->pdo = new PDO($dsn, self::user, self::password, self::OPTIONS);
    }

    public static function instance()
    {
        if (null === self::$instance) {
            self::$instance = new self;
        }

        return self::$instance;
    }

    public function __call($method, $args)
    {
        //always ensure the desired method is callable!
        if (is_callable([$this->pdo, $method])) {
            //php 5.6+ variadic optimization (aka splat operator)
            return $this->pdo->$method(...$args);

            //php <= 5.5
            //return call_user_func_array(array($this->pdo, $method), $args);
        }
        throw new \BadMethodCallException(sprintf('Unknown method PDO::%s called!', $method));
    }

    public function run($sql, $args = [])
    {
        if (!$args) {
            try {
                return $this->query($sql);
            } catch(\PDOException $e) {
                 throw new \PDOQueryException($e->getMessage(), (int) $e->getCode(), $e);
            }
        }
        try {
            $stmt = $this->prepare($sql);
        } catch(\PDOException $e) {
            throw new \PDOPrepareException($e->getMessage(), (int) $e->getCode(), $e);
        }
        try {
            $stmt->execute($args);

            return $stmt;
        } catch(\PDOException $e) {
            throw new \PDOExecuteException($e->getMessage(), (int) $e->getCode(), $e);
        }

        throw new \LogicException('Unknown error occurred');
    }

}

Functional Example (PHP 5.6+): https://3v4l.org/8QoRF

You will now be able to handle each of the exceptions for any of the specific types.

try {
   $db = Database::instance();
   $db->run('SELECT ?', ['foo', 'bar']);
} catch(\PDOExecuteException $e) {
    echo 'PDO::execute() failed';
    //Handle the execute exception
    throw $e;
}

In PHP 7.1+ you can catch multiple exceptions.

try {
   $db = Database::instance();
   $db->run('SELECT ?', ['foo', 'bar']);
} catch(\PDOQueryException $e) {
    //Handle the query exception
    throw $e;
} catch(\PDOPrepareException $e) {
    //Handle the prepare exception
    throw $e;
} catch(\PDOExecuteException $e) {
    echo 'PDO::execute() failed';
    //Handle the execute exception
    throw $e;
}

In PHP <= 7.0 you can use the DatabaseRunException interface to catch and check the specific exceptions caused by Database::run() with instanceof to determine which exception was actually thrown.

try {
   $db = Database::instance();
   $db->run('SELECT ?', ['foo', 'bar']);
} catch(\DatabaseRunException $e) {
    if ($e instanceof \PDOQueryException) {
       //Handle the query exception
    } elseif ($e instanceof \PDOPrepareException) {
       //Handle the prepare exception
    } elseif ($e instanceof \PDOExecuteException) {
       echo 'PDO::execute() failed';
       //Handle the execute exception
    }
    throw $e;
}

As you can see, this increases the code complexity and will be up to you on determining what best suits your application needs.

It is important to note, that variables declared in the try section will not be declared if an exception occurs prior to the declaration.

try {
  throw new \Exception('FooBar');
  $foo = 'foo';
} catch(\Exception $e) {
   var_dump(isset($foo)); //false
}
var_dump(isset($foo)); //false

PDO wrapper class, bind dynamically?

Assuming you want to bind parameters.

You don't actually need to bind them as PDO has a very handy feature of feeding execute() with array of values.

Though after taking a second look at this class I see not much point in it. as it giving you no benefit over PDO...

As you can have the same workflow with raw PDO

$db = new PDO(...);
$stmt = $db->prepare("SELECT id FROM users WHERE email = ?");
$stmt->execute($array($email));
$row =  $stmt->fetch();

class Database {

    private $connect;
    private $query;
    private $stmt;

    public function __construct() {

        $opt = array(
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        );
        $dsn = "mysql:host=localhost;dbname=blog";
        $this->connect = new PDO(, "root", "root". $opt);
        $this->connect->setAttribute();

    }
    public function prepare($query) {
        return $this->connect->prepare($query);

    }
    public function execute($stmt, $params) {   
        return $stmt->execute($params);
    }
    public function single($stmt, $params) {
        $stmt->execute($params);
        return $stmt->fetch();
    }

}

used as follows

$db = new Database();
$stmt = $db->prepare("SELECT id FROM users WHERE email = ?");
$row =  $db->single($stmt, $array($email));

---

Related Topics