MySQLi multi_query - Execute Multiple Queries

In this tutorial, you will learn how to use the MySQLi multi_query() method in PHP to execute multiple SQL queries in a single call. This technique can help you run batch operations more efficiently, reducing the number of round-trips between your application and the MySQL server.

Introduction to MySQLi multi_query

The multi_query() method allows you to pass multiple SQL statements separated by semicolons to the MySQL server in one call. Unlike the standard query() method, which handles only one statement at a time, multi_query() is useful when you need to execute several queries sequentially, such as multiple inserts, updates, or a combination of different DML/DDL commands.

Prerequisites

Basic understanding of PHP and MySQLi extension
MySQL server installed and running
Access to a MySQL database with proper credentials
PHP 5.0 or higher (MySQLi extension enabled)

Setup Steps

Before using multi_query(), ensure you have a working MySQLi connection:

<?php
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "test_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

Using MySQLi multi_query() - Step-by-Step with Examples

Example 1: Executing Multiple INSERT Statements

Suppose you want to insert multiple rows into a table in one go. Instead of running several INSERT queries separately, use multi_query():

<?php
$sql = "
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');
INSERT INTO users (username, email) VALUES ('jane', 'jane@example.com');
INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com');
";

if ($conn->multi_query($sql)) {
    do {
        // Store first result set if any
        if ($result = $conn->store_result()) {
            $result->free();
        }
        // Check if there are more queries to process
    } while ($conn->more_results() && $conn->next_result());
    echo "Multiple records inserted successfully.";
} else {
    echo "Error: " . $conn->error;
}
?>

Example 2: Running Mixed Queries (DDL and DML)

You can also mix different kinds of queries, such as creating a table and inserting data immediately after:

<?php
$sql = "
CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    price DECIMAL(10,2)
);
INSERT INTO products (name, price) VALUES ('Laptop', 1200.00);
INSERT INTO products (name, price) VALUES ('Smartphone', 800.00);
";

if ($conn->multi_query($sql)) {
    do {
        if ($result = $conn->store_result()) {
            $result->free();
        }
    } while ($conn->more_results() && $conn->next_result());
    echo "Table created and records inserted successfully.";
} else {
    echo "Error: " . $conn->error;
}
?>

Explanation of Code

multi_query() accepts a string with multiple SQL commands separated by semicolons.
The do-while loop is used to process each individual result set or query outcome.
store_result() fetches the result set from SELECT queries or frees memory for others.
more_results() checks if there are more queries to process.
next_result() prepares the connection to fetch the next result.

Best Practices for Using MySQLi multi_query()

Always validate and sanitize your SQL strings to avoid SQL injection risks.
Use transactions for batch operations that must succeed or fail atomically.
Check for errors after each query, not just the first one, if needed.
Do not rely on multi_query() to simply concatenate user inputs without validation.
Free result sets using store_result()->free() to avoid buffering issues.

Common Mistakes When Using multi_query()

Not properly handling multiple result sets which can cause the connection to block.
Forgetting to separate SQL queries with semicolons.
Using query() method instead of multi_query() for multiple statements.
Ignoring security risks by using unsanitized input in multi-query strings.
Not calling next_result() or more_results() leading to incomplete execution.

Interview Questions

Junior-Level Questions

Q: What is the basic function of multi_query() in MySQLi?
A: It executes multiple SQL queries separated by semicolons in a single call.
Q: How do you separate multiple SQL statements when using multi_query()?
A: By using semicolons (;) between each SQL statement.
Q: What kind of MySQLi connection method does multi_query() belong to?
A: It is a method of the MySQLi object-oriented connection.
Q: Can you use multi_query() for a single SQL statement?
A: Yes, but typically query() is preferred for a single statement.
Q: What PHP structure do you commonly use to process multiple results after multi_query()?
A: A do-while loop with more_results() and next_result().

Mid-Level Questions

Q: What is the purpose of store_result() in the context of multi_query()?
A: It fetches the result set from the current query so you can process or free it.
Q: What problems might occur if you do not call next_result() after multi_query()?
A: The connection may block because subsequent queries remain unprocessed.
Q: How can transactions be combined with multi_query()?
A: You can wrap multi-query batches between BEGIN and COMMIT/ROLLBACK.
Q: Is it possible to retrieve results from SELECT queries inside multi_query()?
A: Yes, by using store_result() and processing the results inside the loop.
Q: When would you avoid using multi_query()?
A: When queries depend on results of previous queries or when security risks from concatenating user input exist.

Senior-Level Questions

Q: How does multi_query() affect server performance compared to consecutive single queries?
A: It reduces network round-trips, potentially improving performance when running many queries.
Q: Explain how SQL injection risks are amplified when poorly using multi_query().
A: Because multiple statements can be executed in one call, attackers could inject additional harmful queries.
Q: How would you handle error reporting for each query executed with multi_query()?
A: After processing each result with next_result(), check $conn->error to detect individual query errors.
Q: Describe a scenario where multi_query() is critical to implement batch database migrations.
A: When running several DDL statements sequentially (creating tables, altering columns) in a single deploy script.
Q: How can you handle retrieving mixed result sets (result sets + affected rows) from multi_query()?
A: By checking if store_result() returns a result or null, and then handling data fetch accordingly while iterating.

FAQ

What is the difference between `query()` and `multi_query()`?

query() handles one SQL statement per call, whereas multi_query() can execute multiple queries separated by semicolons at once.

Can `multi_query()` be used with prepared statements?

No, prepared statements do not support multiple statements in a single execution. multi_query() works with plain SQL strings.

How do I handle multiple result sets from SELECT statements inside `multi_query()`?

Use a loop with store_result() for each result, then fetch rows as usual before calling next_result() to move to the next result set.

Is it safe to execute user inputs via `multi_query()`?

Not unless you rigorously sanitize and escape inputs. Using prepared statements is safer as multi_query() allows multiple statements that can be exploited.

What happens if one query fails inside `multi_query()`?

The method returns false immediately on failure, but to pinpoint errors in subsequent queries, you must check after each next_result() call.

Conclusion

The MySQLi multi_query() method is a powerful tool to execute multiple SQL commands in one batch. It helps streamline batch operations and optimize communication with the MySQL server. However, proper handling of results, error checks, and security precautions like input sanitization are essential when working with this method. By following this tutorial, you are now equipped to implement multi_query() effectively in your PHP projects.

MySQLi multi_query Method