Commands out of sync; you can't run this command now on $mysqli->prepare

Question

I have a PHP function called getNumRows:

function getNumRows() {
    $query = $this->mysqli->prepare("CALL GetNumRows('$this->tableName')") or die('Unable to prepare: ' . $this->mysqli->error);
    $query->execute();
    $query->store_result();
    $query->bind_result($rowCount);
    while ($query->fetch()) {
        $numRows = $rowCount;
    }
    $query->close();

    return $numRows;
}

Which uses a Stored Procedure CALL GetNumRows('TableName'):

DROP PROCEDURE gpstrack.GetNumRows;
CREATE DEFINER=`******`@`localhost` PROCEDURE `GetNumRows`( IN tab_name VARCHAR( 40 ) )
BEGIN 
SET @t1 = CONCAT(  'SELECT COUNT(*) FROM ', tab_name) ;
PREPARE stmt3 FROM @t1 ;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
END;

But it fails on row #1 of my function with:

Unable to prepare: Commands out of sync; you can't run this command now

How can I use this procedure to get the number of rows of any table?

possible duplicate of [mysqli giving "Commands out of sync" error - why?](http://stackoverflow.com/questions/3632075/mysqli-giving-commands-out-of-sync-error-why) — Bill Karwin, Mar 24 '14 at 21:02
Seems much different, as I am getting an error on my Prepare line. — grepsedawk, Mar 24 '14 at 21:04
Why you're using a stored procedure for such a trifle query? Why you're making it unsafe for SQL injection? — Your Common Sense, Mar 25 '14 at 04:58
I am not making it unsafe, as the table name is set ONLY in php, not from client in any way. — grepsedawk, Mar 25 '14 at 05:30

score 0 · Answer 1 · answered Mar 24 '14 at 21:49

I was able to fix it by using this PHP:

function getNumRows()
{
    $query = $this->mysqli->prepare("CALL GetNumRows('$this->tableName')") or die('Unable to prepare: ' . $this->mysqli->error);

    $query->execute();
    $query->store_result();
    $query->bind_result($rowCount);
    $query->fetch();
    $numRows = $rowCount;
    $this->mysqli->next_result();// ADDED THIS LINE
    $query->close();

    return $numRows;
}

Commands out of sync; you can't run this command now on $mysqli->prepare

1 Answers1