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

  mysqli, php

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?

Source: Ask PHP

LEAVE A COMMENT