PHP PDO and MySQLi [duplicate]

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.