How You Can Simplify MySQL Prepared Query Execution in PHP 8.2 Using mysqli::execute_query

Continue to read this article below to learn about:

2. How to Use Prepared Queries with the MySQLi Extension

4. Shall You Use MySQLi or PDO PHP Extensions to Access Databases in PHP

1. What is MySQLi Extension

MySQLi is an extension that lets PHP developers access MySQL databases with MySQL native driver. It is a successor of the MySQL extension that was discontinued in PHP 7.

The MySQLi extension provides features that make it faster and help PHP developers to implement more secure use than the old MySQL database.

2. How to Use Prepared Queries with the MySQLi Extension

The built-in support to prepare queries is one feature that makes using MySQLi extension functions more secure.

Using the MySQL extension functions to execute queries, and you would have to compose the SQL query string using functions like mysql_real_escape_string.

Although this function works in PHP versions before PHP 7, it was a delicate matter to compose queries with parameters taken from external sources like the HTTP request variables.

Prepared queries are SQL strings that have placeholder marks. When the prepared query is executed, these marks will be replaced by parameter values. Let's see an example.

Consider the following declaration of SQL query to retrieve the record of a user with a given email address. The question mark symbol (?) in the query will be replaced with the parameter's value when the query is executed.

The execute function will escape the email parameter value before executing the query. This way, you can avoid SQL injection security attacks.

    $sql = 'SELECT id, name FROM users WHERE email = ?';

    $parameters = array( 'mlemos@gmail.com' );

    $driver = new mysqli_driver();

The SQL query execution starts here:

    $stmt = $mysqli->prepare($sql);
    if (!($driver->report_mode & MYSQLI_REPORT_STRICT) && $mysqli->error) {
        return false;
    }
 
    $stmt->execute($parameters);
    if (!($driver->report_mode & MYSQLI_REPORT_STRICT) && $stmt->error) {
        return false;
    }
 
    $results = $stmt->get_result();

3. What is the mysqli::execute_query Function in PHP 8.2

As you may have seen in the example code above, you would need to write 7 lines of helpful code to execute a prepared query using the MySQLi extension functions before PHP 8.2.

It would work well, but it could be better if you would write less code because it would require you to take less time and be more productive.

Fortunately PHP 8.2 version introduces a new function in the MySQLi extension named  execute_query.

This function combines most of the code above into 3 lines of useful code, thus taking you less time write the code to execute a prepared query.

Let's see an updated version of the example code above that works in PHP 8.2 version or later to the same.

    $results = $mysqli->execute_query($sql, $parameters);

    if (!($driver->report_mode & MYSQLI_REPORT_STRICT) && $mysqli->error) {
        return false;
    }

4. Shall You Use MySQLi or PDO PHP Extensions to Access Databases in PHP

As you may know, PHP has another database extension called PDO. This extension is not specific to any SQL database type.

There are some advantages and disadvantages to using one extension or the other. Let's take a look at the following comparison, so you can decide which database extension is better to use in your PHP projects.

I can add more criteria to compare the advantages and disadvantages for you to evaluate. For now, let's analyze these criteria, so you can decide what is better for you.

My advice is that if you are starting a new project, you should use PDO as a database access layer because it has more advantages.

If you are working on an existing project, stick with the database extension you already use unless you use MySQLi. You want to make your project more database independent. In that case, you need to refactor the code to migrate the MySQLi-based PHP code to PDO.

Suppose you want to make your project faster and continue to use the MySQL database. In that case, it may not be worth the effort to migrate your application code to use PDO because in real-world PHP applications, most of the time, your PHP code will be waiting for the queries to execute, and the database server returns its results.

To improve the performance, what may be worth to improve the speed of your application can be any of the following approaches:

4.1. Query Result Caching

Use database results caching techniques as much as possible to reduce the number of queries your application makes to the database.

You can use local files if you only have a single machine to store the cache files. In that case, you can try this PHP file cache class.

You can also use a cache server like Memcache to store the cached results in the central database server, so it can scale better in an environment that is distributed to several machines. In this case, you can use the PHP Memcache extension.

4.2. Optimize Slow SQL Queries

Benchmark and optimize slow queries. I have covered this topic in recent episodes of the Lately in PHP podcast about MySQL query optimization.

4.3. Asynchronous Programming

Use asynchronous programming, so your application can execute parallel tasks while it waits for the database query server to execute the query and return results. In PHP you can use the swoole extension for instance.

4.4. Your Suggestions

Some other approaches that you may suggest. If you have a suggestion, please comment below on this article so we can learn about it from you.