PDO v MySQL

PDO is considered to be a standard, that’s why you were advised to use it… MySQL was primarily a bespoke solution to a certain problem, but it has all the problems of the other DBMS-specific libraries. PDO is where you should implement all your clever thinking and hard work : http://net.tutsplus.com/tutorials/php/pdo-vs-mysqli-which-should-you-use/comment-page-1/#comments

Albeit a bit true, however if you replace Stored Proc with Query, the topic still fits. PDO is a data-accecss abstraction that really doesn’t work, and it isn’t PDO’s fault, it is because every database out there choose to customize their syntax with proprietary functions, so you don’t have LIMIT everywhere, nor TOP, nor NOW(), or GETDATE(), or whatever.

So what does PDO provide then? It provides performance enhancements IF you will be executing a similar query within the same request multiple times. If PDO could utilize those optimizations across requests, it would help tremendously (and if it does and I misunderstood that in the manual, oops).

However, I still argue MOST of that optimization should occur naturally within your database of choice. Not from the language (I related PDO to LINQ to SQL in .NET; yes, it is cool, but you are asking your language to take on overhead that the database should already be handling for you).

Fair enough. Though I wasn’t suggesting the debate end. It’s quite interesting.

I am not sure this is actually very accurate. Linq converts your code statement into proper syntax, depending on usage. LinqToSql for example would generate SQL, while LinqToXML would generate an xpath query, despite the inline code remaining the same. As far as I understand it, PDO doesn’t do this. The only real point of sameness is in how connection strings are handled. If there is significant changes in the way a backing storage medium works, some recoding of your statement may be required. Linq2Sql, in specific, is designed to generate SQL Server specific statements. PDO isn’t designed to generate SQL, much less be MySQL specific.

Sorry, I guess the only relation between them is that they both try and enhance performance on a language level that (in my opinion) is better suited on the database side by caching/compiling/optimizing queries as they are sent across the wire. I do agree that LINQ to SQL takes it one step further and helps separate the SQL syntax from your code (that is nice), but there is still overhead in the entire process that can be costly (or more costly) than writing a good set of abstraction classes.

Yes, beginners may have problems understanding this, however it’s a good exercise in learning how objects work :). But I must admit I am not a fan of PDO. I like the idea of a universal database API but the implementation is too complicated IMHO and PDO lacks many simple shortcut methods for frequent operations like fetching a single value, single row, etc. The end result is that I have to always extend PDO (or mysqli) with my own wrapper class to have all those convenience methods because I don’t want to type multiple lines of code to perform a simplest database operation - and when I have to use a wrapper then I lose most of the benefit of database independence that PDO provides (sort of). If I need to change db then I modify slightly my wrapper class and that’s it, it’s not a lot of work considering the non-PDO db extensions are pretty similar.

Also, I think the default non-buffered PDO results are more awkward to use than the buffered ones (default in mysqli). I remember having problems enabling buffered results in PDO… For example, I wanted to implement fetching multiple-row result set as objects in my simple ORM implementation. Normally, I would pull all rows and insert them into an array of objects. However, I wanted the result to be an Iterator implementing Countable and ArrayAccess - can’t do this with unbuffered queries without having num_rows (except pulling all rows into an array first). Struggling with this with PDO I finally went back to mysqli and I’m happy.

I did try PDO for quite some time and my overall feeling was it’s overcomplicated. I can work with it without problems and I understand all the concepts but there’s no real benefit I can see.

But prepared statements is a different thing from stored procedures so one doesn’t exclude the other. In recent versions of Mysql (5.5, not sure about 5.1) prepared statements properly make use of the query cache if necessary.

My main point is preparing statements doesn’t make any logical sense if it is done only to prevent SQL injection, because this is not their purpose and there are other tools designed for the job. Sure, this will work but in my opinion it’s unnecessary. Speed differences are so small that they are not worth worrying about them, at least for mysql.

For beginners I would advise using mysqli (if you have the luxury to choose) - the advanced people will be able to choose for themselves :).

When I first looked into PDO somoene suggested the only difference is the connection code however it appears MySQL is completely different to PDO.

Yes, the differences are not minor but first you need to differentiate between 3 different extensions to connect to MySQL:

  1. MySQL (procedural only) - oldest extension, lacks support for some features of the newest MySQL versions, according to benchmarks it’s the fastest. It’s recommended not to use it unless you have some legacy code.

  2. MySQLi (procedural or object-oriented) - improved MySQL extension, similar but has additional features to be able to utilise all latest MySQL features.

  3. PDO (object-oriented only) - extension based on a completely different API with the idea of making database access universal across all databases. There are some major differences between this and the previous two like queries are unbuffered, which sometimes require a different logic in your code that fetches data.

I’d say choose between the last two and if you use MySQLi then go for the OO version.

I use the MySQLi extension procedurally but wrapped up in a custom class. When I get round to writing it as an example as to why I decided to do it that way when preparing a query and bidning parameters, depending on the query that could be a half dozen lines of binding paramambers, the custome function that I’ll write will take the query and values and bind them, keeping the code in the model classes cleaner.

Any app that uses the MySQL_* extension should be migrated over to either the MySQLi* or PDO extensions.

I found that via a google search but can’t speak for how accurate it is.

I don’t think the connection semantics for SQL server would matter if you are using PDO vs. traditional methods. It basically groups things by connection and sql, no matter how it gets the SQL. Now, PDO probably feeds it SQL it can like more easily, but I don’t think that is a requirement to take advantage of MSSQL caching plans.

Caching / compiling / optimization is the wrong thing to focus on here. Better, cleaner more secure code is. PDO gets you there better with parameter binding as you don’t need to go through the same 19 steps of mysql santization before you get there. I also like the OO database access angle but I’ve spent quality time in ADO.NET so I’m used to that sort of thing.