Command out of Sync when running a query inside a query with PHP

Question

Im facing an issue where I am trying to run a query within a query. I am using a php script that will make an api call to update tracking details to ebay for sale records.

The way it works is that it first does a select on the table for any records that are not completed. Then for the first record, it will make the api call and then obtain the ack in the response. If the Ack is a success, it will update the sale as completed, and insert a log record. If it is a failure, it will not update the record, and again insert a log record. It will then move on to the next record and repeat the same action, and so forth for the rest of the sales.

Now when I go to run it, I receive the message of "Commands out of sync; you can't run this command now". This only happens on the second sale record and any of the recordsafter.

I've tried closing the connection and free_result, but nothing seems to work.

I was thinking of storing all the sales in an array but i dont if that is smart or possible.

I noted this post had a solution, but I cant seem to apply it to my scenario. Please any help would be appreciated!

Here is my code:

<?php
echo "Start update of sales";

//Database query for pending orders
$servername = "localhost";
$username = "XXX";
$password = "XXXX";
$dbname = "XXXX";
$conn = mysqli_connect($servername, $username, $password, $dbname);
//Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$query = "SELECT ss.`item_id`, ss.`transaction_id`, ss.`tracking_number` FROM `sales_table` ss WHERE ss.`completed`<> 1";   
$result = mysqli_query($conn, $query) or die(mysql_error());
while($row = mysqli_fetch_array($result)){
echo $row['transaction_id']. " - ". $row['item_id'];
echo "<br />";
$tranid = $row['transaction_id'];
echo $tranid;

///Build the request Xml string
$requestXmlBody = '<?xml version="1.0" encoding="utf-8"?>';
$requestXmlBody .= '<CompleteSaleRequest xmlns="urn:ebay:apis:eBLBaseComponents">';
...
$requestXmlBody .= '</CompleteSaleRequest>';    

//Create a new eBay session 
$session = new eBaySession($userToken, $devID, $appID, $certID, $serverUrl, $compatabilityLevel, $siteID, $verb);
//send the request and get response
$responseXml = $session->sendHttpRequest($requestXmlBody);
//print $responseXml;
PrintUtils::printXML($responseXml);
$xml = simplexml_load_string($responseXml);

foreach ($xml->Ack as $Ack) {  
        echo "Status: ".$Ack ."<br>";

        { $sql = "UPDATE `sales_table` SET `completed`=1, `completed_date`=now(), `update_date`=now() where `transaction_id`='$tranid' and `item_id`='$itemid';";
          $sql .= "INSERT INTO `sales_log` (`item_id`, `transaction_id`, `status`, `short_desc`, `long_desc`, `log_date`) VALUES ('$itemid', '$tranid', '$Ack', '$Ack', '$Ack', CURRENT_TIMESTAMP);";
    }}

    if (mysqli_multi_query($conn, $sql)){

    echo "New record created successfully <br>";
        } else {
            echo "Error inserting records. " . $sql ."<br>" . mysqli_error($conn);
        }} 
mysqli_free_result($result);mysqli_close($conn);?>

P.s Im still learning PHP, so any help is appreciated.

Answer 1

How does this do?

$rows=array();
if($result=mysqli_query($conn,$query)){
    while(($rows[]=mysqli_fetch_assoc($result)) || array_pop($rows));
    mysqli_free_result($result);
}else{
    die(mysqli_error($conn));
}

foreach($rows as $row){
    $tranid=$row['transaction_id'];
    echo $row['transaction_id']." - ".$row['item_id']."<br>";

    //Build the request Xml string
    //Create a new eBay session 
    //send the request and get response
    //print $responseXml;

    $sql=array();
    foreach($xml->Ack as $Ack){
        echo "Status: ".$Ack."<br>";
        $sql["Update"]="UPDATE `sales_table` SET `completed`=1, `completed_date`=now(), `update_date`=now() where `transaction_id`='$tranid' and `item_id`='$itemid'";
        $sql["Insert"]="INSERT INTO `sales_log` (`item_id`, `transaction_id`, `status`, `short_desc`, `long_desc`, `log_date`) VALUES ('$itemid', '$tranid', '$Ack', '$Ack', '$Ack', CURRENT_TIMESTAMP)";
    }

    if(mysqli_multi_query($conn,implode(';',$sql))){
        do{
            list($current_key,$current_query)=each($sql);   //advances array pointer to first or next element
            if(mysqli_affected_rows($conn)<1){
                echo "Query logic error @ $current_key where TranID=$tranid & Ack=$Ack<br>";
            }else{
                echo "New record created where TranID=$tranid & Ack=$Ack<br>";
            }
        } while(mysqli_more_results($conn) && mysqli_next_result($conn));
    }else{
        list($current_key,$current_query)=each($sql);   //advances array pointer to first element
    }
    if($error_mess=mysqli_error($conn)){
        echo "Query syntax error @ $current_key where TranID=$tranid & Ack=$Ack<br>$error_mess<br>";
    }
    $error_mess=""; // clear for next iteration
} 
mysqli_close($conn);