How to Efficiently Execute Multiple MySQL Queries in PHP?

MySQL Multiple Queries with PHP

In PHP, executing multiple MySQL queries in a single request can be challenging. This article addresses a common situation where multiple interdependent queries need to be combined for efficient processing.

The initial approach was to create individual views using the provided queries. However, this resulted in errors or empty result sets. To address this, PHP offers the mysqli_multi_query() function, which allows for the execution of concatenated queries.

Here's an example code snippet:

/* Create database connection */
$link = mysqli_connect("server", "user", "password", "database");

$query = "CREATE VIEW current_rankings AS SELECT * FROM main_table WHERE date = X;";
$query .= "CREATE VIEW previous_rankings AS SELECT rank FROM main_table WHERE date = date_sub('X', INTERVAL 1 MONTH);";
$query .= "CREATE VIEW final_output AS SELECT current_rankings.player, current_rankings.rank as current_rank LEFT JOIN previous_rankings.rank as prev_rank ON (current_rankings.player = previous_rankings.player);";
$query .= "SELECT *, @rank_change = prev_rank - current_rank as rank_change from final_output;";

/* Execute multiple queries using mysqli_multi_query() */
if (mysqli_multi_query($link, $query)) {
    do {
        /* Store current result set */
        if ($result = mysqli_store_result($link)) {
            while ($row = mysqli_fetch_array($result)) {
                echo $row['player']. $row['current_rank']. $row['prev_rank']. $row['rank_change'];
            }
            mysqli_free_result($result);
        }
    } while (mysqli_next_result($link));
}

This approach combines all the queries into a single string and executes them concurrently. The mysqli_multi_query() function returns TRUE upon successful execution.

If the execution plan requires additional queries, they can be appended to the $query string with semicolons (;) as separators. Each result set can be stored in a separate variable using mysqli_store_result() and fetched using mysqli_fetch_array() within a loop.

Alternatively, you can also execute queries separately using the mysqli_query() function:

$query1 = "Create temporary table A select c1 from t1"; 
$result1 = mysqli_query($link, $query1) or die(mysqli_error());

$query2 = "select c1 from A"; 
$result2 = mysqli_query($link, $query2) or die(mysqli_error());

while($row = mysqli_fetch_array($result2)) {
    echo $row['c1'];
}  

This approach allows for greater control over query execution and result handling.

The above is the detailed content of How to Efficiently Execute Multiple MySQL Queries in PHP?. For more information, please follow other related articles on the PHP Chinese website!