The last week I read a lot about the „new“ PDO lib and mysqli lib to decide what shall I use for my next project. This article is supposed to help those users who don’t know yet what they should use for there project and I’ll try to convince them to not use PDO. I also want to lose a few words about extending mysqli and prepared statements.

So here we are, PDO is an abstract layer which has one major ability, allow choosing different databases without changing the php-code. Another advantage is that PDO is programmed totally in OOP manner. It even has a build in iterator so you can walk through your results using foreach-style loop and it supports the prepared statements (even on databases which don’t support prepared statements) as well as the transactions. There are view nice features and I am sure there will be much more.

What about mysqli, well at first there is no much difference to the old mysql functionality but mysqli supports prepared statements as well and can be written in OOP. Anyways mysqli is just for mysql and it takes care for only the basic functionalities. No fancy stuff on it.

PDO can be really helpful for big/large enterprise systems, which have the habit to change the databases weekly^^. No seriously, they have reasons and they should indeed use there database abstraction layer. Your application will lose speed doing so. If you know right know there is no other database as mysql you will use right know or in the feature, don’t throw another abstract layer to your system. It is just nonsense overhead you will create.

So for those who have more then one database system, please leave a comment about your exp in multi database using PDO or not (or others).

I just want to encourage everyone not using PDO or any other database abstraction layers who doesn’t use more then one database.

I decided to use mysqli, but there are a few thinks which still bother me, it doesn’t support foreach syntax nor you can use a fetch_assoc() on prepared statements. But there is one nice thing, since mysqli has now an OOP interface you can just extend the base class and don’t have to build a whole wrapper class for it as you used to do for the old mysql functions.

If you want to extend mysqli, you should extend all three classes: MySQLi, MySQLi_Stmt, MySQLi_Result. Also you can add your own exception classes. E.g.:

class MySQLi_Exception
class MySQLiConnection_Exception extends MySQLi_Exception
class MySQLiQuery_Exception extends MySQLi_Exception

I’ll call my three MySQLi classes (of cause you can use you own names):

class DB extends MySQLi
class DBStmt extends MySQLi_Stmt
class DBQuery extends MySQLi_Result

In my project I always have only one connection to the database, and I want to be able to access this connection globally. Therefore, I usually made a wrapper class having a static method query(), but this can’t be done since MySQLi already defines the query() method as none static. So I just decided to use the singleton pattern for this problem.
I also want to be able to set my database connection options outside of the class, without actually establishing a connection to the database.
So here is what my DB (extended MySQLi class) looks like:

class DB extends MySQLi
{
 
    protected static $instance;
 
    protected static $options = array();
 
    private function __construct() {
 
 
        $o = self::$options;
 
        // turn of error reporting
        mysqli_report(MYSQLI_REPORT_OFF);
 
        // connect to database
        @parent::__construct(isset($o['host'])   ? $o['host']   : 'localhost',
                             isset($o['user'])   ? $o['user']   : 'root',
                             isset($o['pass'])   ? $o['pass']   : '',
                             isset($o['dbname']) ? $o['dbname'] : 'test',
                             isset($o['port'])   ? $o['port']   : 3306,
                             isset($o['sock'])   ? $o['sock']   : false );
 
        // check if a connection established
        if( mysqli_connect_errno() ) {
 
            throw new MySQLiConnection_Exception(mysqli_connect_error(), mysqli_connect_errno()); 
        }
    }
 
    public static function getInstance() {
 
        if( !self::$instance ) {
 
            self::$instance = new self(); 
        }
        return self::$instance;
    }
 
 
    public static function setOptions( array $opt ) {
 
        self::$options = array_merge(self::$options, $opt);
 
    }
 
}

To extend the MySQLi_Stmt class and the MySQLi_Result, we have to overwrite the MySQLi::query() and MySQLi::prepare() methods since both create instance of the MySQLi_Result and MySQLi_Stmt class.
Creating a new prepared statement class is quite simple, the __constructor is defined as MySQLi_Stmt::__construct(db_link, query ). Here is what the new method should look like:

    public function prepare($query) {
 
        $stmt = new DBStmt($this, $query);
        return $stmt;
    }

It is a tick more complicated for the result object since you can’t pass a query to it. You have to use the method MySQLi::real_query(query) and then build a new instance of MySQLi_Result yourself. Now passing the db_link to the instance, it will automatically fetch the last result set itself.
You can also add you own Exception here.

    public function query($query) {
 
        if( !$this->real_query($query) ) {// returns true or false
 
            throw new MySQLiQuery_Exception( $this->error, $this->errno );
        }
 
        $result = new DBQuery($this); // build you subclass
        return $result;  
    }

So this is what you class should look like now:

class DB extends MySQLi
{
 
    protected static $instance;
 
    protected static $options = array();
 
    private function __construct() {
 
 
        $o = self::$options;
 
        // turn of error reporting
        mysqli_report(MYSQLI_REPORT_OFF);
 
        // connect to database
        @parent::__construct(isset($o['host'])   ? $o['host']   : 'localhost',
                             isset($o['user'])   ? $o['user']   : 'root',
                             isset($o['pass'])   ? $o['pass']   : '',
                             isset($o['dbname']) ? $o['dbname'] : 'test',
                             isset($o['port'])   ? $o['port']   : 3306,
                             isset($o['sock'])   ? $o['sock']   : false );
 
        // check if a connection established
        if( mysqli_connect_errno() ) {
 
            throw new MySQLiConnection_Exception(mysqli_connect_error(), mysqli_connect_errno()); 
        }
    }
 
 
    public static function getInstance() {
 
        if( !self::$instance ) {
 
            self::$instance = new self(); 
        }
        return self::$instance;
    }
 
 
    public static function setOptions( array $opt ) {
 
        self::$options = array_merge(self::$options, $opt);
 
    }
 
    public function query($query) {
 
        if( !$this->real_query($query) ) {
 
            throw new MySQLiQuery_Exception( $this->error, $this->errno );
        }
 
        $result = new DBQuery($this);
        return $result;
 
    }
 
 
    public function prepare($query) {
 
        $stmt = new DBStmt($this, $query);
        return $stmt;
 
    }    
}

If you whish you can implement the Iterator and ArrayAccess interface to access the dataset like an array, or do what ever you wish to do. I will later add my classes and documented code here for downloading, but for now I hope I gave you an idea on how to use mysqli lib. Please let me know if you have any suggestions for me, I am willing to learn

class DBQuery extends MySQLi_Result implements ArrayAccess, Iterator
class DBStmt extends MySQLi_Stmt implements ArrayAccess, Iterator

A quick thought on prepared statements, what I like is that it totally reduce the risk of sql-injections and that you can write a nice query with only a few “?” or “:var” in it. But for a single request, prepared statements are slower. Furthermore I think prepared statements don’t get cached by mysql. So don’t use them to much, they can slow down your application.

I also want to mention some profiler numbers, using prepared statements, foreach loop, or the good old while(fetch()) loop. Those numbers have not been produced in any fair test environment, and I should note that I used my own fetch_assoc() for the prepared statement…

495ns - while($data = $sql->fetch)
1815ns - foreach
2718ns – prepared statement