PHP mysqli multi_query() Method
The PHP mysqli::multi_query() / mysqli_multi_query() function is used to execute one or multiple queries which are concatenated by a semicolon.
Queries are sent asynchronously in a single call to the database, but the database processes them sequentially. This function waits for the first query to complete before returning control to PHP. The MySQL server will then process the next query in the sequence. Once the next result is ready, MySQL will wait for the next execution of mysqli_next_result() from PHP.
It is recommended to use do-while to process multiple queries. The connection will be busy until all queries have completed and their results are fetched to PHP. No other statement can be issued on the same connection until all queries are processed. To proceed to the next query in the sequence, mysqli_next_result() function can be used. If the next result is not ready yet, mysqli will wait for the response from the MySQL server. To check if there are more results, mysqli_more_results() function can be used.
For queries which produce a result set, such as SELECT, SHOW, DESCRIBE or EXPLAIN, mysqli_use_result() or mysqli_store_result() can be used to retrieve the result set. For queries which do not produce a result set, the same functions can be used to retrieve information such as the number of affected rows.
Syntax
//Object-oriented style public mysqli::multi_query(query) //Procedural style mysqli_multi_query(mysql, query)
Parameters
mysql |
Required. For procedural style only: Specify a mysqli object returned by mysqli_connect() or mysqli_init(). |
query |
Required. Specify a string containing the queries to be executed. Multiple queries must be separated by a semicolon. |
Return Value
Returns false if the first statement failed. To retrieve subsequent errors from other statements, mysqli_next_result() function can be called first.
Example: Object-oriented style
The example below shows the usage of mysqli::multi_query() method.
<?php //establishing connection to the database $mysqli = new mysqli("localhost", "user", "password", "database"); if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: ". $mysqli->connect_error; exit(); } //string containing multiple queries $sql = "SELECT CURRENT_USER();"; $sql .= "SELECT Name FROM Employee"; //executing multiple queries $mysqli->multi_query($sql); do { //storing the result set in PHP if ($result = $mysqli->store_result()) { while ($row = $result->fetch_row()) { printf("%s\n", $row[0]); } } //if there are more result-sets, printing divider if ($mysqli->more_results()) { printf("--------------\n"); } } while ($mysqli->next_result()); //closing the connection $mysqli->close(); ?>
The output of the above code will be similar to:
user@localhost -------------- Marry Kim John Adam
Example: Procedural style
The example below shows the usage of mysqli_multi_query() function.
<?php //establishing connection to the database $mysqli = mysqli_connect("localhost", "user", "password", "database"); if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: ". mysqli_connect_error(); exit(); } //string containing multiple queries $sql = "SELECT CURRENT_USER();"; $sql .= "SELECT Name FROM Employee"; //executing multiple queries mysqli_multi_query($mysqli, $sql); do { //storing the result set in PHP if ($result = mysqli_store_result($mysqli)) { while ($row = mysqli_fetch_row($result)) { printf("%s\n", $row[0]); } } //if there are more result-sets, printing divider if (mysqli_more_results($mysqli)) { printf("--------------\n"); } } while (mysqli_next_result($mysqli)); //closing the connection mysqli_close($mysqli); ?>
The output of the above code will be similar to:
user@localhost -------------- Marry Kim John Adam
❮ PHP MySQLi Reference