PHP MySQLi mysqli_multi_query() - Executing Multiple Queries

mysqli_multi_query()

The mysqli_multi_query() function executes one or more queries on the database. The commands must be in a single string and separated by semicolons (;).

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "mysqli_database";

$connection = mysqli_connect($server, $username, $password, $database);

if (mysqli_connect_errno()) {
    echo "Connection failed: " . mysqli_connect_error();
    exit();
}

// String with three separate SQL commands to be sent in a single batch
$sql = "SELECT * FROM mysqli_table WHERE vegetable = 'Carrot'; ";
$sql .= "SELECT * FROM mysqli_table WHERE vegetable = 'Cabbage'; ";
$sql .= "SELECT * FROM mysqli_table WHERE vegetable = 'Chicory'";

// Execute all three queries at once
if (mysqli_multi_query($connection, $sql)) {
    do {
        // Store and output the current result set
        if ($result = mysqli_store_result($connection)) {
            // $row[1] represents the fruit column
            while ($row = mysqli_fetch_row($result)) {
                printf("%s<br>", $row[1]);
            }
            mysqli_free_result($result);
        }
        
        // If more data sets are waiting in the background, add a separator
        // NOTE: mysqli_multi_query() executes all commands at once, but we must read the results 
        // sequentially. This loop reads the fruit from the first SELECT, then draws a line, moves 
        // to the second SELECT, and repeats the process until everything is outputted.
        if (mysqli_more_results($connection)) {
            printf("------------<br>");
        }
        
    } while (mysqli_next_result($connection)); // Move the pointer to the next result set
}

mysqli_close($connection);
?>

RESULT

The result will be displayed here...

Practical use: This function is extremely efficient when you want to retrieve data from several different tables in a single step or execute multiple operations at once (e.g., an INSERT followed by a SELECT). Below, take a look at how we process a triple result set:

Reduces the number of calls between PHP and the database (shorter round-trip time).

Requires special handling of the results using a do...while loop.

Used in combination with mysqli_next_result() and mysqli_store_result().