mysqli multi_query() Method

The 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

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