How do I apply mysqli_free_result()?

Question

I'm getting the "Commands out of sync" error which seems to be typically resolved by freeing the result of a previous query, but I'm not sure how to apply this to my scenario.

I've attempted to free results from other queries in various locations of the php script but the error does not resolve. Examples I find have a query formatted like $result = $mysqli->query() then free the $result. I'm not using this style of result, but if I should, I am open to modifying my approach.

      //Check if active checklist table exists and if not create it
      $activeExist = $mysqli->query("CALL TEST('active')");

      while ($row = $activeExist->fetch_row()) {
        echo $row[0];
        error_log($row[0]."\n\n", 3, "/var/tmp/my-errors.log");
        if ($row[0] == "2") {
          //$activeExist->free();
          $sql = "CREATE TABLE IF NOT EXISTS active (
                      id varchar(10) NOT NULL PRIMARY KEY,
                      title varchar(100)) SELECT '$activeID' AS 'id', '$realname' AS 'title';";
          $mysqli->query($sql);
          error_log("Active then statement from dbTableGen ".$sql."\n\n", 3, "/var/tmp/my-errors.log");
          error_log("Active (" . $mysqli->errno . ") " . $mysqli->error ."\n\n", 3, "/var/tmp/my-errors.log");
          //$mysqli->query("INSERT INTO active VALUES ('$activeID', '$realname')");
        }
        else {
          error_log("Active else statement from dbTableGen"."\n\n", 3, "/var/tmp/my-errors.log");
          $mysqli->query("INSERT INTO active VALUES ('$activeID', '$realname')");
        }
      }

  }
  //mysqli_free_result();
  mysqli_close($mysqli);

Expected result: creation of a table called "active" and inserting of values IF the table does not already exist OR just inserting the values IF the table already exists.

Actual result: Commands out of sync; you can't run this command now

The procedure being called looks like this: `CREATE PROCEDURE TEST(IN tablename VARCHAR(15)) BEGIN IF EXISTS(SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE `table_name LIKE tablename) THEN select 1; ELSE select 2; END IF; END$$` — user11273111, Mar 28 '19 at 15:52
Possible duplicate of [Why is mysqli giving a "Commands out of sync" error?](https://stackoverflow.com/questions/3632075/why-is-mysqli-giving-a-commands-out-of-sync-error) — Progman, Mar 28 '19 at 17:54

score 0 · Answer 1 · answered Mar 29 '19 at 13:47

I have security/stability concerns because you are directly writing variables into your iterated queries, but generally, just cache the CALLs results, the fire off your individual queries.

Additionally, you should always endeavor to minimize total trips to the database. I recommend performing just one INSERT query rather than many.

$activeExist = $mysqli->query("CALL TEST('active')");
$call_results = $activeExist->fetch_all(); // or loop if you don't have this driver installed

foreach ($call_results as $row) {
    echo $row[0];
    error_log($row[0] ."\n\n", 3, "/var/tmp/my-errors.log");
    if ($row[0] == "2") {
        //$activeExist->free();
        $sql = "CREATE TABLE IF NOT EXISTS active (
                  id varchar(10) NOT NULL PRIMARY KEY,
                  title varchar(100)) SELECT '$activeID' AS 'id', '$realname' AS 'title';";
        $mysqli->query($sql);
        error_log("Active then statement from dbTableGen " . $sql . "\n\n", 3, "/var/tmp/my-errors.log");
        error_log("Active (" . $mysqli->errno . ") " . $mysqli->error ."\n\n", 3, "/var/tmp/my-errors.log");
        //$mysqli->query("INSERT INTO active VALUES ('$activeID', '$realname')");
    } else {
        error_log("Active else statement from dbTableGen" . "\n\n", 3, "/var/tmp/my-errors.log");
        $mysqli->query("INSERT INTO active VALUES ('$activeID', '$realname')");
    }
}

If this doesn't solve things, let me know and I'll adjust. – mickmackusa Mar 29 '19 at 13:47 — mickmackusa, Mar 29 '19 at 13:47

How do I apply mysqli_free_result()?

1 Answers1