mysqli or PDO - what are the pros and cons? [closed]

Question

Well, you could argue with the object oriented aspect, the prepared statements, the fact that it becomes a standard, etc. But I know that most of the time, convincing somebody works better with a killer feature. So there it is:

A really nice thing with PDO is you can fetch the data, injecting it automatically in an object. If you don't want to use an ORM (cause it's a just a quick script) but you do like object mapping, it's REALLY cool :

class Student {

    public $id;
    public $first_name;
    public $last_name

    public function getFullName() {
        return $this->first_name.' '.$this->last_name
    }
}

try 
{
    $dbh = new PDO("mysql:host=$hostname;dbname=school", $username, $password)

    $stmt = $dbh->query("SELECT * FROM students");

    /* MAGIC HAPPENS HERE */

    $stmt->setFetchMode(PDO::FETCH_INTO, new Student);


    foreach($stmt as $student)
    {
        echo $student->getFullName().'<br />';
    } 

    $dbh = null;
}
catch(PDOException $e)
{
    echo $e->getMessage();
}

Moving an application from one database to another isn't very common, but sooner or later you may find yourself working on another project using a different RDBMS. If you're at home with PDO then there will at least be one thing less to learn at that point.

Apart from that I find the PDO API a little more intuitive, and it feels more truly object oriented. mysqli feels like it is just a procedural API that has been objectified, if you know what I mean. In short, I find PDO easier to work with, but that is of course subjective.

I've started using PDO because the statement support is better, in my opinion. I'm using an ActiveRecord-esque data-access layer, and it's much easier to implement dynamically generated statements. MySQLi's parameter binding must be done in a single function/method call, so if you don't know until runtime how many parameters you'd like to bind, you're forced to use call_user_func_array() (I believe that's the right function name) for selects. And forget about simple dynamic result binding.

Most of all, I like PDO because it's a very reasonable level of abstraction. It's easy to use it in completely abstracted systems where you don't want to write SQL, but it also makes it easy to use a more optimized, pure query type of system, or to mix-and-match the two.

PDO is the standard, it's what most developers will expect to use. mysqli was essentially a bespoke solution to a particular problem, but it has all the problems of the other DBMS-specific libraries. PDO is where all the hard work and clever thinking will go.

Here's something else to keep in mind: For now (PHP 5.2) the PDO library is buggy. It's full of strange bugs. For example: before storing a PDOStatement in a variable, the variable should be unset() to avoid a ton of bugs. Most of these have been fixed in PHP 5.3 and they will be released in early 2009 in PHP 5.3 which will probably have many other bugs. You should focus on using PDO for PHP 6.1 if you want a stable release and using PDO for PHP 5.3 if you want to help the community.

Another notable (good) difference about PDO is that it's PDO::quote() method automatically adds the enclosing quotes, whereas mysqli::real_escape_string() (and similars) don't:

PDO::quote() places quotes around the input string (if required) and escapes special characters within the input string, using a quoting style appropriate to the underlying driver.

PDO will make it a lot easier to scale if your site/web app gets really being as you can daily set up Master and slave connections to distribute the load across the database, plus PHP is heading towards moving to PDO as a standard.

PDO Info

Scaling a Web Application