How does mysqli::multi_query affect mysqli_stmt::prepare when only CREATE table is executed in multi_query?

Question

I had a very simple code in php to create a table and then use user input to store values in the table as following:

   $sql = 'CREATE TABLE admins (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      username VARCHAR(30) NOT NULL,
      password VARCHAR(30) NOT NULL,
      reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );';  
    $result = $conn->query($sql);
    if($result) {              
      $stmt = $conn->prepare("INSERT INTO `admins` (username, password) VALUES (?, ?)");         
      $stmt->bind_param("ss", $admin_name, $admin_pass1);
      $stmt->execute()

This was working smooth as... Then I needed to create two tables in one go and I changed my code to:

    $sql = 'CREATE TABLE admins (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      username VARCHAR(30) NOT NULL,
      password VARCHAR(30) NOT NULL,
      reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );
    CREATE TABLE categories (
      id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      datetime2 VARCHAR(50) NOT NULL, 
      name VARCHAR(100) NOT NULL,
      creatorname VARCHAR(200) NOT NULL
    );
    ';  
    $result = $conn->multi_query($sql);
    if($result) {
      //$result->free(); //$result is a boolean !!
      $stmt = $conn->prepare("INSERT INTO `admins` (username, password) VALUES (?, ?)");
      
      //var_dump($conn->error);
      $stmt->bind_param("ss", $admin_name, $admin_pass1);
      //here I get error, "can't bind to boolean"// ($stmt is false)
      $stmt->execute();

Upon research I found out that for multiple queries to be executed the result buffer has to cleared(fetched?) first, other wise you get the error Commands out of sync. php.net quotes:

MYSQLI_USE_RESULT - returns a mysqli_result object with unbuffered result set. As long as there are pending records waiting to be fetched, the connection line will be busy and all subsequent calls will return error Commands out of sync. To avoid the error all records must be fetched from the server or the result set must be discarded by calling mysqli_free_result().

Question 1: Do we need to fetch records in the default usage, where $result_mode = MYSQLI_STORE_RESULT, to execute multiple queries with mysqli::query?

Another caveat to the situation is that the $result from $result = $conn->multi_query($sql); in my case is a boolean, because the queries being used are non-DML. Now I thought that the $conn->prepare(...) failed because I needed to fetch the results of multi_query first, but since I am using non-dml query, there isn't any reults to fetched. I cannot use mysqli_result::free or anything similar like mysqli_next_result() or fetch(), because $result is not an object!

To add more to my confusion, my first code ran without errors, where I used only mysqli::query() and then $conn->prepare() in succession without clearing any buffers. So,

Question 2: What is happening under the hood here? why can't I use $conn->prepare after using $conn->multi_query? If there are any buffers to be cleared, then how can I clear them or why are there any bufferd with no results to be shown?

Answer 1

Just execute them using two separate calls to mysqli_query(). Don't use mysqli_multi_query().

When you use mysqli_multi_query(), you send multiple queries to the MySQL server in one go. MySQL does not execute them in parallel. In fact, MySQL will only execute the first one and return the result back to PHP. It is then the responsibility of PHP code to retrieve the first result (store_result()) and ask MySQL to send the next one (next_result()). See Multiple Statements on php.net.

As soon as the current pending result is fetched in full by PHP, MySQL will start executing the next query. This is the only tiny performance benefit of sending queries in bulk. PHP can do work in parallel while MySQL is preparing the next result.

$mysqli->multi_query('SELECT 1;SELECT SLEEP(5)');
// fetch the pending result. This let's MySQL start working on the next one
$res1 = $mysqli->store_result();

// PHP does something time consuming in the meantime
sleep(2);

// Tell MySQL to send the next result. PHP waits here...
$mysqli->next_result();
$res2 = $mysqli->store_result();

All queries produce results! You might not get mysqli_result object in PHP for some queries, but that doesn't mean that MySQL didn't send any response. When using mysqli_multi_query() you must fetch these results to PHP. Otherwise, the line will be busy and you won't be able to send anything else on the same connection.

Using mysqli_multi_query() is very cumbersome and has pretty much no benefits. If you find yourself needing to use it, then it's very likely you have done something wrong. Just send each query one by one or use prepared statements.

P.S. Don't forget to enable mysqli error reporting or you will have much worse experience with this whole functionality. How to get the error message in MySQLi?