How Can I Execute Multiple MySQL Queries Simultaneously in PHP and Handle Separate Result Sets?

Executing Multiple MySQL Queries as One in PHP/MySQL

Introduction

Executing multiple SQL queries simultaneously can improve efficiency in database processing. This article explores how to execute two MySQL queries as one in PHP/MySQL, addressing the need to handle separate result sets effectively.

Problem Definition

Consider the following two MySQL queries:

SELECT SQL_CALC_FOUND_ROWS Id, Name FROM my_table WHERE Name LIKE '%prashant%' LIMIT 0, 10;
SELECT FOUND_ROWS();

The first query fetches data from the my_table, and the second query calculates the total number of rows in the table.

PHP Execution

Typically, these queries are executed separately using the mysql_query() function. However, this approach does not provide a way to handle the individual result sets separately.

$result1 = mysql_query($query1);
$result2 = mysql_query($query2);

Multiple Query Execution

To execute both queries in a single attempt, the mysqli_multi_query() function can be used. This function allows multiple queries to be executed sequentially:

$mysqli = new mysqli('hostname', 'username', 'password', 'database_name');

mysqli_multi_query($mysqli, $query1 . ';' . $query2);

Handling Result Sets

After executing the queries, the result sets can be retrieved using the mysqli_store_result() function:

$result1 = $mysqli->store_result();
$result2 = $mysqli->store_result();

Now, you can iterate through each result set individually, similar to how it is handled in ASP.NET using datasets:

while ($row1 = $result1->fetch_assoc()) {
    // Process row data from the first query
}

while ($row2 = $result2->fetch_assoc()) {
    // Process row data from the second query
}

Note

It is important to note that the mysqli_multi_query() function should only be used when the individual queries are independent and do not depend on each other's results.

The above is the detailed content of How Can I Execute Multiple MySQL Queries Simultaneously in PHP and Handle Separate Result Sets?. For more information, please follow other related articles on the PHP Chinese website!