php Commands out of sync; you can't run this command now with freed result

Question

Hi I have a couple of php classes. And I am passing a connection to a MySql database. When I make an instance of the firs class and try to call the invoice class with the method getinvoicedetails() I get error "Command out of sync; you can't run this command".

Any help wil be appreciated.

Here is the code:

    <?php //Invoicectrl class
    class invoicectrl
{
    // define properties
     public $invoices = Array();
     public $connection;
     public $userID;
     public $ownerID;

    // constructor
    public function __construct($userIDin,$ownerIDin) {
        $this->userID = $userIDin;
        $this->ownerID = $ownerIDin;

    }

     //function to set connection to db
    public function setconnection($conn){
        $this->connection = $conn;
    }
    public function getinvoicelist(){
        $res = Array();
        $invIDs = Array();

        //Get list of invoicenumbers and invoiceIDs
        $queryd = "CALL sp_getOwnersInvNumIDs (".$this->ownerID.")";
        $result = $this->connection->query($queryd) or die($this->connection->error.__LINE__);

        // GOING THROUGH invoices and getting there data
        $o=0;
        while($row = $result->fetch_assoc()) {
           $invIDs[$o]['ID'] = $row['invoiceID'];
           $invIDs[$o]['NR'] = $row['invoicenumber'];                               }
                   $result->free();

                   //Set each invoice own info
                   for($i=0;$i<=count($invIDs)-1;$i++){
                      $res[$i] = new invoice($invIDs['ID'],$invIDs['NR']);
                      $res[$i]->setconnection($this->connection);
           $res[$i]->getinvoicedetails();
                   } 

        return $res;
    }

}

?>

And here is the invoice class

    <?php //Invoice class
    class invoice
{
    // define properties
     public $invoiceID;
     public $invoicenumber;

     public $itemIDs=Array();
     public $items= Array();

     public $customerID;
     public $cust_company;
     public $cust_name;
     public $cust_email;
     public $cust_adress1;
     public $cust_adress2;
     public $cust_adress3;

     private $connection;

    // constructor
    public function __construct($invoiceIDin,$invoicenumberin) {
        $this->invoiceID = $invoiceIDin;
        $this->invoicenumber = $invoicenumberin;
    }

    //function to set connection to db
    public function setconnection($conn){
        $this->connection = $conn;
    }
        //sets all invoices details
    public function getinvoicedetails(){

                  //Get customer on invoice
                    $queryd = "select customerID FROM Accounting_Invoice_Customer where invoiceID =".$this->invoiceID."";
        $result = $this->connection->query($queryd) or die($this->connection->error.__LINE__);
                   while($row1 = $result->fetch_assoc()) {
            $this->customerID = $row1['customerID'];
                   }
                   $result->free();

                    //Get list of items on invoice
        $queryd = "call sp_getItems(".$this->invoiceID.")";
        $result = $this->connection->query($queryd) or die($this->connection->error.__LINE__);
        $o=0;
                   while($row2 = $result->fetch_assoc()) {
            $this->items[$o]['itemID'] = $row2['itemID'];

            //$this->items[$o]['itemnumber'] = $row['itemnumber'];
            //$this->items[$o]['itemdescription'] = $row['itemdescription'];
            //$this->items[$o]['itempricewotax'] = $row['itempricewotax']; 
            $o++;
        }       
                   $result->free(); 
    }
}
?>

I hope this [answer](http://stackoverflow.com/a/614741/92487) helps you. — Searock, Jan 30 '13 at 14:13

score 0 · Answer 1 · answered Jan 30 '13 at 14:54

When you run a SQL request, you have to get through the whole result or to close the cursore before sending another request.

The best thing to do, here is to get the whole result at first and then traverse it with a foreach loop to be able to send the second request. Moreover, it should be a good idea to use INNER JOIN in your sql query.

$queryd = "CALL sp_getOwnersInvNumIDs (".$this->ownerID.")";
$result = $this->connection->query($queryd) or die($this->connection->error.__LINE__);
$invIDs = $result->fetch_all();
$res = array();
foreach($invIDs as $inv){
     $inv = new invoice($invIDs['invoiceID'],$invIDs['invoiceNumber']);
     $inv->setconnection($this->connection);
     $inv->getinvoicedetails();
     $res[] =$inv;
}

php Commands out of sync; you can't run this command now with freed result

1 Answers1