Many people have asked this, and questioned as to why I use a "weird extension" when I'm writing new code. Well for those of you that don't know PHP (like the people that quizzed me, you know who you are!), then welcome to the world of PHP5. This article should be an explanation of what the different methods for accessing a MySQL database are, and when you 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're one of these people that say to use PDO regardless of the situation, you're wrong, read on for more info. Although, if you're actually using PDO in the first place, you could probably work out the obviousness behind this article yourself in no time!
PHP's MySQL extension provides simple methods that are synonymous to the C API that is available for MySQL. All in all, it's not an advanced system, and most importantly, it is no longer maintained.
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 MySQL 4.1 and above, including MySQL 5, which we're all (hopefully) running by now.
Specifically, the MySQLi extension provides the following extremely useful benefits over the old MySQL extension..
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're prefixed with mysqli_.
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 is changed, the code changes to reflect this should be minimal. When using PDO, your code will normally "just work" across many database engines, simply by changing the driver you're using.
In addition to being cross-database compatible, PDO also supports prepared statements, stored procedures and more, whilst using the MySQL Driver.
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're 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.
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 a 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 most common hosting platform right now (unfortunately) is a Linux/Apache/PHP/MySQL setup, so everything's right there for them, and you, by default.
Only around 3 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.
* - 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 to be able to bind your parameters with straight-up values, no more escaping or anything (there's obviously more to it than that - read!)!
** - Learning PDO certainly is not a bad thing. All I'm saying is that if you have to write an application for MySQL, spend time learning the nitty gritty details about MySQLi and not PDO. PDO can come later, and everything that you've learned from MySQLi is still applicable there.