When to use MySQL, MySQLi, or PDO in PHP

Many people have doubt and questioned as to why I use a "weird extension" when I'm writing a new code. Well for those of you that do not know PHP (like the people that quizzed me, you know who you are!), then come to the world of PHP5. This article would be an explanation of what the different methods for accessing a MySQL database are, and when we should use them.

First of all, many people will say to use a single method no matter what, the one they'd say is normally PDO. If you are one of those people that say to use PDO regardless of the situation, you are wrong, read on for more info.Although, if you are  using PDO in the first place, you could probably work out the obviousness behind this article yourself in no time!

PHP's MySQL Extension

PHP's MySQL extension provides simple methods that are synonymous to the C API that is available for MySQL. All in all, it is not an advanced system  and most importantly, it is no longer maintained.

MySQLi Extension

PHP's MySQLi Extension stands for " MySQL Improved ". It's been around for a long time and was mainly developed to be able to use the new features within MySQL4.1 and above, including MySQL5, which we are all (hopefully) running by now.

Specifically, the MySQLi extension provides the following extremely useful benefits over the old MySQL extension..

• OOP Interface (in addition to procedural)
• Prepared Statement Support
• Transaction + Stored Procedure Support
• Nicer Syntax
• Speed Improvements
• Enhanced Debugging

The most important nicety about MySQLi, in my opinion, would either be the OOP interface, or the Prepared Statement Support.

If you're currently using MySQL Extension (ie. mysql_*) functions, and reading this article thinking something along the lines of "Noooo, I don't want to learn new stuff!", then don't worry, MySQLi also provides a procedural interface which is almost the same as mysql_ functions, however they are prefixed with mysqli_.

PDO Extension

PHP Data Objects extension is a Database Abstraction Layer. Specifically, this is not a MySQL interface,as it provides drivers for many Database Engines (of course including MYSQL).

PDO aims to provide a consistent API that means when a database engine has changed, the code changes to reflect this should be minimal to minimal. When using PDO, your code will normally "just work" across many database engines, simply by changing the driver which you're using.

In addition to being cross-database compatible, PDO also supports prepared statements, stored procedures and more, whilst using the MySQL Driver.

New Projects - What to use?

There are really only 2 options. PHP's MySQL extension definitely should not be used any more for new projects, due to it's lack of support / maintenance.

After reading the above descriptions, you may be thinking that it's always best to use PDO as it appears to provide everything that MySQLi does. Simply put - Don't think this at all. Despite PDO having some advantages such as the portability, it does not allow you to use new features available in latest versions of MySQL servers, like a MySQL specific extension does.

If you're coming from MySQL Extension to something modern, although you really should be using OOP already, you don't have to, and shouldn't have to just to use your database in a better way. MySQLi's procedural interface will be a breeze to convert to as 90% of the time, all you'll have to do is replace instances of mysql_ with mysqli_*.

If you are particularly versed with OOP already, and want to use the best thing for your project, then read on.

Is your project going to ever be used on a database engine other than MySQL? It's not a hard question to answer even if it may seem like it now. You can generally answer it by answering the following sub-questions: Is your application redistributable? Is your application advertised/forced to run on a LAMP/WAMP/MAMP stack?

If the answer is NO to that, then why would you use PDO? There is no point at all using a DAL for a single database, some people will say that it's future proofing your application, but if you're having to learn PDO or MySQLi from scratch, you'd spend more time learning PDO for no reason**.

If the answer is YES, then of course, PDO is definitely your best option and you should invest in making your application compatible with as many things as possible. Just remember, by simply using PDO in your application, it's not guaranteed to work on every other databae known to man, or that PDO supports.

Should not all projects be able to be used across multiple databases?

It depends. If your project is client redistributable (ie. a product), then yes, it would possibly be a good selling point to have it work across many database engines to increase your customer base.

But when it comes to companies, or looking for a job, MySQL is what the people want. In all of my years as  commercial developer within an agency or working on behalf of other companies chances are that they have an existing infrastructure and need you to write an application that suits them.The  one of most common hosting platform right now (unfortunately) is a Linux/Apache/MySQL /PHP setup, so everything's right there for them, and you, by default.

Only around three times have I ever been asked to write something that is portable, and even then, I used sqlite and it's sqlite3 extension within PHP.

Notes

1. I wouldn't suggest that in practice you simply change your calls for example,from mysql_query to mysqli_query. Instead  you should learn about the benefits of prepared statements, and how much nicer it is able to bind your parameters with straight up values, no more escaping or anything (there's obviously more to it than that - read!)!

2. Learning PDO certainly is not a bad thing. All I'm saying is that if you have  write an application in MySQL, spend time learning the nitty gritty details about MySQLi and not PDO. PDO come later, and everything that you have learned from MySQLi is still applicable there.