What is mysqli_multi_query?

mysqli_multi_query is a function in the PHP MySQLi extension that allows you to execute multiple SQL statements in a single function call. These statements are concatenated into a single string and separated by semicolons (;).

This is fundamentally different from mysqli_query, which only executes a single SQL statement per call. mysqli_multi_query is essential for executing batches of SQL, such as a set of INSERT statements, or complex operations that involve creating tables and then populating them.

Key Characteristics and Workflow

1. Semicolon-Separated: The SQL commands must be separated by a semicolon (;), just as you would write them in a MySQL client.
2. Combined Results: It can return multiple result sets (e.g., from multiple SELECT statements).
3. Sequential Processing: The results of the statements must be processed in sequence. You can't access the result of the third query until you've processed the results of the first and second.
4. Security Warning: EXTREME CAUTION IS REQUIRED. If any part of the SQL string comes from user input, you must escape it to prevent SQL Injection attacks. Using prepared statements with multi_query is not straightforward, so it's often best used with static, hard-coded SQL batches.

The standard workflow for using mysqli_multi_query is:

1. Formulate a string containing multiple SQL statements.
2. Execute the string using mysqli_multi_query().
3. Check if the first result is available.
4. Process the first result (if it's a SELECT, SHOW, etc.).
5. Close the first result set.
6. Check if there are more results and repeat steps 4-5.
7. Check for errors during the entire process.

Example 1: Basic Usage with Multiple INSERTs

This example shows how to insert multiple rows in a single call, which can be more efficient than individual queries.

<?php
// Create a database connection
$mysqli = new mysqli("localhost", "username", "password", "testdb");

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Create a multi-query string
$sql = "
    INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
    INSERT INTO users (name, email) VALUES ('Jane Smith', 'jane@example.com');
    INSERT INTO users (name, email) VALUES ('Bob Johnson', 'bob@example.com');
";

// Execute the multi-query
if ($mysqli->multi_query($sql)) {
    // Loop through all results (even though these are INSERTS, they produce a result)
    do {
        /* Store the first result set (which is just a boolean for INSERTS) */
        if ($result = $mysqli->store_result()) {
            // For SELECT, you could fetch data here: $result->fetch_all(MYSQLI_ASSOC)
            $result->free(); // Free the result set
        }
        // Check if there are more results
    } while ($mysqli->next_result());

    echo "All queries executed successfully.";
} else {
    echo "Error: " . $mysqli->error;
}

// Close the connection
$mysqli->close();
?>

Explanation:

• The do...while loop is crucial. It calls $mysqli->store_result() to handle the current result and then $mysqli->next_result() to move the pointer to the next result set.
• Even INSERT statements produce a result set that needs to be cleared. store_result() and free() manage this process to prevent memory leaks and command out-of-sync errors.

Example 2: Handling a Mixed Workload (SELECT and UPDATE)

This example demonstrates processing different types of statements, including retrieving data from a SELECT.

<?php
$mysqli = new mysqli("localhost", "username", "password", "testdb");

$sql = "
    SELECT id, name FROM users WHERE name LIKE 'J%'; -- Get users whose names start with J
    UPDATE users SET email = CONCAT(name, '@company.com') WHERE email IS NULL; -- Update empty emails
";

if ($mysqli->multi_query($sql)) {
    // Process the first result set (the SELECT query)
    if ($result = $mysqli->store_result()) {
        echo "Users starting with 'J':<br>";
        while ($row = $result->fetch_assoc()) {
            echo " - " . $row['name'] . "<br>";
        }
        $result->free(); // Free the first result set
    }

    // Move to the next result (the UPDATE query)
    $mysqli->next_result();

    // The second result (from the UPDATE) just needs to be cleared, not fetched from
    // You can check the number of affected rows
    echo "Updated " . $mysqli->affected_rows . " rows.";

} else {
    echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

$mysqli->close();
?>

Critical Warning and Best Practices

• SQL Injection: This is the biggest risk. Never directly insert user input (like $_POST['name']) into a multi-query string. If you must use variables, escape them meticulously with $mysqli->real_escape_string().
• Use Transactions: For batches of INSERT/UPDATE/DELETE statements, wrap them in a transaction (START TRANSACTION; ... COMMIT;) to ensure all statements succeed or fail together, maintaining database integrity.
• Prefer Individual Queries: For most web application tasks, separate prepared statements are safer, more readable, and often just as performant. Use multi_query only for specific, advanced batch operations or schema changes.