How to Efficiently Execute Multiple Queries in PHP/MySQL?

Executing Multiple Queries for Comprehensive Data in PHP/MySQL

When dealing with complex data analysis, running multiple queries in PHP/MySQL can provide a seamless solution. However, concatenating multiple queries and manually fetching results can be challenging. This article explores a technique using mysqli_multi_query($link, $query) to execute multiple queries at once.

Consider the following code snippet:

$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";

This code executes a series of queries to create views and select data. However, to fetch the results using mysql_fetch_array, the queries need to be executed as one concatenated query.

Using mysqli_multi_query

The mysqli_multi_query function allows you to execute multiple concatenated queries in a single operation. To do this, you need to:

1. Concatenate your queries with proper syntax.
2. Initialize your MySQL connection using mysqli_connect.
3. Use mysqli_multi_query to execute the concatenated queries.
4. Store the first result set using mysqli_store_result.
5. Iterate through the results using mysqli_fetch_array.

Example with mysqli_multi_query:

$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";

if (mysqli_multi_query($link, $query)) {
    do {
        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));
}

Alternative Method: Executing Queries Separately

Executing queries separately allows you to control the execution order and use temporary tables. To execute queries separately, you can use:

$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 method requires you to create temporary tables and run additional queries to retrieve data from them.

Choosing the right method depends on your specific needs. If you require a single result set from multiple queries, mysqli_multi_query is a suitable option. However, if you need more flexibility and control over the execution order, executing queries separately might be preferred.

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