PDO v MySQL

[justlukeyou]

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

[Lemon Juice]

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.

[SpacePhoenix]

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.

http://net.tutsplus.com/tutorials/ph...hould-you-use/

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

[wwb_99]

Unfortunately, for MySQL, it doesn't do that globally, it does it per connection, which makes it meaningless (Source)

Well, to be honest, because I believe (but can't prove it yet; haven't found the documentation to support it yet -- if you know the answer to this, I'm interested) the preparation only keeps for the given request, beyond that, it is lost, so other requests don't get to take advantage of the user before them already running that query. This is where if SProcs worked properly in MySQL and it cached the execution plan globally and not per connection, then it would make sense to always recommend prepared statements. But the way it seems to work goes against all expectations and you end up spending time for PHP to cache/compile/optimize a query used for a single request.

Granted, that is ALL under the assumption you are using MySQL. If you are using PDO with SQL Server, you are going to do great things with PDO's aid in terms of performance optimization.

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.