How do I execute multiple MySQL queries in a single PHP script?

Executing Multiple MySQL Queries in PHP

When working with MySQL in PHP, it can be necessary to execute multiple queries together. This can be useful for creating views, temporary tables, and manipulating data in a single transaction.

Problem Scenario

Consider the following code snippet:

$query1 = "CREATE VIEW current_rankings AS SELECT * FROM main_table WHERE date = X";
$query2 = "CREATE VIEW previous_rankings AS SELECT rank FROM main_table WHERE date = date_sub('X', INTERVAL 1 MONTH)";
$query3 = "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)";
$query4 = "SELECT *, @rank_change = prev_rank - current_rank AS rank_change FROM final_output";

Our goal is to execute all four queries within a single PHP script, and then use mysql_fetch_array to retrieve the results of the fourth query. However, when attempting to do so, we encounter errors or receive an empty fetch array.

Solution

To execute multiple queries in a single PHP script, we can use mysqli_multi_query. Here's the updated code:

$link = mysqli_connect("server", "user", "password", "database");

$query = "
    CREATE VIEW current_rankings AS SELECT * FROM main_table WHERE date = X;
    CREATE VIEW previous_rankings AS SELECT rank FROM main_table WHERE date = date_sub('X', INTERVAL 1 MONTH);
    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);
    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));
}

Using mysqli_multi_query, we concatenate all four queries into a single string and execute them. Each result set is then stored in $result and can be retrieved using mysqli_fetch_array.

Alternative Approach

It's also possible to execute queries separately and store results in temporary tables. For example:

$query1 = "Create temporary table A select c1 from t1";
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'];
}

In this approach, the first query creates a temporary table named A. We then select data from A in the second query and retrieve the results.

Both approaches can be used depending on the specific requirements and database structure.

The above is the detailed content of How do I execute multiple MySQL queries in a single PHP script?. For more information, please follow other related articles on the PHP Chinese website!