How to Efficiently Run Multiple MySQL Queries in PHP?

Running Multiple Queries in PHP with MySQL

Consider a scenario where you need to execute multiple SQL queries within a single PHP script. One common approach is to use the mysqli_multi_query() function, which allows you to concatenate multiple queries into a single string.

Here's an example of how you might achieve this:

// Create database connection
$conn = mysqli_connect('server', 'user', 'password', 'database');

// Construct the multiple queries as a single string
$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 the multiple queries
if (mysqli_multi_query($conn, $query)) {
    // Process each result set
    do {
        // Check for the first result set
        if ($result = mysqli_store_result($conn)) {
            // Fetch and display the results
            while ($row = mysqli_fetch_array($result)) {
                echo $row['player'] . ' ' . $row['current_rank'] . ' ' . $row['prev_rank'] . ' ' . $row['rank_change'];
            }
            // Free the result set
            mysqli_free_result($result);
        }
    } while (mysqli_next_result($conn));
} else {
    // Handle any errors
    echo mysqli_error($conn);
}

Alternatively, if you wish to execute each query separately, you can use the mysqli_query() function multiple times:

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

// Select from temporary table A
$query2 = "select c1 from A";
$result2 = mysqli_query($conn, $query2) or die(mysqli_error($conn));

// Fetch and display the results
while ($row = mysqli_fetch_array($result2)) {
    echo $row['c1'];
}

Choosing between concatenating multiple queries into a single string via mysqli_multi_query() or executing them separately via mysqli_query() depends on your specific requirements and performance considerations.

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