PHP mysqli_multi_query(): Execute Multiple SQL Statements At Once

The mysqli_multi_query() function is used to execute one or multiple queries which are concatenated by a semicolon.

Here's a basic tutorial on how to use the mysqli_multi_query() function in PHP:

Syntax:

The syntax of mysqli_multi_query() is:

mysqli_multi_query ( mysqli $link , string $query ) : bool

$link: A mysqli link identifier returned by mysqli_connect().
$query: One or multiple queries to execute.

Return Value:

This function returns TRUE if the first query was successful, FALSE otherwise.

Example:

Suppose we have a MySQL database with a table named Users. Let's insert two new records into this table using mysqli_multi_query():

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

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

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

// Our queries
$sql = "INSERT INTO Users (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com');";
$sql .= "INSERT INTO Users (firstname, lastname, email) VALUES ('Jane', 'Doe', 'jane@example.com');";

// Execute multi query
if ($conn->multi_query($sql)) {
   echo "New records created successfully";
} else {
   echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

In this example, we first create a connection to the database using mysqli(). We then define two queries to insert new records into the Users table. These queries are concatenated into one string, separated by a semicolon. We execute these queries using mysqli_multi_query(). If the queries execute successfully, a success message is printed. Otherwise, the error is printed.

Note:

Be careful when using mysqli_multi_query() with user-supplied input, as this can open your code up to SQL Injection attacks. Always sanitize and validate your input.

PHP mysqli_multi_query() example code:

Example demonstrating the use of mysqli_multi_query() to execute multiple SQL statements.

$mysqli = new mysqli("localhost", "username", "password", "database");

$sql = "INSERT INTO table1 (column1) VALUES ('value1');";
$sql .= "INSERT INTO table2 (column2) VALUES ('value2');";

if ($mysqli->multi_query($sql)) {
    do {
        // Process results
    } while ($mysqli->more_results() && $mysqli->next_result());
} else {
    echo "Error: " . $mysqli->error;
}

$mysqli->close();

Handling multiple SQL queries in PHP:

Use mysqli_multi_query() to handle multiple SQL queries, and loop through the results.

$mysqli = new mysqli("localhost", "username", "password", "database");

$sql = "SELECT * FROM table1; ";
$sql .= "UPDATE table2 SET column2 = 'new_value';";

if ($mysqli->multi_query($sql)) {
    do {
        if ($result = $mysqli->store_result()) {
            // Process results
            $result->free();
        }
    } while ($mysqli->more_results() && $mysqli->next_result());
} else {
    echo "Error: " . $mysqli->error;
}

$mysqli->close();

mysqli_multi_query() vs mysqli_query() in PHP:

Compare mysqli_multi_query() for multiple queries and mysqli_query() for a single query.

$mysqli = new mysqli("localhost", "username", "password", "database");

// Using mysqli_query for a single query
$result1 = $mysqli->query("SELECT * FROM table1");

// Using mysqli_multi_query for multiple queries
$sql = "SELECT * FROM table1; SELECT * FROM table2;";
$result2 = $mysqli->multi_query($sql);

$mysqli->close();

Transactional queries with mysqli_multi_query() in PHP:

Execute a transaction with multiple SQL statements using mysqli_multi_query().

$mysqli = new mysqli("localhost", "username", "password", "database");

$sql = "START TRANSACTION; ";
$sql .= "INSERT INTO table1 (column1) VALUES ('value1'); ";
$sql .= "UPDATE table2 SET column2 = 'new_value'; ";
$sql .= "COMMIT;";

if ($mysqli->multi_query($sql)) {
    do {
        // Process results
    } while ($mysqli->more_results() && $mysqli->next_result());
} else {
    echo "Error: " . $mysqli->error;
}

$mysqli->close();

Error handling with mysqli_multi_query() in PHP:

Implement error handling to manage issues during the execution of multiple queries.

$mysqli = new mysqli("localhost", "username", "password", "database");

$sql = "INSERT INTO table1 (column1) VALUES ('value1'); ";
$sql .= "INVALID SQL;"; // This will cause an error

if ($mysqli->multi_query($sql)) {
    do {
        if ($result = $mysqli->store_result()) {
            // Process results
            $result->free();
        }
    } while ($mysqli->more_results() && $mysqli->next_result());
} else {
    echo "Error: " . $mysqli->error;
}

$mysqli->close();

PHP mysqli_multi_query() security considerations:

Be cautious with user input and ensure proper validation to prevent SQL injection.

$mysqli = new mysqli("localhost", "username", "password", "database");

$userInput = $_POST['input']; // Validate and sanitize user input
$sql = "INSERT INTO table1 (column1) VALUES ('$userInput');";

if ($mysqli->multi_query($sql)) {
    do {
        // Process results
    } while ($mysqli->more_results() && $mysqli->next_result());
} else {
    echo "Error: " . $mysqli->error;
}

$mysqli->close();

Combine SELECT, UPDATE, INSERT with mysqli_multi_query() in PHP:

Use mysqli_multi_query() to combine SELECT, UPDATE, and INSERT queries.

$mysqli = new mysqli("localhost", "username", "password", "database");

$sql = "SELECT * FROM table1; ";
$sql .= "UPDATE table2 SET column2 = 'new_value'; ";
$sql .= "INSERT INTO table3 (column3) VALUES ('value3');";

if ($mysqli->multi_query($sql)) {
    do {
        if ($result = $mysqli->store_result()) {
            // Process results
            $result->free();
        }
    } while ($mysqli->more_results() && $mysqli->next_result());
} else {
    echo "Error: " . $mysqli->error;
}

$mysqli->close();