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 usingmysqli_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 theconnect_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'[email protected]"; $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, andmysqli_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’smysql_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'[email protected]"; // 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 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 protected]')"); if ($resultA === false) { mysqli_rollback($db); } $resultB = mysqli_query($db, "INSERT INTO table_name VALUES (NULL, 'Tom', '[email protected]')"); 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.
Janeth Kent
Licenciada en Bellas Artes y programadora por pasión. Cuando tengo un rato retoco fotos, edito vídeos y diseño cosas. El resto del tiempo escribo en MA-NO WEB DESIGN AND DEVELOPMENT.