Question

New in PHP: MySQLi

Answer 1

What is PHP MySQLi?
What does the mysqli extension cover?
What are the advantages of MySQLi?
mysqli () versus mysql (): why was the PHP extension changed?
Bottom line: MySQLi is more secure and faster

---

New in PHP: MySQLi

Will PHP stop offering technical support for MySQL anytime soon? : This was the question the PHP community asked when, when connecting to a MySQL server using the old mysql extension , an error message appeared and the recommendation to switch to ext / mysqli . This message started showing with PHP 5.5 version, but the mysqli extension had already been released with PHP 5.0, that is, in 2004..

And why so much nervousness? Among other things, because the team in charge of PHP at Oracle realized that there were still many who used ext / mysql , including heavyweights in the sector, such as WordPress. Oracle then decided to start a slow deprecation process , that is, a transition to leave ext / mysql behind and replace it with the new extension . However, the old extension didn't last much longer: it ceased to exist with the PHP7 version . In this article we describe in detail its successor, MySQLi, showing examples and explaining the specific differences between the two extensions.

Index

1. What is PHP MySQLi?
2. What does the mysqli extension cover?
3. What are the advantages of MySQLi?
4. mysqli () versus mysql (): why was the PHP extension changed?
5. Bottom line: MySQLi is more secure and faster

What is PHP MySQLi?

MySQLi is an improved extension (the final i is for improved ) of PHP for accessing MySQL databases. MySQL is, along with Oracle and Microsoft SQL Server, one of the most popular relational database management systems (that is, a Database Management System or DBMS) worldwide. Relational databases are a central element of the Internet, as they allow large amounts of data to be processed and stored permanently. To do this, they divide complex data sets into parts and then establish the necessary relationships between them..

This software , developed in 1994 by the Swedish company MySQL AB, is currently distributed by Oracle Corporation through a dual licensing system: in addition to the proprietary license for companies, Oracle also offers a GPL licensed and open source version. This double licensing system gives companies the opportunity to develop their own applications based on MySQL without having to resort to an open source license .

What does the mysqli extension cover?

In PHP there are three ways to access a MySQL database. The oldest is to use the MySQL extension, which, however, is considered outdated or deprecated since version PHP 5.5 and was completely removed with PHP 7. In this latest version, the mysql function no longer works and has been replaced by mysqli ..

In addition to the outdated mysql extension , to access a MySQL database, PHP also offers PHP Data Objects (PDO), the application of which is particularly flexible. The third option is to use the MySQL Improved Extension , that is, the mysqli extension , which already since PHP 5 allows access to MySQL databases. The following snippet or code snippet can help you get an idea of ​​how the MySQLi PHP extension works.

Snippet : send an SQL query to the database

To send queries or queries to the database, use the command query ($ sql) :

  <?php $mysqli = new mysqli("localhost", "user", "password", "database"); if ($mysqli->connect_errno) { die("error de conexión: " . $mysqli->connect_error); } $sql = "UPDATE tabla SET columna = 'Valor' WHERE id = 1"; $mysqli->query($sql); ?>  

What are the advantages of MySQLi?

Unlike its predecessor, the mysqli extension can be used not only procedurally, but also object-oriented . An advantage of object-oriented programming is that written code can be easily corrected and adapted later . This can be useful, for example, to create new classes that can inherit the behavior and properties of other existing classes. Thus, the development time is considerably shortened and the adaptation of the program to a changing environment or new requirements is facilitated.

Another important advantage of MySQLi are the prepared statements or prepared queries . These are, as it were, ready-made instructions for the database system. While conventional statements contain parameter values, prepared statements contain so-called placeholders or wildcards instead. When a statement with different parameters is executed in the database system several times (in a loop, for example), the prepared statements allow to increase the speed, since the orders themselves are already compiled in the database and they simply have to be executed with the new parameters. In addition, prepared statements are an effective preventive measure against SQL injections, since the database system has to check the validity of the parameters before processing them.

Snippet : prepared statements in MySQLi

In MySQLi, an example of prepared statements could have the following form:

  <?php $mysqli = new mysqli("localhost", "user", "Password", "database"); if ($mysqli->connect_errno) { die("error de conexión: " . $mysqli->connect_error); } $sql = "UPDATE user SET email = ?, contraseña = ? WHERE id = ?"; $statement = $mysqli->prepare($sql); $statement->bind_param('ssi', $email, $contraseña, $id); //Asignar valores a las variables $id= 1; $email = "[email protected]"; $contraseña = "nueva contraseña"; $statement->execute(); ?>  

The bind_param () command relates the parameters of the SQL query to the variables. In the example above, the first argument to bind_param () , which has the value ssi , describes the parameter types. In this case, ssi indicates that there are three parameters in the query: the first is of type string , the second is also of type and the third is of type integer . For floating point figures, there is also the value d .

Once the variables have been related to the parameters, the latter are assigned the corresponding values ​​and, using $ statement? Execute () , the prepared statement is sent to the database. Compared to the PDO extension, however, this process is clearly more complicated.

mysqli () versus mysql (): why was the PHP extension changed?

There was no choice but to switch to MySQLi, as the old mysql extension was out of date. Also, when developing the old extension, we had tried to make it compatible with previous versions, so that the code was difficult to update , since it dates from the beginning of PHP and MySQL and, in part, had not been developed from the better way.

If, for example, no connection resources were explicitly specified, all functions tried to use the last connection that occurred. In the worst case, mysql_query () could even access a totally different database. The connection identifier was optional in the old extension, while in the new one it must be specified. Prepared statements have also been added, which make reading the data from the database faster and more secure.

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

Snippet : connection identifier in MySQL and MySQLi

Some mysqli () functions require a connection identifier, that is, a PHP variable generated when connecting to the database. In the example below, it will be $ link .

  <?php // mysql() establecer conexión: mysql_connect("localhost", "root", "", "test"); // mysqli() establecer conexión: $link = mysqli_connect("localhost", "root", "", "test"); ?>  

Snippet : read data from database table

The mysqli_query () function requires a connection handle; the mysqli_fetch_array ( ) function , instead, no.

  <?php $link = mysqli_connect("localhost", "root", "", "test"); // Leer registros: $registros = mysqli_query($link, "SELECT `nombre`, `texto`, `fecha` FROM `mensajes`"); // Mostrar registros: while (list($nombre, $texto, $fecha) = mysqli_fetch_array($registros)) { echo "<p>$nombre - $titel - $texto - $fecha</p>"; } ?>  

In addition to the aforementioned function, the following functions, among others, also require a connection identifier:

• mysqli_set_charset ()
• mysqli_real_escape_string ()
• mysqli_insert_id ()

Bottom line: MySQLi is more secure and faster

The switch to MySQLi was necessary to increase the speed of access to the databases. Prepared statements or prepared queries were added to the new extension, which also improve connection security by preventing SQL injections. They manage to prevent them because they force the database system to check if the parameters are valid before processing them. As if that were not enough, the new code can be updated much easier thanks to object-oriented programming.

---