As of PHP 5.5 the original MySQL extension is no longer supported. While the majority of modern frameworks and libraries use more up-to-date code, there will still be many applications referencing this now very dated extension.
Most web host servers run at least a couple of versions behind, but when yours catches up you'll want to make sure none of your old applications are using this extension to connect to a database.
The original MySQL extension distributed with PHP allows for connection to MySQL databases using a series of functions that start with 'mysql_'. You'll probably be familiar with this section of code for connecting to a database:
$c = mysql_connect("host", "user", "password");
$result = mysql_query("SELECT * FROM users LIMIT 1");
$row = mysql_fetch_assoc($result);
So the above code looks OK, why is it being deprecated?
The extension was originally written for MySQL 3.23 and hasn't had any major updates that provides greater functionality that MySQL now offers such as prepared statements. Here's some of the reasons for the vote on deprecating the extension:
With suitable alternatives available, the extension will be discontinued.
If your host is going to upgrade your PHP version, or you're moving to a new host running PHP 5.5, any applications trying to use the deprecated MySQL extension will return the E_DEPRECATED error. Yikes.
While it is possible to suppress these errors in your ini file or at run time, I would strongly advise against this as you would only be delaying the problem before the extension is removed altogether. Should you wish to do this though, here's the snippet you'll need:
// PHP .ini file
error_reporting = E_ALL & ~E_DEPRECATED
// PHP runtime (place in code)
error_reporting(E_ALL & ~E_DEPRECATED);
Note by doing this you'll be switching off all your deprecation warnings.
There are two main alternatives for connecting to a MySQL database, PDO and MySQLi.
So here comes the big question - PDO vs MySQLi - if you need to upgrade, which one should you use?
PDO and MySQLi both offer an object orientated API, though MySQLi does also offer a procedural API so it may be more familiar if you're moving away from the original extension (the 'i' in MySQLi actually stands for 'improved' - MySQL extension Improved).
In short they both achieve the same result (MySQLi has shown to be marginally faster in performance benchmarks) however PDO has a couple of distinct advantages:
This can be achived with '?' holders in MySQLi however you have to keep track of the order which is more error prone. Prepared statments are an important feature for preventing MySQL injection.
Here's an example of how you would connect MySQLi and PDO in a similar way to the opening code snippet for the original extension:
$mysqli = new mysqli("host", "user", "pass", "database");
$result = $mysqli->query("SELECT * FROM users LIMIT 1");
$row = $result->fetch_assoc();
$pdo = new PDO('mysql:host=host;dbname=database', 'user', 'pass');
$statement = $pdo->query("SELECT * FROM users LIMIT 1");
$row = $statement->fetch(PDO::FETCH_ASSOC);
If you've decided to use PDO then in an upcoming post I'll be looking at creating your own database layer. If you can't wait until then here's some resources to get you started...
MySQL Extension, RIP.