<p>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.</p>

<p>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.</p>

<p>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?</p>

[{'answer_id': 13571, 'author': 'Theo', 'author_id': 1109, 'author_profile': 'https://Stackoverflow.com/users/1109', 'pm_score': 6, 'selected': False, 'text': "<p>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.</p>\n\n<p>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.</p>\n"}, {'answer_id': 16740, 'author': 'BlaM', 'author_id': 999, 'author_profile': 'https://Stackoverflow.com/users/999', 'pm_score': 3, 'selected': False, 'text': '<p>Personally I use PDO, but I think that is mainly a question of preference.</p>\n\n<p>PDO has some features that help agains SQL injection (<a href="http://de.php.net/manual/de/pdo.prepared-statements.php" rel="noreferrer">prepared statements</a>), but if you are careful with your SQL you can achieve that with mysqli, too.</p>\n\n<p>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.</p>\n'}, {'answer_id': 49787, 'author': 'Brian Warshaw', 'author_id': 1344, 'author_profile': 'https://Stackoverflow.com/users/1344', 'pm_score': 5, 'selected': False, 'text': "<p>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 <code>call_user_func_array()</code> (I believe that's the right function name) for selects. And forget about simple dynamic result binding.</p>\n\n<p>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.</p>\n"}, {'answer_id': 55400, 'author': 'Dave Gregory', 'author_id': 5677, 'author_profile': 'https://Stackoverflow.com/users/5677', 'pm_score': 4, 'selected': False, 'text': "<p>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.</p>\n"}, {'answer_id': 127218, 'author': 'mike', 'author_id': 19217, 'author_profile': 'https://Stackoverflow.com/users/19217', 'pm_score': -1, 'selected': False, 'text': "<p>There's one thing to keep in mind.</p>\n\n<p>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 <strong>really</strong> hard time writing it (for non-believers: if it seems easy to you, try it on your own some time and don't cheat :) )</p>\n"}, {'answer_id': 127319, 'author': 'Unlabeled Meat', 'author_id': 20291, 'author_profile': 'https://Stackoverflow.com/users/20291', 'pm_score': 2, 'selected': False, 'text': "<p>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. <code>$pdo-&gt;fetchObject('MyClass')</code>). MySQLi's <code>fetch_object()</code> will only return an <code>stdClass</code> object.</p>\n"}, {'answer_id': 368758, 'author': 'Tom', 'author_id': 26155, 'author_profile': 'https://Stackoverflow.com/users/26155', 'pm_score': 4, 'selected': False, 'text': "<p>Here's something else to keep in mind: For now (PHP 5.2) the PDO library is <b>buggy</b>. It's full of strange bugs. For example: before storing a <code>PDOStatement</code> in a variable, the variable should be <code>unset()</code> 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.</p>\n"}, {'answer_id': 368990, 'author': 'e-satis', 'author_id': 9951, 'author_profile': 'https://Stackoverflow.com/users/9951', 'pm_score': 8, 'selected': False, 'text': '<p>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:</p>\n\n<p>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 <a href="http://en.wikipedia.org/wiki/Object-relational_mapping" rel="noreferrer">ORM</a> (cause it\'s a just a quick script) but you do like object mapping, it\'s REALLY cool :</p>\n\n<pre><code>class Student {\n\n    public $id;\n    public $first_name;\n    public $last_name\n\n    public function getFullName() {\n        return $this-&gt;first_name.\' \'.$this-&gt;last_name\n    }\n}\n\ntry \n{\n    $dbh = new PDO("mysql:host=$hostname;dbname=school", $username, $password)\n\n    $stmt = $dbh-&gt;query("SELECT * FROM students");\n\n    /* MAGIC HAPPENS HERE */\n\n    $stmt-&gt;setFetchMode(PDO::FETCH_INTO, new Student);\n\n\n    foreach($stmt as $student)\n    {\n        echo $student-&gt;getFullName().\'&lt;br /&gt;\';\n    } \n\n    $dbh = null;\n}\ncatch(PDOException $e)\n{\n    echo $e-&gt;getMessage();\n}\n</code></pre>\n'}, {'answer_id': 736849, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 3, 'selected': False, 'text': '<p>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.</p>\n\n<p>There are still bugs in mine, but if anyone wants it, <a href="http://gorilla3d.com/blog/entry/2008-08-03/improved-mysqli-db-wrapper-prepared-statements" rel="nofollow noreferrer">here it is</a>.</p>\n\n<p>So in short, if you are looking for a speed gain, then MySQLi; if you want ease of use, then PDO.</p>\n'}, {'answer_id': 3937381, 'author': 'Dobb', 'author_id': 476285, 'author_profile': 'https://Stackoverflow.com/users/476285', 'pm_score': 2, 'selected': False, 'text': '<p>In my <a href="http://pbox.ca/1628i" rel="nofollow">benchmark script</a>, 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!</p>\n\n<p>These are my results: </p>\n\n<ul>\n<li>"<code>SELECT NULL" -&gt; PGO()</code> faster by ~ 0.35 seconds</li>\n<li>"<code>SHOW TABLE STATUS" -&gt; mysqli()</code> faster by ~ 2.3 seconds</li>\n<li>"<code>SELECT * FROM users" -&gt; mysqli()</code> faster by ~ 33 seconds</li>\n</ul>\n\n<p>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).</p>\n'}, {'answer_id': 6825215, 'author': 'Alix Axel', 'author_id': 89771, 'author_profile': 'https://Stackoverflow.com/users/89771', 'pm_score': 3, 'selected': False, 'text': '<p>Another notable (good) difference about PDO is that it\'s <a href="http://php.net/manual/en/pdo.quote.php"><code>PDO::quote()</code></a> method automatically adds the enclosing quotes, whereas <a href="http://php.net/manual/en/mysqli.real-escape-string.php"><code>mysqli::real_escape_string()</code></a> (and similars) don\'t:</p>\n\n<blockquote>\n  <p>PDO::quote() places quotes around the input string (if required) and\n  escapes special characters within the input string, using a quoting\n  style appropriate to the underlying driver.</p>\n</blockquote>\n'}, {'answer_id': 10254595, 'author': 'Dfranc3373', 'author_id': 1327815, 'author_profile': 'https://Stackoverflow.com/users/1327815', 'pm_score': 3, 'selected': False, 'text': '<p>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.</p>\n\n<p><a href="http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/">PDO Info</a></p>\n\n<p><a href="http://www.oracle.com/technetwork/articles/dsl/white-php-part1-355135.html">Scaling a Web Application</a></p>\n'}, {'answer_id': 14303765, 'author': 'Your Common Sense', 'author_id': 285587, 'author_profile': 'https://Stackoverflow.com/users/285587', 'pm_score': 3, 'selected': False, 'text': '<p><em>Edited answer.</em></p>\n\n<p>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.<br>\nThey were already mentioned in 2 excellent (yet way underrated) answers:</p>\n\n<ol>\n<li><a href="https://stackoverflow.com/a/4351461/285587"><strong>Binding values to arbitrary number of placeholders</strong></a></li>\n<li><a href="https://stackoverflow.com/a/127218/285587"><strong>Returning data as a mere array</strong></a> </li>\n</ol>\n\n<p>(both also mentioned in <a href="https://stackoverflow.com/a/49787/285587">this answer</a>)</p>\n\n<p>For some reason mysqli failed with both.<br>\nNowadays it got some improvement for the second one (<a href="http://php.net/manual/en/mysqli-stmt.get-result.php" rel="nofollow noreferrer">get_result</a>), but it works only on mysqlnd installations, means you can\'t rely on this function in your scripts. </p>\n\n<p>Yet it doesn\'t have bind-by-value even to this day. </p>\n\n<p>So, there is only one choice: <strong>PDO</strong></p>\n\n<p>All the other reasons, such as </p>\n\n<ul>\n<li>named placeholders (this syntax sugar is way overrated)</li>\n<li>different databases support (nobody actually ever used it)</li>\n<li>fetch into object (just useless syntax sugar)</li>\n<li>speed difference (there is none)</li>\n</ul>\n\n<p>aren\'t of any significant importance.</p>\n\n<p>At the same time both these APIs lacks some <strong>real important features</strong>, like</p>\n\n<ul>\n<li>identifier placeholder</li>\n<li>placeholder for the complex data types to make dynamical binding less toilsome</li>\n<li>shorter application code.</li>\n</ul>\n\n<p>So, to cover the <em>real</em> 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.</p>\n'}]
['https://Stackoverflow.com/questions/13569', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/137/']