How to use real_query function to handle complex SQL statements

gitbox 2025-06-04

What is mysqli_real_query ?

mysqli_real_query is a method of mysqli object, which is responsible for sending a complete SQL query statement to the MySQL server without directly returning the result. This function returns true when it executes successfully, and false when it fails. The query result needs to be obtained through other functions such as mysqli_store_result or mysqli_use_result .

Compared to mysqli_query (which calls real_query internally and automatically processes the results), real_query allows developers to control query and result processing step by step, which is especially important for complex queries.

Use scenarios

Complex query optimization : Complex queries on large batches of data or multiple statements can avoid consuming a large amount of memory at one time.
Multi-result set processing : When executing multiple SQL statements, the results can be parsed one by one to improve stability.
Asynchronous query : Used in conjunction with mysqli_more_results and mysqli_next_result to implement non-blocking query operations.

Example: Execute complex SQL statements using mysqli_real_query

 <?php
$mysqli = new mysqli("gitbox.net", "user", "password", "database");

if ($mysqli->connect_errno) {
    die("Connection failed: " . $mysqli->connect_error);
}

// A complexSQLStatement，May contain multiple subqueries or joint queries
$sql = "SELECT u.id, u.name, COUNT(p.id) AS post_count
        FROM users u
        LEFT JOIN posts p ON u.id = p.user_id
        WHERE u.status = 'active'
        GROUP BY u.id
        HAVING post_count > 10
        ORDER BY post_count DESC";

if ($mysqli->real_query($sql)) {
    $result = $mysqli->store_result();
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            echo "userID: " . $row['id'] . ", name: " . $row['name'] . ", Number of posts: " . $row['post_count'] . "\n";
        }
        $result->free();
    } else {
        echo "No result is returned or the result is empty。\n";
    }
} else {
    echo "Query execution failed: " . $mysqli->error;
}

$mysqli->close();
?>

Tips to improve query efficiency and stability

Step-by-step processing result set <br> After using real_query , the developer needs to actively call store_result or use_result to get the result. This saves memory when processing large data sets and avoids loading all results at once.
Processing multiple result sets <br> If the executed SQL statement contains multiple commands, real_query will be executed in turn. Cooperate with mysqli_more_results to determine whether there are more results. Mysqli_next_result switches the result set, which can handle multi-result queries stably.
Combination of transaction and lock mechanism <br> Complex queries often involve multiple tables and transactions. First, use real_query to submit SQL, combine begin_transaction and commit to ensure data consistency and improve stability.
Error checking refinement <br> Get detailed error information through $mysqli->error , and combine real_query to more conveniently locate syntax or logical errors in complex SQL.

Summarize

mysqli_real_query provides PHP developers with a more underlying and flexible SQL execution interface. It is particularly suitable for processing complex or multiple SQL statements. Through step-by-step execution and result processing, it can effectively improve the efficiency and stability of query. In scenarios where more refined control of database interactions is required, learning to use real_query is an important means to optimize the performance of PHP database applications.

How to use real_query function to handle complex SQL statements