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


In our place we're split between using mysqli and PDO for stuff like prepared statements and transaction support. Some projects use one, some the other. There is little realistic likelihood of us ever moving to another RDBMS.

I prefer PDO for the single reason that it allows named parameters for prepared statements, and as far as I am aware mysqli does not.

Are there any other pros and cons to choosing one over the other as a standard as we consolidate our projects to use just one approach?


Edited answer.

After having some experience with both these APIs, I would say that there are 2 blocking level features which renders mysqli unusable with native prepared statements.
They were already mentioned in 2 excellent (yet way underrated) answers:

  1. Binding values to arbitrary number of placeholders
  2. Returning data as a mere array

(both also mentioned in this answer)

For some reason mysqli failed with both.
Nowadays it got some improvement for the second one (get_result), but it works only on mysqlnd installations, means you can't rely on this function in your scripts.

Yet it doesn't have bind-by-value even to this day.

So, there is only one choice: PDO

All the other reasons, such as

  • named placeholders (this syntax sugar is way overrated)
  • different databases support (nobody actually ever used it)
  • fetch into object (just useless syntax sugar)
  • speed difference (there is none)

aren't of any significant importance.

At the same time both these APIs lacks some real important features, like

  • identifier placeholder
  • placeholder for the complex data types to make dynamical binding less toilsome
  • shorter application code.

So, to cover the real life needs, one have to create their own abstraction library, based on one of these APIs, implementing manually parsed placeholders. In this case I'd prefer mysqli, for it has lesser level of abstraction.

In my benchmark script, each method is tested 10000 times and the difference of the total time for each method is printed. You should this on your own configuration, I'm sure results will vary!

These are my results:

  • "SELECT NULL" -> PGO() faster by ~ 0.35 seconds
  • "SHOW TABLE STATUS" -> mysqli() faster by ~ 2.3 seconds
  • "SELECT * FROM users" -> mysqli() faster by ~ 33 seconds

Note: by using ->fetch_row() for mysqli, the column names are not added to the array, I didn't find a way to do that in PGO. But even if I use ->fetch_array() , mysqli is slightly slower but still faster than PGO (except for SELECT NULL).

By : Dobb

This video can help you solving your question :)
By: admin