By Polsonby


2008-08-17 09:47:22 8 Comments

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?

13 comments

@Your Common Sense 2013-01-13 13:06:20

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.

@Ihsan 2013-05-02 07:15:55

Finally somebody who knows and does not deny the facts of life...

@Dobb 2010-10-14 20:43:32

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).

@Alix Axel 2011-07-26 04:15:48

What's PGO? And faster by 33 seconds?! I find that very hard to believe...

@Joseph Montanez 2009-04-10 06:04:27

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.

@Julius F 2009-11-28 14:44:26

in sense of speed, could you give benchmarks?

@Adam 2011-03-08 12:09:25

Jonathen Robson has done a decent speed comparison of the two at jonathanrobson.me/2010/06/mysqli-vs-pdo-benchmarks. Summary: inserts - almost equal, selects - mysqli is ~2.5% faster for non-prepared statements/~6.7% faster for prepared statements. Given how small the performance penalties are, the features and flexibility of using PDO generally outweigh the performance hit.

@jnrbsn 2012-03-23 19:33:54

@Adam Thanks for linking to my blog!

@Dyin 2013-01-13 10:05:53

@daemonfire300 This is true, there is no need for benchmarks. PDO wraps the mysqli library. I would probably hit the fan if someone could prove that PDO is faster than mysqli. :-D

@Basit 2013-02-02 14:47:43

@jnrbsn do you agree with Adam on what he said?

@jnrbsn 2013-03-13 18:12:56

@Basit Yes, I agree with everything he said. The pros of PDO definitely outweigh the cons. I never use MySQLi anymore.

@Unlabeled Meat 2008-09-24 14:01:54

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->fetchObject('MyClass')). MySQLi's fetch_object() will only return an stdClass object.

@Andrioid 2010-09-04 08:12:13

Actually, you can specify a class manually: "object mysqli_result::fetch_object ([ string $class_name [, array $params ]] )". stdClass is only used if you don't specify anything.

@Dfranc3373 2012-04-20 22:58:40

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

@Alix Axel 2011-07-26 04:20:45

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.

@e-satis 2008-12-15 16:49:17

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->first_name.' '.$this->last_name
    }
}

try 
{
    $dbh = new PDO("mysql:host=$hostname;dbname=school", $username, $password)

    $stmt = $dbh->query("SELECT * FROM students");

    /* MAGIC HAPPENS HERE */

    $stmt->setFetchMode(PDO::FETCH_INTO, new Student);


    foreach($stmt as $student)
    {
        echo $student->getFullName().'<br />';
    } 

    $dbh = null;
}
catch(PDOException $e)
{
    echo $e->getMessage();
}

@sergiom 2010-03-10 09:48:30

ORM: Object-relational mapping en.wikipedia.org/wiki/Object-relational_mapping

@micmcg 2010-03-18 04:41:24

Its a good post, but could definitely be clearer. Where do animals come into a snippet with a Person class and a persons table?

@DesignerGuy 2011-04-15 07:18:09

is there a difference between the above and $mysqliResult->fetch_object("student"); ?

@OZ_ 2011-05-24 12:30:00

Silly feature. If object needs to have public fields - it's against encapsulation. And the new keyword used inside method - bad practice.

@OZ_ 2011-05-24 21:08:12

@e-satis no, I use PHP. Public fields violates encapsulation, so AS A BEST PRACTICE it's just... lol :) Google doesn't uses public fields, only accessors: google-styleguide.googlecode.com/svn/trunk/… .

@OZ_ 2011-05-24 21:11:52

@e-satis, try to read this book (I'm sure you can find it in your native language, I did) amazon.com/Objects-Patterns-Practice-Matt-Zandstra/dp/… - there you can find very good explanation, what is main goal of encapsulation, why public fields should not be used and why interfaces are so important. I know, you think I'm your opponent at this moment - don't take it so close to heart, just read this book and, I hope, you will understand me.

@James P. 2011-06-04 17:08:59

@e-satis: Sorry for jumping in but getters and setters are necessary if you wish to control what happens when the variables are changed. Otherwise you simple can't guarantee the internal state of your object (this is especially an issue if you have another object inside). This is entirely language independent. @OZ_: Do ease up. Personal criticism will only put someone else on the defensive.

@alternative 2011-06-06 13:05:42

I love it when people say that public fields are evil. We are using a database here; when will we not want to be able to edit the fields of a database, and access the same fields? Come on...

@Dan Lugg 2011-07-16 00:50:47

@monadic: Agreed. Encapsulation is of course a valid argument when dealing with core components, or complex objects, etc., however as representations of records which would otherwise be read-write assoc. arrays, this is acceptable. Furthermore it permits easier type checking as records float through the system.

@outis 2011-08-28 02:24:42

@Mattygabe: sadly, too many developers that are just starting out won't realize there are problems with sample code and will copy it rather than understanding it. If part of sample code shouldn't be copied, the code should be accompanied with a note to the effect.

@Mattygabe 2011-09-01 14:11:50

@outis I hope I'm not in the minority here, but I don't feel answers should be judged on their safety toward new developers. Sounds harsh, but it's true. The purpose of an answer on SO is not just to provide copy-and-paste code, but to provide understanding as well. It's not the answerer's job to ensure every security hole or pattern flaw is covered in an example, because let's face it, the application the code gets copied into is inherently different than every other application using the same code.

@outis 2011-09-01 20:48:26

@Mattygabe: please understand, I'm not arguing for the downvote, nor that every aspect of the sample code be covered, just that a short note is needed when sample code contains something that (in production code) is commonly a mistake, or where readers should be careful. I very much agree answers on SO should provide explanations, not complete solutions, hence sample code should be considered illustrative. This is why there should be a note, as some readers won't take code as illustrative examples but will copy it whole hog.

@outis 2011-09-01 20:51:30

... As for differences between projects, there are similarities as well. The differences aren't important here; the common mistakes lie in the similarities.

@Mattygabe 2011-09-02 16:00:53

@outis Understood, and I never thought you were necessarily arguing for the downvote. I was merely positing that any examples on SO should be treated with the universal "developer beware" caveat. The biggest reason for my feelings on this particular example being that, the example is to show a feature of PDO that isn't necessarily well-known, and is not about the best way to perform a MySQL select statement with PDO, so the example's purpose is in no way to show the best way to perform a select. But, we agree on most everything, so the disagreement is minor.

@toon81 2013-02-15 07:08:23

I haven't tested this out, but IMO it is very likely that the encapsulation argument is largely moot, because PHP 5+ allows accessors in the form of the magic __get() and __set() methods. So there happily goes the entire "I can't guarantee my object's state's integrity now" argument out the window...

@Tom 2008-12-15 15:45:02

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.

@Brian Warshaw 2009-11-10 16:55:12

I think that the gains that PDO offers are worth understanding and working around the bugs. PHP itself is full of very aggravating bugs, some that we can't even work around efficiently, and yet it offers many benefits that cause us to use it instead of other options.

@NikiC 2011-01-23 10:49:28

Uhm, strange, I never experienced any bug with PDO. And I use it a lot.

@Bill Karwin 2013-05-06 19:02:44

Mysqli also has bugs. All software has bugs.

@michal kralik 2008-09-24 13:45:36

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 :) )

@Till 2008-09-24 13:49:18

@michal kralik 2008-09-24 13:56:53

Was implementing longer time ago, but yes I checked the manual. Does it work with prepared statements? I doubt...

@Till 2008-09-24 14:13:07

"But can it crush cars?"

@Álvaro González 2010-04-30 10:22:02

Actually, it has a curiously partial support. You can fetch arrays in regular queries but not in parametrized queries :-!

@Majid Fouladpour 2012-04-19 03:04:00

Why not delete an answer which is obviously wrong?

@Álvaro González 2012-04-24 11:02:50

@MajidFouladpour - The answer is not obviously wrong. It's just missing some context. Mysqli does not fully support associative array retrieval.

@Dave Gregory 2008-09-10 21:56:31

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.

@Brian Warshaw 2008-09-08 14:17:40

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.

@Pim Jager 2009-04-21 07:50:39

Result binding with dynamic generated querys is possible, we do it at our applications. It however is a huge pain.

@cwallenpoole 2009-11-09 19:09:28

That is the right name.

@BlaM 2008-08-19 18:55:06

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.

@Tower 2009-07-07 09:59:07

MySQLi has prepared statements.

@Halil Özgür 2011-02-10 10:06:10

I back rFactor...

@Theo 2008-08-17 09:55:03

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.

Related Questions

Sponsored Content

32 Answered Questions

[SOLVED] Reference - What does this error mean in PHP?

18 Answered Questions

[SOLVED] Reference — What does this symbol mean in PHP?

7 Answered Questions

[SOLVED] Are PDO prepared statements sufficient to prevent SQL injection?

1 Answered Questions

[SOLVED] PDO escaping with imploding array's

5 Answered Questions

[SOLVED] What is the difference between MySQL, MySQLi and PDO?

  • 2010-02-03 08:59:55
  • Chintan Parekh
  • 103444 View
  • 121 Score
  • 5 Answer
  • Tags:   php mysql pdo mysqli

3 Answered Questions

[SOLVED] PHP/mysqli - prepared statement (in a loop) or multi_query

1 Answered Questions

[SOLVED] PDO vs MYSQLI, Prepared Statemens and Binding Parameters

6 Answered Questions

[SOLVED] PHP PDO and MySQLi

  • 2012-05-22 13:49:35
  • grasshopper
  • 24328 View
  • 36 Score
  • 6 Answer
  • Tags:   php mysql pdo mysqli

5 Answered Questions

[SOLVED] Moving from mysql to mysqli or pdo?

1 Answered Questions

[SOLVED] Building an ORM - mysqli vs regular SQL vs PDO

  • 2011-04-01 06:39:00
  • Bharad
  • 1603 View
  • 0 Score
  • 1 Answer
  • Tags:   php orm pdo mysqli

Sponsored Content