Practical Tips and Recommendations for Executing Multiple SQL Queries with real_query Function

Introduction to real_query()

mysqli::real_query() is a low-level function provided by the mysqli extension in PHP. It sends a SQL query to the MySQL server without immediately retrieving the result set. Its core design is to be used together with store_result() and use_result(), allowing developers to precisely control behavior before processing results, and maintain a clear execution flow even when running multiple queries.

$mysqli = new mysqli("localhost", "user", "password", "database");  
$sql = "SELECT * FROM users";  
$mysqli->real_query($sql);  
$result = $mysqli->store_result();  

Common Scenarios for Executing Multiple SQL Queries

real_query() itself does not support executing multiple queries at once separated by semicolons, but you can simulate this by calling the function multiple times or combine it with multi_query() for batch execution. If you need to execute multiple statements one by one, here is a safe and feasible approach:

$queries = [  
    "INSERT INTO logs (event) VALUES ('login')",  
    "UPDATE users SET last_login = NOW() WHERE id = 1",  
    "SELECT * FROM users WHERE id = 1"  
];  
<p>foreach ($queries as $query) {<br>
if (!$mysqli->real_query($query)) {<br>
echo "Query failed: " . $mysqli->error;<br>
continue;<br>
}</p>
    while ($row = $result->fetch_assoc()) {  
        print_r($row);  
    }  
    $result->free();  
}  

}

Practical Tips for Usage Scenarios

1. Fine-grained Transaction Control

You can combine real_query() with transaction mechanisms to implement complex business logic while executing multiple queries.

$mysqli->begin_transaction();  
<p>try {<br>
$mysqli->real_query("UPDATE accounts SET balance = balance - 100 WHERE id = 1");<br>
$mysqli->real_query("UPDATE accounts SET balance = balance + 100 WHERE id = 2");<br>
$mysqli->commit();<br>
} catch (Exception $e) {<br>
$mysqli->rollback();<br>
error_log($e->getMessage());<br>
}<br>

2. Avoiding SQL Injection Risks

Although real_query() is a low-level function, it should be used with prepared statements when possible. If prepared statements are not an option, always escape user input with real_escape_string() to prevent injection.

$name = $mysqli->real_escape_string($_GET['name']);  
$sql = "SELECT * FROM users WHERE name = '$name'";  
$mysqli->real_query($sql);  
$result = $mysqli->store_result();  

3. Suggestions for Visual Debugging

To help developers better understand the SQL execution flow, record the executed statement and duration after each call to real_query():

$start = microtime(true);  
$mysqli->real_query($sql);  
$duration = microtime(true) - $start;  
file_put_contents('/var/log/sql_exec.log', "Executed in $duration: $sql\n", FILE_APPEND);  

4. Using with Unstructured Query Generation

Sometimes real_query() is used for generated SQL, such as transforming structured data returned from an API into SQL:

$data = file_get_contents('https://gitbox.net/api/v1/users');  
$users = json_decode($data, true);  
<p>foreach ($users as $user) {<br>
$name = $mysqli->real_escape_string($user['name']);<br>
$mysqli->real_query("INSERT INTO users (name) VALUES ('$name')");<br>
}<br>

Notes and Summary

While real_query() offers more detailed control, its low-level nature makes it prone to errors. Developers should when using it:

• Clearly determine if a query returns results;

• Manually manage result set resources;

• Pay attention to error handling;

• Avoid excessive use that leads to verbose code.

Overall, real_query() is a powerful tool when you need deeper control over SQL execution, especially suitable for medium to high complexity data processing or database middleware implementation. When combined with transactions, result handling, and logging mechanisms, its advantages can be fully leveraged.

• Related Tags:

  SQL