PHP PDO and MySQLi

Question

I just finished an introduction course in PHP, and throughout the stackoverflow forum people have recommended that I switch to PDO, prepared statements or MYSQLi, I briefly checked the manual but most of it went over my head.

I've been using mysql_* functions up till now so these concepts are new to me. I think they are used to access and perform database specific actions, but I'm not sure.

So what is the difference between PDO, prepared statements and MySQLi, are they different features that accomplishes the same task? Are they compatible in a script or is it "choose one or the other"? And lastly which offers the best performance?

Update: Thanks for the answers, I'll be hunting for more PDO tutorials.

For reference I also found the following posts useful:

Which one is fast and light - mysqli or PDO

mysqli or PDO - what are the pros and cons?

Answer 1

At the basic level the mysql, mysqli and PDO extensions all answer the question how do I talk to the database? They all provide functions and functionality to connect to a database and send and retrieve data from it. You can use them all at the same time establishing several connections to the database at once, but that's typically nonsense.

mysql* is a very simple extension that basically allows you to connect to the database, send it SQL queries and not much else.
mysqli improves this (as the name suggests) by adding parameterized queries and a few other things into the mix.
PDO is an extension that abstracts several database drivers into one package, i.e. it allows you to use the same code to connect to MySQL, Oracle, MS SQL Server and a number of other databases without needing to use database specific extensions or rewrite your code when you switch databases (in theory at least). It also supports parameterized queries.

If you know you're going to be using MySQL exclusively, mysqli is a good choice. Especially since you can use it in a procedural way, what you're already used to from the mysql extension. If you're not familiar with OOP, that's helpful. Otherwise, PDO is a nice object oriented, flexible database connector.

---

* Note that the mysql extension is now deprecated and will be removed sometime in the future. That's because it is ancient, full of bad practices and lacks some modern features. Don't use it to write new code.

Answer 2

PDO is the "PHP Data Object." I mostly use PDO, so I can only speak on its merits:

• Works for many more databases than just MySQL (may not matter to you)
• Compiled C, so it's faster (supposedly)
• Prepared statements (others have these, though)
• SO seems to like it, so you can probably get a lot of help here at least
• Various fetch/error handling modes you can set and change on the fly

You ask

So what is the difference between PDO, prepared statements and MySQLi ...

PDO and MySQLi are DB wrappers. "Prepared statements" is a different concept altogether. You can prepare a query that can be executed multiple times, and properly parameterized statements are SQL-Injection safe (though maybe not proof). The latter reason is most of the reason why you should be using PDO (or MySQLi), but prepared statements also bring a level of clarity to the queries.

/* mysql_* version */
mysql_connect("host");
$query = "SELECT column FROM db1.t1 WHERE id = ";
foreach ($_GET['id'] as $id) {
   $id = mysql_real_escape_string($id);
   $result = mysql_query($query . "'$id'";
   while ($row = mysql_fetch_assoc($result)) {
      echo "$row[column]\n";
   }
}
//NOTE: it would probably be better to store the resource returned by
//mysql_connect and use that consistently (in query/escape)

/* PDO version */
$pdo = new PDO('mysql:host=HOST', 'user', 'pass');
$query = $pdo->prepare("SELECT column FROM db1.t1 WHERE id = ?";
foreach ($_GET['id'] as $id) {
   $query->execute($id);
   echo $query->fetch(PDO::FETCH_COLUMN);
}
//Notice that you skip the escape step.

You can do essentially the same with MySQLi, but I prefer PDO's syntax. It may be faster too, but I could be making that up. There's also the PEAR MDB2 that rarely gets spoken of, and I'm sure many more. Since PDO is built in, I would go with it.

Answer 3

If you're used to the mysql_xxx functions, then I would starting by moving across to the MySQLi extension instead.

You could use PDO instead if you wish, but this would only really be worth it in the first instance if you need to start supporting multiple databases. For your purposes, I'd suggest switching to MySQLi, as it'll be easier for you, and you won't be getting the benefits of PDO right away anyway.

The functions available with MySQLi are pretty much analogous to the mysql_xx functions you're used to; it's generally possible to take existing code, do a direct swap between them, and the code should continue working just fine.

So that's a good place to start -- get your code using mysqli_xxx instead of mysql_xxx`.

If possible, I'd recommend using the object oriented syntax rather than the procedural syntax. MySQLi supports both, and the procedural syntax will be closer to what you're used to, but the OO syntax is more flexible in the long run, and really isn't that much different once you're used to it.

Once you've got your code converted to using the MySQLi library, and you're comfortable with the basics, you're ready to start using the more advanced features like prepared statements. But get yourself comfortable with the basics first.

Answer 4

Coming from the same point of view as you. From my perspective I don't think the difference is truly noticeable (depending on what you're using it for). It looks like PDO is simply a database api that merges ALL of the other database api's into one. So if you needed to connect to a MS Sql server and MySQL server, you could simply call on the PDO api and specify the driver for the specific db. My guess is also that any future features and abilities in MySQL will be only available in PDO. So basically just use PDO to ensure that you have access to all the latest features.

Answer 5

One big advantage of PDO is platform independence. This means that you can migrate to a different DBMS at some point without having to recode all of your function calls. This is how things are typically done in Java (via JDBC), .Net (ADO) and most other environments. The advantage is not just that you can switch DBMS per se, it's also that you have only one API to learn.

As regards your question, the PDO layer provides the facility to do prepared statements. The idea behind prepared statements is that you create placeholders for the parts of your SQL statement that will not be known until run time. Many learners start off by creating SQL as a string which gets executed by calling mysqli::query($someQuery). This is problematic for many reasons, most prominent of which is the vulnerability to SQL injection (see stackoverflow.com/questions/5315351 for a similar question and answer). With PDO, you can avoid SQL injection and all of the problems of handling characters such as quotes, backslashes etc. The end result is that your code is more secure, readable and predictable.

If you've already figured out how to use mysqli then using PDO is not much different. The linked question and answer above shows an example of a query being submitted using PDO prepared statements which should act as a useful guide.

Answer 6

So what is the difference between PDO, prepared statements and MySQLi, are they different features that accomplishes the same task?

The difference is fairly simple.
PDO is usable with prepared statements and mysqli is not.

Just run some usual queries with both API using native prepared statements, and you will clearly see the difference.