To PDO or not?

[suhana]

Various discussions of late in a number of IRC channels has caused me to again examine the PDO interface however I am having difficulty in seeing what if any advantages there are over my current preference of the MySQLi interface.

 

Statements such as "you are using prepared statements of course?" are usually met on my side with a large dose of sighing .. and if I am foolish enough to ask the fatal "why?" the conversation deteriorates into a discussion about the pros and cons of preventing SQLi.

 

Now while I may only be a "fair" to "passable" programmer, I an more than fully aware of blocking this type of attack, which I hasten to add, I believe has nothing to do with SQL at all, and is simple a failure to sanitize user input correctly.

 

Again, with prepared statements there's the case for "well it's easier to use". Not sure I can agree with this. For one, he very fact of preparing a statement generates traffic to the database server unless I;m very much mistaken, and that in turn has the scope to kill one application I manage - which is already pushing my knowledge of scaling with MySQL.

 

Binding parameters? so:

 

INSERT INTO `table` (`f1`, `f2`, `f3`) VALUES (?, ?, ?)

 

followed by some lines of PHP to prepare, and bind the values is easier and/or more legible than a quick bit of sanitization of user input followed by:

 

$sql = sprintf("INSERT INTO `table` (`f1`, `f2`, `f3`) VALUES (NULL, %u, '%s')", $f2, $f3);
$res = mysql_query($sql, $conn);

 

Frankly, I'm not convinced. So what else does the PDO supply? OOPS - Well I favor procedural code however I do "nod" in the direction of classes and have sufficient strength in classes and the skill to link them correctly without issue.

 

Exceptions? Nothing really special here. Sure, I throw a few where I believe the application has reached a state that could cause a problem, and given the fact that there still appears to be some small unusual bugs in PDO, I'd rather use something that has a proven track record.

 

Speed? I've no idea on this one - if anybody has experience here on the comparison speed-wise between MySQL, MySQLi, and PDO/MySQL, I'd be delighted to see some benchmarks, comments etc.

 

"It's the done thing" Unfortunately I often hear this from people who run Windows servers, or small database - and by small I mean < 1 million rows. The MySQLi interface is for me handling 50 million rows without any headache, in a master/master (active/passive) + redundant r/o slave configuration.

 

"Using PDO makes it easy to change to different DBMS". Well that's a new one me. For one, I'd have to rewrite almost all queries, and probably a small chunk of the application to handle the differences, so sorry, but that argument fails.

 

So finally, I really have to ask: just why should I change to the PDO interface? What sort of problems am I liable to encounter? What sort of speed issues are there? Are there server-side issues I need to be aware off? (I already use mysql-proxy to permit certain tricks plus my applications are always read-write split aware).

[oni-kun]

You should only use something new if it fits to your progmatic style and will be efficient to your code and its performance, You're wanting to use a database abstaction layer, when you can talk to your SQL database directly with it's own functions?

 

If you're wanting to write a multi-dbm application then it would be in your best interest to use PDO, and its OO functionality, else it'd not.

[ignace]

"Using PDO makes it easy to change to different DBMS". Well that's a new one me. For one, I'd have to rewrite almost all queries, and probably a small chunk of the application to handle the differences, so sorry, but that argument fails.

 

The manual could told you that:

 

PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database  abstraction; it doesn't rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.

 

If you're using an older version of the mysql client libraries, PDO will emulate them for you.

 

Those are 2 real advantages of PDO over MySQLi. IMO Stick with MySQLi unless you want to distribute your software for which I would recommend Doctrine or Zend_Db.

[Zyx]

PDO is one of very few PHP extensions that are NOT clones of a C API fitted into PHP layout. It is one of very few REALLY designed extensions. It is one of very few fully objective extensions. It is one of very few extensions that are able to throw exceptions natively. This is how the whole PHP runtime library should look like. It's not about database features, but what we want PHP to be - a crappy language or modern, well-designed tool? For me, the answer is simple: I want PDO and I want more extensions like PDO.

 

Speed issue -> in benchmarks PDO seems to be slower, but remember that it has a built-in error reporting and other stuff that we actually must manually add to the old "mysql_" and "mysqli_" functions in a bigger application.