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?

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

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

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

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

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

View more solutions

Comments

[Kebman]

Finally someone who could answer this in a simple and straight forward way. Thank you!

[Your Common Sense]

-1 for calling mysqli a good choice.

[Gromski]

@YourCommonSense I think you've lost yours. Especially since you're using mysqli in your library you're advertising everywhere.

[Your Common Sense]

I am using homebrew prepared statements (which you say you don't trust :), as a part of DBAL. So, I am using mysqli as mere mysql_ to send conventional SQL to server. Mysqli is all right with it. But it is strictly implied in this topic of using prepared statements that only raw API and native prepared statements have to be used. And mysqli just unusable with them, making you use call_user_func()(!) with whatever more or less complex query, either to bind your variables and to get data off the result. That's just weird. People get lured by simple examples and then get into pain

[Gromski]

@YourCommonSense "Unusable" is rather subjective, no? It works, which makes it usable. Yes, sssisisississi is ugly as sin, but you also have the ::bind_param() API as an alternative. Nobody said it's awesome, but it works, it's robust, and as far as I know it's the only API which supports every last feature of MySQL. PDO doesn't (though that's not usually problem).

[martinstoeckli]

@YourCommonSense - Could you please explain in which situation you have to call call_user_func()? Why don't you just use $stmt->bind_param(), that's not very far from the PDO $sth->bindParam()? Surely it's not a reason to downvote everywhere.

[Your Common Sense]

No, no. if you have your values in array, you can't use bind_param without referencing every variable first. So, you can't use a loop with bind_params. See this one for example stackoverflow.com/q/16120822/285587. And the same pleasure you would have getting your values back(!) if there is no get_result() happened to be available. Surely it is still feasible. But I would rather call it 'pain' than 'usability'

[martinstoeckli]

@YourCommonSense - Never used it together with call_user_func_array() myself, so this could be one disadvantage. Are there other reasons not to use mysqli?

[a coder]

Is there a performance advantage with PDO over MDB2?

[Daan]

This source states that MySQL and MySQLi are a bit faster: code.tutsplus.com/tutorials/…

[mendez7]

You missed a closing bracket Explosion Pillis in your pdo prepare query statement.

[Thomas Williams]

This is simply not true mysqli does have prepared statements eg $stmt = $db->prepare($query);

[Your Common Sense]

@ThomasWilliams you need to pay more attention to what you read. The word "usable" is the key. phpdelusions.net/pdo/mysqli_comparison

[Thomas Williams]

After some investigation I quite like the way pdo works, but mysqli prepared statements are usable, and I have been using them for a few years. Yes it is a pain when you leave out a comma and you spend a while tracking down the bug, but yes it is very usable. The only dealbreaker for me with pdo is that it is slower than mysqli on mysql databases.

[Your Common Sense]

It's OK. Most PHP users never care about the amount of code they write, so you are not the only one who finds mysqli usable.

[Thomas Williams]

I was on the verge of changing to pdo until I found out that pdo is 6% slower than mysqli, and a little extra php code is not a problem with performance. And it is not that much extra if you write your code in classes and re-use the same code over and over again.

[Your Common Sense]

The way you measured the difference is deadly inaccurate. there is not even 0.6%