Commands out of sync; you can't run this comma

Question

I am trying to execute my PHP code, which calls two MySQL queries via mysqli, and get the error "Commands out of sync; you can't run this command now".

Here is the code I am using

<?php
$con = mysqli_connect("localhost", "user", "password", "db");
if (!$con) {
    echo "Can't connect to MySQL Server. Errorcode: %s\n". Mysqli_connect_error();
    exit;
}
$con->query("SET NAMES 'utf8'");
$brand ="o";
$countQuery = "SELECT ARTICLE_NO FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE % ? %";
if ($numRecords = $con->prepare($countQuery)) {
    $numRecords->bind_param("s", $brand);
    $numRecords->execute();
    $data = $con->query($countQuery) or die(print_r($con->error));
    $rowcount = $data->num_rows;
    $rows = getRowsByArticleSearch("test", "Auctions", " ");
    $last = ceil($rowcount/$page_rows);
}  else {

print_r($con->error);
}
foreach ($rows as $row) {
    $pk = $row['ARTICLE_NO'];
    echo '<tr>' . "\n";
    echo '<td><a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')">'.$row['USERNAME'].'</a></td>' . "\n";
    echo '<td><a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')">'.$row['shortDate'].'</a></td>' . "\n";
    echo '<td><a href="#" onclick="deleterec(\'Layer2\', \'' . $pk . '\')">DELETE RECORD</a></td>' . "\n";
    echo '</tr>' . "\n";
}
function getRowsByArticleSearch($searchString, $table, $max) {
    $con = mysqli_connect("localhost", "user", "password", "db");
    $recordsQuery = "SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME, date_format(str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), '%d %m %Y' ) AS shortDate FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE '%?%' ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s')" . $max;
    if ($getRecords = $con->prepare($recordsQuery)) {
        $getRecords->bind_param("s", $searchString);
        $getRecords->execute();
        $getRecords->bind_result($ARTICLE_NO, $USERNAME, $ACCESSSTARTS, $ARTICLE_NAME, $shortDate);
        while ($getRecords->fetch()) {
            $result = $con->query($recordsQuery);
            $rows = array();
            while($row = $result->fetch_assoc()) {
                $rows[] = $row;
            }
            return $rows;
        }
    }
}

I have tried reading up on this, but I am unsure of what to do. I have read about store result and free result, however these have made no difference when using them. I am unsure at exactly which point this error is being caused, and would like to know why it is being caused, and how to fix it.

Going by my debug statements, the first if loop for countQuery is not even being entered, because of an error in my sql syntax near near '% ? %'. However if I just select * instead of trying to limit based on a LIKE clause, I still get the command out of sync error.

Answer 1

I call this function every time before using $mysqli->query Works with stored procedures as well.

function clearStoredResults(){
    global $mysqli;

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

}

Answer 2

I use CodeIgniter. One server OK ... this one probably older ... Anyway using

$this->db->reconnect();

Fixed it.

Answer 3

to solve this problem you have to store result data before use it

$numRecords->execute();

$numRecords->store_result();

that's all

Answer 4

I solved this problem in my C application - here's how I did it:

1. Quoting from mysql forums:

   This error results when you terminate your query with a semicolon delimiter inside the application. While it is required to terminate a query with a semicolon delimiter when executing it from the command line or in the query browser, remove the delimiter from the query inside your application.

2. After running my query and dealing with the results [C API: mysql_store_result()], I iterate over any further potentially pending results that occurs via multiple SQL statement execution such as two or more select statements (back to back without dealing with the results).

   The fact is that my procedures don't return multiple results but the database doesn't know that until I execute: [C API: mysql_next_result()]. I do this in a loop (for good measure) until it returns non-zero. That's when the current connection handler knows it's okay to execute another query (I cache my handlers to minimize connection overhead).

   This is the loop I use:

   for(; mysql_next_result(mysql_handler) == 0;) 
     /* do nothing */;
   

I don't know PHP but I'm sure it has something similar.

Answer 5

The problem is the MySQL client C library, which most MySQL APIs are built on. The problem is that the C library doesn't support simultaneous execution of queries, so all APIs built on top of that also do not. Even if you use unbuffered queries. This is one reason why the asynchronous MySQL API was written. It communicates directly with the MySQL server using TCP and the wire-protocol does support simultaneous queries.

Your solution is to either modify the algorithm so you don't need to have both in progress at once, or change them to use buffered queries, which is probably one of the original reasons for their existence in the C library (the other is to provide a kind of cursor).

Answer 6

This is not related to the original question, but i had the same error-message and this thread is the first hit in Google and it took me a while to figure out what the Problem was, so it May be of use for others:

i'm NOT using mysqli, still using mysql_connect i had some simple querys, but ONE query caused all other querys to fail within the same Connection.

I use mysql 5.7 and php 5.6 i had a table with the data-Type "JSON". obviously, my php-version did not recognize the return value from mysql (php just did not know what to do with the JSON-Format because the built-in mysql-module was too old (at least i think))

for now i changed the JSON-Field-Type to Text (as for now i don't need the native mysql JSON-functionality) and everything works fine