PDO or MySQLi?

[NigelRel3]

I've seen a few posts which recommend PDO over MySQLi and after a bit of reading it seems that PDO mainly comes out better due to more database brand connectivity.

TBH - I have no real preference and have used MySQLi due to the fact I was connecting to MySQL.  I suppose I should have a go with PDO so at least I can use it proficiently and be able to work with any code that's thrown at me (or more likely I'm thrown at :-/)

One thing about the multi database connectivity that sort of puts me off though is that it is assumed that you simply change your database type and automagically everything works out of the box.  This doesn't take the different SQL dialects into account and could involve quite some work to make it work again.  So this sort of puts me off pushing this as an advantage!

Is there any real winner between the two - if I was to start a new project for a client, should I be going down one path or the other?

 

Other differences I'm aware of:

PDO has named parameters as well as positional, MySQLi uses only positional.

PDO uses client side prepares, MySQLi uses server side.

MySQLi passes binary client-server (for most things), PDO uses strings.

 

Any thoughts/suggestions/ideas would be helpful!

Thanks

 

[Jacques1]

I think there are several misunderstandings:

• PDO isn't meant to be a full database abstraction layer. If that's what you want, you'll need a very different tool like an object-relational mapper (which is much more complex and much heavier). The purpose of PDO is to replace the current zoo of vendor-specific database extensions with a single unified interface. So instead of having to learn ext/mysqli, ext/pgsql, ext/sqlite etc., you just have to learn one API. And while you cannot automagically switch from one vendor to another, it will be significantly easier than switching from one extension to another, because you only have to adjust the queries which use special features.
• PDO does use server-side prepared statements. Client-side emulation is only an option (which should usually be turned off for security reasons).
• I don't know what you mean by “binary client-server”. Both PDO and mysqli use the exact same low-level MySQL API.

The main benefit of PDO is that it's easier to use. It's a well-designed, very intuitive high-level interface. For example, a standard prepared statement only requires three simple steps: prepare(), execute() and a loop (or one of the fetch methods):

$productsStmt = $databaseConnection->prepare('
    SELECT
        product_id,
        title,
        description
    FROM
        products
    WHERE
        category = :category
');
$productsStmt->execute([
    'category' => $_GET['category'],
]);

foreach ($productsStmt as $product)
{
    // ...
}

mysqli requires five different steps which only makes sense to somebody who understands the underlying mechanism of prepared statements:

$productsStmt = $databaseConnection->prepare('
    SELECT
        product_id,
        title,
        description
    FROM
        products
    WHERE
        category = ?
');
$productsStmt->bind_param('i', $_GET['category']);
$productsStmt->execute();
$productsStmt->bind_result($product_id, $title, $description);

while ($productsStmt->fetch())
{
    // ...
}

It's even worse when you have to do more complex tasks like implementing a variable number of parameters (e. g. an IN list). With PDO, you just add them to the parameter array. With mysqli, you have to do reference gymnastics with call_user_func_array() -- or use the new and mostly unknown splat operator.

 

mysqli only makes sense when you need very special MySQL features which aren't available in PDO, and when you're willing to spend a lot of time reading the manual and learning how exactly the API works. For most people, neither is true.

[NigelRel3]

Thanks - that's the sort of thing I was after.... Just a few points

 

 

PDO isn't meant to be a full database abstraction layer

I think this is where some people get misled and think it is.  They know enough about it being able to connect to multiple database vendors but not that this is just part of the work involved.

 

 

PDO does use server-side prepared statements

Didn't know that, so will ensure that when I use it I make sure this is set properly.

 

 

I don't know what you mean by “binary client-server”. 

My understanding was that with MySQLi prepares, the result set fields where sent from the server to the client API using binary and using the correct data-types (as from http://php.net/manual/en/mysqli.quickstart.prepared-statements.php -  Result set values data types).  I thought PDO did this using ASCII and then converted the types at the client end.  Nothing huge, but IMHO still something that was worth knowing.

 

And finally...

 

 

... reading the manual and learning how exactly the API works. For most people, neither is true.

Which is where I think the real problem lies - unfortunately people seem to have this idea that anyone can write a web site using PHP.  This is probably true, but how many of those sites are either still running after real world exposure and even those still running are probably prime targets for hacking.

 

I will have to try and rebuild something from MySQLi to PDO and see how it goes.  Although I'm currently trying to use Laravel and it uses Eloquent as an ORM - not that impressed and have resorted to running straight forward SQL rather than trying to work out how to do table joins and aggregates.  My main goal was to understand the architecture of the framework and so RTFM of all it's components is a bit much for now.

[benanamen]

Just a comment on Laravel since you mentioned you are using it. I started learning it and there was just too much magic going on for my liking. In my apps I want to know exactly what is going on and only have the code it actually will use. In any framework you get everything including the kitchen since whether you will be using it or not. It has it's place and there are valid reasons you would want to use it though. And now, back on topic, stick with PDO. Understanding the ins and outs of why is good but all you need to know is that is what you should be using.

[Jacques1]

NigelRel3:

 

I don't think I've ever encountered anybody who thought that PDO would magically change their queries to a different dialect. On top of that, it's fairly rare for projects to suddenly switch to a different vendor, and even if that happens, the changes are usually small. How often do you need vendor-specific SQL extensions? So this seems to be a non-issue.

 

The binary protocol issues refer to the old libmysql driver which was replaced with mysqlnd a long time ago and has lots of other drawbacks. Nowadays, you have to forcefully compile it into your PHP installation, so I doubt this will affect the average user. mysqlnd properly returns the native types.

Edited April 4, 2017 by Jacques1

[Psycho]

The main benefit of PDO is that it's easier to use. It's a well-designed, very intuitive high-level interface.

 

This.

 

Last night I had to fix a site that was originally written using mysql_ and was breaking since the web server was updated to a newer version of PHP that did not support it. I *thought* it would be a simple task to just convert it to mysqli_ since, you know, I'd just have to add the letter i to all the function names. Ok, I didn't really think that. I typically use PDO in PHP projects, but I thought it would be easier to convert to mysqli_ and figure out any slight modification to parameters, function names, etc. In doing so, I rediscovered how un-intuitive mysqli_ is. I would have been better off rewriting everything using PDO.

 

The ability to use named placeholders in prepared statements and pass an associative array with indexes matching the placeholders is a huge benefit in readability and maintainability of the code, IMO.

[gizmola]

I agree with the things stated.  PDO is just better designed and easier to use.  If I use an ORM it's typically Doctrine2, and that depends and loosely wraps PDO.  AFAIK, laravel's db abstraction also depends on PDO.