Avoid the Original MySQL Extension, Part 1

Tweet
This entry is part 2 of 2 in the series Avoid the MySQL Extension

Avoid the MySQL Extension

Experienced developers eschew the original MySQL extension because of its abandoned status in PHP. Nascent web developers, however, may be completely oblivious to its dormant past and dying future.

Introductory tutorials for connecting to MySQL from PHP are certainly abundant, as is evident from a simple “php and mysql tutorial” Google search. It’s just unfortunate that many of them teach only how to use the original MySQL extension. This has channelled new developers who would like to learn how to interact with their database to only one of three potential extensions, giving them a skill that will soon become useless in an upcoming major version of PHP.

In turn, this has increased the amount of people who rely upon the extension and will, in some cases, use it as their one-and-only database access method. This means a ‘soft deprecation’ is needed to slowly phase out the use of the extension with as little impact as possible for the users who have yet switched over to a newer extension.

It is therefore the intention of this two-part article to raise awareness among developers who still use the MySQL extension, inform them of its problems, and to help them switch over to an alternative extension. After all, who really wants to write a script knowing that it won’t work in a near-future version of PHP? In this part we’ll focus on MySQLi, and in part two we’ll look at PDO.

What’s Wrong with the MySQL Extension?

Aside from the fact that it’s currently in the process of being deprecated, there may seem to be nothing wrong with the original MySQL extension. Dig a little deeper though into PHP history and it’s easy to see why many developers avoid using it. The extension has been maintain-only since PHP 4.3.1. This means the last time any new functionality was added to the MySQL extension was back in late 2002. That’s 10 years worth of features you’re missing out on!

If that doesn’t sound bad enough, the last version of the MySQL API to be integrated into PHP was from MySQL 4.0.0. This means the extension doesn’t handle any of the new features added to the API over the years by MySQL.

Alternative #1: MySQLi – MySQL Improved

There are two primary alternatives to interacting with a MySQL database. The first is the MySQLi, or MySQL Improved, extension which exposes both an object-oriented and procedural API.

The difference between the procedural API in comparison to the MySQL extension’s is minimal, but the gains are significant. With over 100 functions provided by MySQLi, it dwarfs the older extension which has a mere 48 functions. These new functions bring new features, and we’ll run through some of them right after we’ve familiarized ourselves with the basics.

MySQLi Basics

First, let’s create a connection to a MySQL database and then interact with it by performing some basic queries.

<?php
// Procedural API connection method #1
$db = mysqli_connect('host', 'username', 'password');
mysqli_select_db($db, 'database');

// Procedural API connection method #2
$db = mysqli_connect('host', 'username', 'password', 'database');

if (mysqli_connect_errno()) {
     die(mysqli_connect_error());
}

// Object-oriented API connection
$db = new MySQLi('host', 'username', 'password', 'database');

if ($db->connect_errno) {
    die($db->connect_error);
}

When using the procedural implementation, we are presented with two ways to connect to the database. The first is more similar to the original MySQL extension where the host connection is segregated from the database connection, requiring a little more code. The second method is more like the object-oriented interface of the MySQLi API, where the database name is passed as a parameter. We can then check if there was an error while attempting to connect to the database by questioning the return of mysqli_connect_errno(). The error message is retrieved using mysqli_connect_error().

With the object-oriented implementation, we instantiate the MySQLi class and pass all of the connection details as constructor arguments. A check can then be carried out on the connect_errno property to see if there was an error, and the corresponding error message can be retrieved from the connect_error property.

Now let’s carry out some basic interactions against the database.

<?php
$name = "O'Reilly";
$email = "In'valid@email.com";
$clean = array();

// Procedural API
$clean['name'] = mysqli_real_escape_string($db, $name);
$clean['email'] = mysqli_real_escape_string($db, $email);

mysqli_query($db, "INSERT INTO table_name
     VALUES (NULL, '{$clean['name']}', '{$clean['email']}')");

mysqli_query($db, "UPDATE table_name
     SET name = 'Thomas' WHERE email = '{$clean['email']}'");

$result = mysqli_query($db, "SELECT name FROM table_name
     WHERE email = '{$clean['email']}'");

if ($row = mysqli_fetch_assoc($result)) {
     echo $row['name'];
} else {
     echo 'No results found.';
}

// Object-oriented API
$clean['name'] = $db->real_escape_string($name);
$clean['email'] = $db->real_escape_string($email);

$db->query("INSERT INTO table_name
    VALUES (NULL, '{$clean['name']}', '{$clean['email']}')");

$db->query("UPDATE table_name
    SET name = 'Thomas' WHERE email = '{$clean['email']}'");

$result = $db->query("SELECT name FROM table_name
    WHERE email = '{$clean['email']}'");

if ($row = $result->fetch_assoc()) {
    echo $row['name'];
} else {
    echo 'No results found.';
}

The main difference from the original MySQL extension and MySQLi’s procedural API is the compulsory passing of the connection link as the first parameter in the mysqli_* functions (a comprehensive list of all functions and methods/properties can be found in the PHP manual). This is of course different to the MySQL extension, where it is optional to pass the connection link as the last parameter.

We don’t have to pass the connection link as an argument to the object’s methods in the object-oriented approach because it’s maintained as part of the MySQLi instance’s state. Additionally, all of the methods and properties exposed by the MySQLi class also omit the mysqli_ prefix, and mysqli_stmt_ is omitted from the properties and methods of the MySQLi_stmt class.

What’s New in MySQLi?

Now that we’ve familiarized ourselves with the basics of the two APIs, let’s take a look at some of the new features that it introduces: prepared statements, multi-queries, and transactions.

Prepared Statements

Prepared statements, otherwise known as parametrized queries, are one of the main features introduced by this API. Prepared statements are considered to be safer than MySQL’s mysql_real_escape_string() function because they are not prone to human error. If MySQL’s native escaping function is applied incorrectly, then it can still leave gaping security holes in a web application.

Parametrized queries can be applied like so:

<?php
$name = "O'Reilly";
$email = "In'valid@email.com";

// Procedural API
$insQuery = mysqli_prepare($db, 'INSERT INTO table VALUES (NULL, ?, ?)');
mysqli_stmt_bind_param($insQuery, 'ss', $name, $email);
mysqli_stmt_execute($insQuery);

// Object-oriented API
$insQuery = $db->prepare('INSERT INTO table VALUES (NULL, ?, ?)');
$insQuery->bind_param('ss', $name, $email);
$insQuery->execute();

When creating a prepared statement, we insert question marks (not surrounded by quotes, otherwise they will be treated as string values) to identify where the values are bound in the query. These are called placeholders, and in this instance are unnamed because MySQLi does not support named placeholders (unlike PDO). Once we have prepared the query, we can then bind the parameters.

Focusing on the object-oriented API, the first parameter of the bind_param() method specifies the argument types we would like to cast the values to when binding them. There are four types: s (string), i (integer), d (double), and b (blob). All must be used in lower-case only, and the number of letters indicating the parameter types must match the number of values that need binding (even if all of the values are going to be cast the same).

A new parameter in the bind_param() method is then passed for each value that needs binding to the prepared query. The binding values will be cast respectively to the type order in the first parameter. We can then execute our prepared statement with all of the values safely inserted into the query.

Multi-Queries

The next major feature introduced with MySQLi is support for multiple statements. This is where we are able to stack SQL statements in one query by separating them with a semi-colon, and then execute them all at once. This is a particularly efficient feature, but can also be destructive if an out-sourced value in the query has not been properly escaped before being used.

With multi-queries, we are unable to use parametrized queries because they are not able to prepare multiple statements in one go. The statements would have to be prepared individually, which would require more code, however the tradeoff is greater legibility and less potential for human error.

Transactions

The final feature we’ll look at is support for transactions, provided you’re using the InnoDB storage engine. Transaction are often described as having ACID properties:

  • Atomicity states that an action will either fully happen, or not at all.
  • Consistency is where the new data coming into the database will conform with the current rules set in place. If the incoming data does not validate, then it will all be returned.
  • Isolation refers to the state of the transaction in progress, which must remain alone from other transactions until it is fully completed.
  • Durability speaks in terms of a database being able to withstand loss of updated data after a failure, for example power loss. It says that once a transaction has been committed, it will remain so.

Transactions are commonly used when we have a block of SQL statements that are inter-dependent on one-another. This makes it all the more important to ensure that either they all happen successfully and are committed as such, or all fail.

We can test the theory of transactions by performing a repeated insertion on a table where a field has a unique key constraint set upon it:

<?php
mysqli_autoCommit($db, false);

$resultA = mysqli_query($db, "INSERT INTO table_name
    VALUES (NULL, 'Tom', 'email@address.com')");
if ($resultA === false) {
    mysqli_rollback($db);
}

$resultB = mysqli_query($db, "INSERT INTO table_name
    VALUES (NULL, 'Tom', 'email@address.com')");
if ($resultB === false) {
    mysqli_rollback($db);
}

mysqli_commit($db);

When creating the transaction, we first call upon the mysqli_autoCommit() function. It’s second parameter (or first parameter if you’re using the object-oriented API), should be set false, disallowing the database to save your changes as you issue each query. This is required to prevent any changes from being saved before you’re sure that the transaction has been completely successful. Once a change has been saved, it cannot be rolled back.

We then perform our queries, checking if they failed; if any failed, then the changes would not affect the database. If, however, there are no rollbacks during the transaction, then we can successfully commit all of the changes with mysqli_commit(). In the example above, our second insert statement would have failed because of a duplicate entry being submitted, meaning that nothing at all will be inserted into the database.

Summary

This article has demonstrated the ease of switching to the newer, and more familiar MySQLi API, as well as giving you two very good reasons why you should forgo using the MySQL extension: it is currently being deprecated (and will be officially deprecated as of PHP 5.5.0), and the extension has become extremely out-dated. I hope this makes migrating to the newer MySQLi API a little easier, so that we can finally see the back of the MySQL extension altogether.

Don’t forget to check out the PHP manual for more information about MySQLi and its library of functions and built-in classes, and stay tuned for part two when we look at PDO.

Image via Fotolia

Avoid the MySQL Extension

<< Avoid the Original MySQL Extension, Part 2

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • narasimha rao

    good article. very much useful

  • Fernando Benitez

    Just a small typo on the first example mysqli:
    Instead of ‘password);
    Must be: ‘password’);

    • Thomas Punt

      Hey Fernando, thanks for the correction. The article has been updated.

  • Nick

    I have over 12 years of code that was written on the MySQL extension. Fortunately is was all loaded via a custom class. I replaced one class file with a new one based on PDO and all my legacy code now uses PDO and I have further extended the class to support parametized queries for new development. Rewriting 12ish functions in one file its good to go

    The real lesson is to never tie your code to any particular 3rd party library including mysqli or PDO or anything new. Abstract your database always

  • Roger

    Awesome buddy, since long time no touch in mysql but you make us to remember those days of SQL ACID ;-)

  • Murray

    Thanks for this article, very interesting. I’m currently switching over some websites to use MySQLi and I’ve run into a small problem. I like to use procedural rather than object code.
    How do I use prepared statements with a simple SELECT? After executing the statement I’d like to end up with an associated array that holds the fetched row, but from what I’ve read you have to use mysqli_stmt_bind_result() to bind the result to variables. This isn’t so bad if only a few columns are returned, but this fails badly if the row has lots (>20) of columns as the mysqli_stmt_bind_result() gets unwieldy.
    Can anyone suggest anything? Have I overlooked something?

    • http://phpmaster.com/author/tpunt/ Thomas Punt

      Hello Murray,
      Thanks for commenting. In regards to your problem, have you tried the “mysqli_stmt_get_result()” function? The function returns a result set, which can then be used in the “mysqli_fetch_array()” function to return an associative array.

      • Murray

        Thanks for the quick reply, Thomas. Unfortunately my host is still running PHP 5.2.17 and mysqli_stmt_get_result() is only available from PHP 5.3.0 onwards. Looks like I’ll just have to bind the result to variables manually.

  • http://brokenbulb.site40.net/blog/ Rahul

    Really nice article. I’ve seen a lot of projects which use the mysql extension. I personally prefer PDO because of the database support and the named parameters being important factors.

  • nick stockley

    Great tutorial, I have always used the old mysql and I was starting to thing about moving to OOP but you have made mysqli sooo easy to understand I was able to update the script i was working on in less than 5 mins by simply rewriting my connection string then using find and replace throughout the directory. Now im cooking with gas and future proof for at least another two years.