Mysql extension deprecated as of PHP 5.5

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. 

What's the MySQL extension?

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");
mysql_select_db("database");
$result mysql_query("SELECT * FROM users LIMIT 1");
$row mysql_fetch_assoc($result);
echo 
htmlentities($row['username']);

What's wrong with the MySQL extension?

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:

  • It doesn't offer access to all the features available in MySQL
  • Use of some of the new features promote better security practice
  • MySQL extension does not offer an Object Orientated interface
  • API doesn't support SSL

With suitable alternatives available, the extension will be discontinued.

What will happen if I don't update my code?

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.

What are the alternatives?

There are two main alternatives for connecting to a MySQL database, PDO and MySQLi.

PDO vs 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:

  • PDO supports different databases including MySQL, MS SQL Server and SQLList to name a few, whereas MySQLi only supports MySQL. The benefit of this is that should you need to change the type of database that you are using it can be achived simply by changing the config settings for PDO, for MySQLi it would be a complete re-write.
  • PDO allows use of 'named parameters', making it easier to use prepared statements client-side, here's an example:
    $params = array(':email' => $email, ':password => $password);
    $pdo->prepare('SELECT * FROM users WHERE email=:email AND password=:password');
    $pdo->execute($params);

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.

Comparing the connection methods

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
$mysqli = new mysqli("host""user""pass""database");
$result $mysqli->query("SELECT * FROM users LIMIT 1");
$row $result->fetch_assoc();
echo 
htmlentities($row['username']);

// PDO
$pdo = new PDO('mysql:host=host;dbname=database''user''pass');
$statement $pdo->query("SELECT * FROM users LIMIT 1");
$row $statement->fetch(PDO::FETCH_ASSOC);
echo 
htmlentities($row['username']);

Getting started with PDO

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

PDO Resources

Net tuts - Why you should be using PDO for database access

PHP Pro - Introduction to PHP PDO

CULTTT - Roll your own PDO PHP class

MySQL Extension, RIP.

Sign Up
comments powered by Disqus
Sign Up

Popular Tags

350x250

Need a web developer?

If you'd like to work with code synthesis on your next project get in touch via the contact page.