An introduction to MySQLi – with some examples

Will PHP soon stop supporting MySQL? It is exactly this question that somewhat shook the PHP community, with the appearance of an error message, when trying to connect to a server using the old extension mysqlaccompanied by an invitation to switch to ext/mysqli. This obsolescence notification has been published since PHP 5.5. However, the extension mysqli had made its first appearance with PHP 5.0. As a reminder: it was in 2004.

But where did this panic come from? Oracle’s PHP team found that users of ext/mysqlincluding heavyweights like WordPress, were still numerous. This is what pushed Oracle to begin a slow and gradual process of obsolescence. But like everything has an end, the extension mysql was stopped at the time of the introduction of PHP 7. This article presents the successor MySQLi in detail, using a few examples. We will also analyze what differentiates these two extensions.

What is MySQLi?

MySQLi (the letter “i” comes from “improved” in English, which means improved) is an extension of the PHP programming language, allowing access to MySQL databases. MySQL is one of the most widely used relational database management systems (DBMS) in the world, along with Oracle and Microsoft SQL Server. Relational databases represent a central element of the Internet, because they allow the processing and recording of large volumes of data. Large data sets are broken down into smaller volumes before being linked together, if necessary.

The software designed in 1994 by the Swedish company MySQL AB is distributed to this day by Oracle Corporation using a dual licensing system. In addition to its proprietary Enterprise version, Oracle offers a free, open source version. This dual form of licensing gives companies the possibility of developing their own applications based on MySQL, without having to submit to the Open Source license.

What does the mysqli extension contain?

In PHP, there are three different ways to access a MySQL database. The oldest is based on the MySQL extension, which has however been made obsolete since the arrival of PHP 5.5 and which has been removed since PHP 7. In PHP 7 the function mysql() is no longer usable, and has been replaced by mysqli().

To be able to access the MYSQL database, PHP had, in addition to the old extension mysqlPHP Data Objects (PDO), particularly flexible in its use. The third variation uses MySQL Improved Extension. Since PHP version 5, the extension mysqli allows access to MySQL databases. The code snippet below gives you an idea of ​​what MySQLi is.

Code snippet: Send SQL query to database

To send a query to the database, we use the method query($sql) :

connect_errno) {
    die("connexion a échoué: " . $mysqli->connect_error);
}
 
$sql = "UPDATE tabelle SET colonne="Valeur" WHERE id = 1";
$mysqli->query($sql);
?>

What are the advantages of MySQLi?

Unlike its predecessor, the extension mysqli can be used not only procedurally, but also with a object orientation. One of the advantages of object-oriented programming is that it is easier to maintain and modify the code. For example, new classes are proposed, and they inherit the properties and behaviors of existing classes. This significantly reduces programming time and makes it easier to adapt the program to new requirements and a constantly changing environment.

Another very important advantage of MySQLi: the Prepared Statements. A Prepared Statement is a prepared query intended for use in a database. Unlike an ordinary statement, it does not contain any parameter values, but markers. If a Statement with different parameters must be executed several times (in a loop for example) on a database, it is interesting to use Prepared Statements which will improve processing speed. Indeed, the Statement has already been pre-interpreted in the database, and must simply be executed with the new parameters. Prepared Statements also have the advantage of preventing SQL injections, since the database checks the validity of the parameters before executing the query.

Code fragment: queries prepared in MySQLi

Here’s what an example of prepared queries looks like in MySQLi:

connect_errno) {
    die("connexion a échoué: " . $mysqli->connect_error);
}
$sql = "UPDATE user SET email = ?, motdepasse = ? WHERE id = ?";
$statement = $mysqli->prepare($sql);
$statement->bind_param('ssi', $email, $passwort, $id);

//Assigner des valeurs variables
$id= 1;
$email = "un@exemple.fr";
$passwort = "nouveau mot de passe";
$statement->execute();
?>

Using bind_param()you associate the SQL query parameters with variables. The first argument of bind_param() in the example below, with the value ssidescribes the parameter types. ssi indicates that there are three parameters in the query, the first is of type stringthe second of type string and the third of type integer. For floating point numbers, there is still the value d.

Once the variables are associated with the parameters, they are assigned the corresponding value, and the prepared query is sent to the database using $statement->execute(). Compared to PDO, it is much more complicated.

mysqli() vs. mysql(): why was this PHP function changed?

The move to MySQLi was inevitable, because the old extension mysql was simply obsolete. The code became more and more difficult to maintainbecause it was always necessary to ensure its compatibility with previous versions. The code dates from the days of PHP and MySQL, its development was not carried out in a very optimal way.

For example, since no connection resource was explicitly defined, all functions attempted to use the last established connection. It could even happen that we could access a completely different database with mysql_query(). The login ID was optional in the old function. In the new extension it is mandatory. Prepared Statements have also been added which make it easier and more secure to read data in database tables.

On a practical level, many functions can be modified by adding an -i to the function mysql(). However, there are some differences between the two extensions.

Code snippet: login ID in MySQL and MySQLi

Some functions mysqli() require a login ID, a PHP variable, created when connecting to the database. In this example, it is: $link.

Code snippet: Read data into a database table

The function mysqli_query() requires a login, which is not the case for the function mysqli_fetch_array().

$name - $text - $datum";
}
?>

Beyond the functions we have already mentioned, the following functions also require a login ID:

In conclusion: MySQLi is faster and more secure

The transition to MySQLi was essential to make access to databases faster. In the new extension, Prepared Statements have been added which increase the security of the connection, because they protect against SQL injections. The database system checks the validity of the parameters before processing the query. This new code also has the advantage of being an object-oriented code, easier to maintain.