English
English
When operating a database in PHP, one of the common requirements is to execute multiple SQL statements to complete complex transactions or batch data processing. The mysqli_multi_query() function is designed for this, which allows multiple SQL statements to be executed at once, improving efficiency and simplifying code. This article will explain in detail how to correctly execute multiple SQL statements using mysqli_multi_query() after establishing a database connection using the connect() function and process the return result.
First, we establish a connection to the MySQL database through the connect() function. Suppose this is a packaged function that returns a valid mysqli object.
<?php
function connect() {
$host = 'localhost';
$user = 'root';
$password = 'password';
$database = 'testdb';
$conn = new mysqli($host, $user, $password, $database);
if ($conn->connect_error) {
die('Connection failed: ' . $conn->connect_error);
}
return $conn;
}
?>
Suppose we have multiple SQL statements to be executed, such as creating tables, inserting data, etc., and we can combine them into a semicolon ( ; )-delimited string and pass it to mysqli_multi_query() .
<?php
$conn = connect();
$sql = "
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
);
INSERT INTO users (username, email) VALUES ('Zhang San', 'zhangsan@m66.net');
INSERT INTO users (username, email) VALUES ('Li Si', 'lisi@m66.net');
";
if ($conn->multi_query($sql)) {
do {
// Store the first result set
if ($result = $conn->store_result()) {
while ($row = $result->fetch_assoc()) {
print_r($row);
}
$result->free();
}
// If there are more result sets，Continue the loop
} while ($conn->more_results() && $conn->next_result());
} else {
echo "Multiple statement execution failed: " . $conn->error;
}
$conn->close();
?>
SQL statement format : Multiple SQL statements must be separated by semicolons, and each statement has the correct syntax.
Result processing : After mysqli_multi_query() is executed, all result sets need to be traversed by looping store_result() and more_results() . Even if some statements do not return the result, call next_result() to jump to the next result.
Error handling : When execution fails, you can get specific error information through $conn->error for easy debugging.
