MySQLi: a new development in PHP

Will PHP stop supporting MySQL soon? This is the question that has weighed on the minds of those in the PHP community since an error message started appearing when you connect to a MySQL server via the old mysql extension recommending that you switch to ext/mysqli. This deprecation notice has been around since PHP 5.5., while the mysqli extension was first introduced in PHP 5.0. Just as a reminder, that was back in 2004.

But where did this feeling of uncertainty come from? The PHP team at Oracle must have realized that many were still using ext/mysql, including industry heavyweights like WordPress. So, Oracle decided to initiate a slow deprecation process. However, everything must come to an end at some point, so the mysql extension was removed with the introduction of PHP 7. In this article, we will introduce the successor MySQLi in detail with examples and discuss what is different between the two extensions.

Contents

What is MySQLi?
What does the mysqli extension include?
What are the advantages of using MySQLi?
mysqli() vs. mysql(): why was the PHP function changed?
Conclusion: MySQLi is faster and more secure

IONOS S3 Object Storage

The IONOS S3 Object Storage is ideal for backups as well as archiving company data. You can store any amount of static data for a reasonable price.

Highly scalable

Cost-effective

Convenient

What is MySQLi?

MySQLi is an improved extension (the -i stands for “improved”) of PHP for accessing MySQL databases. MySQL is one of the world’s most popular relational database management systems (DBMS) alongside Oracle and Microsoft SQL Server. Relational databases are a core part of the internet because they make it possible to process and store large amounts of data in the long term. This involves splitting up complex data sets into subsets and placing them in relation to one another as required.

Developed in 1994 by the Swedish company MySQL AB, this software is now distributed by the Oracle Corporation using a dual licensing system. In addition to the proprietary Enterprise Edition, Oracle offers a GPL-licensed open-source edition. This dual licensing allows companies to develop their own applications based on MySQL without being bound to a license.

What does the mysqli extension include?

There are three different ways to access a MySQL database. The oldest one uses the MySQL extension, which was deprecated as of PHP 5.5 and fully removed in PHP 7. The mysql() function no longer works in PHP 7. It has been replaced with mysqli().

In addition to the old mysql extension, MySQL databases can also be accessed in PHP using the PHP Data Objects (PDO) extension, which is particularly versatile in its use. The third way uses the MySQL Improved Extension. It has been possible to use the mysqli extension to access MySQL databases as of PHP 5. The following code snippet is an example of PHP MySQLi.

Code snippet: sending an SQL query to a database

The query($sql) method is used to send queries to a database:

<?php
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_errno) {
    die("Connect Error: " . $mysqli->connect_error);
}

$sql = "UPDATE table SET column = 'value' WHERE id = 1";
$mysqli->query($sql);
?>

What are the advantages of using MySQLi?

Unlike its predecessor, the mysqli extension uses both a procedural and an object-oriented approach. One advantage of object-oriented programming is that once the code has been written, it can be easily maintained and modified in the future. For example, new classes can be created which inherit properties and behaviors from existing classes. This significantly shortens development time and makes it easier to adapt the program to a changing environment or new requirements.

Another major advantage of MySQLi is its use of prepared statements. A prepared statement is a statement template for a database system. Unlike normal statements, these contain placeholders instead of parameter values. If a statement needs to be executed multiple times (e.g. inside a loop) on a database system with different parameters, using prepared statements can increase the speed since the statement is already pre-translated in the database system and only needs to be executed with the new parameters. In addition, prepared statements can effectively prevent SQL injections since the database system verifies the validity of the parameters before they are processed.

Code snippet: prepared statements in MySQLi

The following is an example of a prepared statement in MySQLi:

<?php
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_errno) {
    die("Connect Error: " . $mysqli->connect_error);
}
$sql = "UPDATE user SET email = ?, password = ? WHERE id = ?";
$statement = $mysqli->prepare($sql);
$statement->bind_param('ssi', $email, $password, $id);

//Assign values to variables
$id= 1;
$email = "an@example.com";
$password = "new password";
$statement->execute();
?>

Using bind_param(), the parameters in an SQL query are bound to the variables. In the example found above, the first argument found in the bind_param() function is ssi. This argument describes the types of parameters. The argument ssi indicates that there are three parameters in the query: the first type is a string, the second is also a string and the third is an integer. The value d still exists for floating point integers.

After the parameters have been bound to the variables, the corresponding values are assigned to them and the prepared statement is sent to the database using $statement->execute(). Compared to PDO, this is much more complicated.

Managed Kubernetes from IONOS

The easy way to manage container workloads. Fully automated Kubernetes cluster setups and maximum visibility and control of K8s clusters.

Persistent Storage

24/7 expert support

Automated cluster setup

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

The switch to MySQLi was unavoidable because the old mysql extension was quite simply outdated. Furthermore, backwards compatibility was always a priority for the extension which made it difficult to maintain the code. The code dates back to the early days of PHP and MySQL, and was not optimally developed in some respects.

For example, if the connection identifier was not explicitly defined, all functions would try to use the last one specified. Very unlucky users might even find that mysql_query() accessed a completely different database. The connection identifier was optional in the old function, but it is required in the new extension. In addition, prepared statements have been added to make retrieving data from a database table faster and more secure.

Conveniently, many functions can be modified by just adding an -i to the mysql() function. However, there are also some differences between the two extensions.

Code snippet: connection identifiers in MySQL and MySQLi

Some mysqli() functions require a connection identifier – a PHP variable created when connecting to the database. In this example, it is called $link.

<?php
// mysql() to establish a connection:
mysql_connect("localhost", "root", "", "test");

// mysqli() to establish a connection:
$link = mysqli_connect("localhost", "root", "", "test");
?>

Code snippet: retrieving data from a database table

The mysqli_query() function requires a connection identifier; however, the function mysqli_fetch_array() does not.

<?php
$link = mysqli_connect("localhost", "root", "", "test");

// Retrieve data sets:
$datasets = mysqli_query($link,
 "SELECT `name`, 'text', 'date' FROM 'messages'");

// Output data sets:
while (list($name, $text, $date) = mysqli_fetch_array($dataset)) {
echo "<p>$name - $title - $text - $date</p>";
}
?>

In addition to the previously mentioned function, the following functions also require a connection identifier:

Conclusion: MySQLi is faster and more secure

The switch to MySQLi was necessary to improve the speed at which databases could be accessed. Prepared statements were introduced in the new extension which also improve connection security since they can prevent SQL injections. The database system verifies whether the parameters are valid before processing them. In addition, the new code is easier to maintain due to its object-oriented approach.