mysqli or PDO - what are the pros and cons

Question

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

User · Answer

Personally I use PDO  but I think that is mainly a question of preference   PDO has some features that help agains SQL injection  prepared statements   but if you are careful with your SQL you can achieve that with mysqli  too   Moving to another database is not so much a reason to use PDO  As long as you don t use  special SQL features   you can switch from one DB to another  However as soon as you use for example  SELECT     LIMIT 1  you can t go to MS-SQL where it is  SELECT TOP 1       So this is problematic anyway

User · Answer

I ve started using PDO because the statement support is better  in my opinion  I m using an ActiveRecord-esque data-access layer  and it s much easier to implement dynamically generated statements  MySQLi s parameter binding must be done in a single function method call  so if you don t know until runtime how many parameters you d like to bind  you re forced to use call user func array    I believe that s the right function name  for selects  And forget about simple dynamic result binding   Most of all  I like PDO because it s a very reasonable level of abstraction  It s easy to use it in completely abstracted systems where you don t want to write SQL  but it also makes it easy to use a more optimized  pure query type of system  or to mix-and-match the two

User · Answer

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    Binding values to arbitrary number of placeholders 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

User · Answer

Moving an application from one database to another isn t very common  but sooner or later you may find yourself working on another project using a different RDBMS  If you re at home with PDO then there will at least be one thing less to learn at that point   Apart from that I find the PDO API a little more intuitive  and it feels more truly object oriented  mysqli feels like it is just a procedural API that has been objectified  if you know what I mean  In short  I find PDO easier to work with  but that is of course subjective

User · Answer

In sense of speed of execution MySQLi wins  but unless you have a good wrapper using MySQLi  its functions dealing with prepared statements are awful   There are still bugs in mine  but if anyone wants it  here it is   So in short  if you are looking for a speed gain  then MySQLi  if you want ease of use  then PDO

User · Answer

Well  you could argue with the object oriented aspect  the prepared statements  the fact that it becomes a standard  etc  But I know that most of the time  convincing somebody works better with a killer feature  So there it is   A really nice thing with PDO is you can fetch the data  injecting it automatically in an object  If you don t want to use an ORM  cause it s a just a quick script  but you do like object mapping  it s REALLY cool    class Student        public  id      public  first name      public  last name      public function getFullName             return  this- gt first name      this- gt last name          try         dbh   new PDO  mysql host  hostname dbname school    username   password        stmt    dbh- gt query  SELECT   FROM students            MAGIC HAPPENS HERE          stmt- gt setFetchMode PDO  FETCH INTO  new Student         foreach  stmt as  student                echo  student- gt getFullName     lt br   gt                 dbh   null    catch PDOException  e        echo  e- gt getMessage

User · Answer

PDO will make it a lot easier to scale if your site web app gets really being as you can daily set up Master and slave connections to distribute the load across the database  plus PHP is heading towards moving to PDO as a standard   PDO Info  Scaling a Web Application

User · Answer

PDO is the standard  it s what most developers will expect to use   mysqli was essentially a bespoke solution to a particular problem  but it has all the problems of the other DBMS-specific libraries   PDO is where all the hard work and clever thinking will go

User · Answer

One thing PDO has that MySQLi doesn t that I really like is PDO s ability to return a result as an object of a specified class type  e g   pdo- gt fetchObject  MyClass     MySQLi s fetch object   will only return an stdClass object

User · Answer

Here s something else to keep in mind  For now  PHP 5 2  the PDO library is buggy  It s full of strange bugs  For example  before storing a PDOStatement in a variable  the variable should be unset   to avoid a ton of bugs  Most of these have been fixed in PHP 5 3 and they will be released in early 2009 in PHP 5 3 which will probably have many other bugs  You should focus on using PDO for PHP 6 1 if you want a stable release and using PDO for PHP 5 3 if you want to help the community

User · Answer

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  - gt  PGO   faster by   0 35 seconds  SHOW TABLE STATUS  - gt  mysqli   faster by   2 3 seconds  SELECT   FROM users  - gt  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

User · Answer

Another notable  good  difference about PDO is that it s PDO  quote   method automatically adds the enclosing quotes  whereas mysqli  real escape string    and similars  don t      PDO  quote   places quotes around the input string  if required  and   escapes special characters within the input string  using a quoting   style appropriate to the underlying driver

User · Answer

There s one thing to keep in mind   Mysqli does not support fetch assoc   function which would return the columns with keys representing column names  Of course it s possible to write your own function to do that  it s not even very long  but I had really hard time writing it  for non-believers  if it seems easy to you  try it on your own some time and don t cheat

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

Examples related to php

Examples related to mysql

Examples related to pdo

Examples related to mysqli

Examples related to database-abstraction