How to run multiple SQL call procedures in a single PHP file

Question

I get a

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

error when I try to run my second SQL store procedure. The first call executes fine, but when I call the second SQL procedure elsewhere in my code I see this error. Do I have to close the first connection before adding the second one?

First call procedure:

<center><?php
    $sql="call DisplayRandomTable('db', 'table1', 10)";
    $result = mysqli_query($conn, $sql);
?></center>

Second call procedure: //Errors here

<?php
    $sql="call GetTableCount('bista', 'S_Student')";
    $result = mysqli_query($conn, $sql);
?>

Answer 1

I hope you are in best of health. This is because you can not have two simultaneous queries because mysqli uses unbuffered queries by default for prepared statements. You can either fetch the first one into an array and loop through that, or tell mysqli to buffer the queries.

You can use the below code to buffer the queries:

$stmt->store_result()

To know more details related to this, I recommend you to read mysqli_query article in detail.

Also, you can work it using store procedures too. It means your query will behave like multi-query. You can get help from the below code.

while($this->mysql->more_results())
{
    $this->mysql->next_result();
    $this->mysql->use_result();
}

A Trick: I have made a function that i call after executing mysqli_query. This help me in removing the previous fetched results from the buffer.

function clearStoredResults()
{
    global $conn;
    do
    {
        if ($res = $conn->store_result())
        {
            $res->free();
        }
    }
    while ($conn->more_results() && $conn->next_result());
}