PHP - Getting: Commands out of sync; you can't run this command now

Question

I know that there were hundreds of similar questions, I have tried everything and nothing really worked for me.

I have got this function that is calling stored procedure in my MariaDB. This is returning array.

<?

class MyClass {

protected static $connection;

    public function connect() {    
        // Try and connect to the database
        if(!isset(self::$connection)) {
            self::$connection = new mysqli(SERVERNAME,USERNAME,PASS,DBNAME);
        }
        // If connection was not successful, handle the error
        if(self::$connection === false) {
            // Handle error - notify administrator, log to a file, show an error screen, etc.
            return false;
        }
        return self::$connection;
    }

    public function query($query) {
        // Connect to the database
        $connection = $this -> connect();

        // Query the database
        $result = $connection -> query($query);

        return $result;
    }

    public function quote($value) {
        $connection = $this -> connect();
        return $connection -> real_escape_string($value);
    }

public function CallStoredProc($query) {
    // Connect to the database
    $connection = $this -> connect();

    // Query the database
    $result = $connection -> query($query,MYSQLI_USE_RESULT);   //,

    if($result === false) {
        return false;
    }

    while ($row = $result -> fetch_assoc()) {
        $rows[] = $row;
    }

    $result->free();
    return $rows;
    }

function StoreProcessed($Name,$Price){
    //escape
    $Name = $this->quote($Name);
    $Price= $this->quote($Price);

    $SQL = "INSERT INTO Result (`Name`,`Price`) VALUES ('$Name','$Price');";
    $result = $this->query($SQL);

    }


    //the function I am using for processing: 
function Compare($ID) {

    $query = "CALL MyProcedure($ID);";
    $result =$this->CallStoredProc($query);

    /*After the array is returned I am looping trough each element of array 
    and storing this in DB with another function. */

    $Table = "<table>";
    foreach ($result as $key=>$val)
        {
            $Name   =   $result[$key]["Name"];  
            $Price  =   $result[$key]["Price"];
            $this->StoreProcessed($Name,$Price);

            //This is where the Commands out of sync is returned

            $Table = $Table. "<tr>
                        <td>$Name</td>
                        <td>$Price</td>
                            </tr>";
                }
            $Table = $Table. "</table>";
        return $Table;
    }

    }

My php file then looks like this:

<?
$auto = new MyClass();
$table = $auto->Compare(14);

echo $table;
?>

I am using MYSQLI_USE_RESULT, after the array is filled, I am using the mysqli_free_result as well. What else should I do?

Many thanks!

Answer 1

I've found answer here . Just added this function:

function free_all_results(mysqli $dbCon)
{
    do {
        if ($res = $dbCon->store_result()) {
            $res->fetch_all(MYSQLI_ASSOC);
            $res->free();
        }
    } while ($dbCon->more_results() && $dbCon->next_result());
}

And I called it before returning the results:

public function CallStoredProc($query) {
// Connect to the database
    $connection = $this -> connect();

    // Query the database
    $result = $connection -> query($query); //,
    mysqli_store_result($connection);

    if($result === false) {
        return false;
    }

    while ($row = $result -> fetch_assoc()) {
        $rows[] = $row;
    }

    $this->free_all_results($connection);
    return $rows;
}