multi_query

In this article, we will focus on the mysqli_multi_query() function in PHP, which executes one or more SQL statements in a single call. We will cover its syntax, parameters, and return value, walk through a runnable example, and explain how to read back multiple result sets safely.

What is mysqli_multi_query()?

mysqli_multi_query() is a built-in PHP function that sends one or more SQL statements, separated by semicolons, to the MySQL server in a single round trip. It is part of the MySQLi extension, PHP's improved interface for talking to MySQL.

It is handy when you have a batch of statements to run at once — for example, seeding several tables on setup, or running a stored procedure that returns several result sets. Because each statement still executes independently on the server, mysqli_multi_query() is not a substitute for a transaction when you need all-or-nothing behavior (see When not to use it).

Syntax

The function works in both procedural and object-oriented styles:

// Procedural style
mysqli_multi_query(mysqli $mysqli, string $query): bool

// Object-oriented style
$mysqli->multi_query(string $query): bool

Parameters

Return value

It returns false if the first statement fails, otherwise true. A return value of true only means the first query was accepted — you must iterate over the results to detect errors in later statements.

How to use mysqli_multi_query()

Call the function on a valid MySQLi connection with a string of statements separated by semicolons, then loop through the result sets. Here is a complete example:

<?php
$mysqli = mysqli_connect("localhost", "username", "password", "database");

if (!$mysqli) {
    die("Connection failed: " . mysqli_connect_error());
}

$query = "INSERT INTO table1 VALUES ('value1', 'value2', 'value3');";
$query .= "UPDATE table2 SET column1 = 'newvalue' WHERE id = 1;";

if (mysqli_multi_query($mysqli, $query)) {
    do {
        if ($result = mysqli_store_result($mysqli)) {
            while ($row = mysqli_fetch_row($result)) {
                print_r($row);
            }
            mysqli_free_result($result);
        }
    } while (mysqli_next_result($mysqli));
} else {
    echo "Error: " . mysqli_error($mysqli);
}

mysqli_close($mysqli);
?>

Here is what each part does:

1. mysqli_connect() opens a connection to the MySQL database; we abort early if it fails.
2. We build a single string holding two statements separated by semicolons.
3. mysqli_multi_query() sends both statements to the server in one call.
4. The do...while loop reads each result set in turn. mysqli_store_result() buffers the current result, mysqli_fetch_row() reads its rows, and mysqli_next_result() advances to the next one.
5. mysqli_close() releases the connection.

Handling multiple result sets

This is the part developers most often get wrong. After a successful mysqli_multi_query(), you must consume every result set — even the empty ones produced by INSERT or UPDATE — before you can run another query on the same connection. Skipping this step triggers a "Commands out of sync" error.

The safe pattern is:

<?php
do {
    // Buffer the current result set, if any.
    if ($result = mysqli_store_result($mysqli)) {
        while ($row = mysqli_fetch_row($result)) {
            print_r($row);
        }
        mysqli_free_result($result);
    }
    // mysqli_more_results() avoids a spurious warning on the last loop.
} while (mysqli_more_results($mysqli) && mysqli_next_result($mysqli));
?>

mysqli_next_result() returns false when there are no more results, which exits the loop. Checking mysqli_more_results() first prevents a warning being emitted after the final statement.

When not to use it

mysqli_multi_query() is powerful but easy to misuse:

• Never pass user input directly into the query string. Concatenating untrusted data here is a classic SQL injection vector, and this function cannot use bound parameters. For anything involving user input, run each statement separately with prepared statements — see mysqli_prepare().
• It is not atomic. If the second statement fails, the first has already been committed. When several statements must succeed or fail together, wrap single queries in a transaction with mysqli_begin_transaction(), mysqli_commit(), and mysqli_rollback().
• Always check the return value and iterate the results to catch errors in statements after the first.

Conclusion

The mysqli_multi_query() function lets you execute several SQL statements in a single call, which is convenient for batch operations. The key to using it correctly is consuming every result set with mysqli_next_result() to avoid "Commands out of sync" errors, and reaching for prepared statements and transactions whenever user input or atomicity is involved.

Practice