The series Using X with PHP mysqli continues. After notes on calling stored procedures and using prepared statements, its time for a multiple statement quickstart. A mighty tool, if used with care…

Using Multiple Statements with mysqli

MySQL optionally allows having multiple statements in one statement string. Sending multiple statements at once reduces client-server round trips but requires special handling.

Multiple statements or multi queries must be executed with mysqli_multi_query(). The individual statements of the statement string are seperated by semicolon. Then, all result sets returned by the executed statements must be fetched.

The MySQL server allows having statements that do return result sets and statements that do not return result sets in one multiple statement.

$mysqli = new mysqli("localhost", "root", "", "test");

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT)"))
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;

$sql = "SELECT COUNT(*) AS _num FROM test; ";
$sql.= "INSERT INTO test(id) VALUES (1); ";
$sql.= "SELECT COUNT(*) AS _num FROM test; ";

if (!$mysqli->multi_query($sql))
 echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;

do {
 if ($res = $mysqli->store_result()) {
  var_dump($res->fetch_all(MYSQLI_ASSOC));
  $res->free();
 }
} while ($mysqli->more_results() && $mysqli->next_result());

array(1) {
  [0]=>
  array(1) {
    ["_num"]=>
    string(1) "0"
  }
}
array(1) {
  [0]=>
  array(1) {
    ["_num"]=>
    string(1) "1"
  }
}

Security considerations

The API functions mysqli_query() and mysqli_real_query() do not set a connection flag for activating multi queries in the server. An extra API call is used for multiple statements to reduce the likeliness of accidental SQL injection attacks. An attacker may try to add statements such as ; DROP DATABASE mysql or ; SELECT SLEEP(999). If the attacker succeeds in adding SQL to the statement string but mysqli_multi_query() is not used, the server will not execute the second, injected and malicious SQL statement.

Prepared statements

Use of the multiple statement with prepared statements is not supported.

Happy hacking!

@Ulf_Wendel