How to pass DB table values with curl in a while loop?

Question

In below code, I am trying to pass values dynamically for "OrderNo & AWB".

$sql="SELECT order_id , alternateno FROM do_order";

$con=mysqli_connect("localhost","root","","do_management");

if ($result=mysqli_query($con,$sql))
{
    while ($row=mysqli_fetch_row($result))
    {
        $data = 
            array (
                'OrderNo' => '$row[order_id]', 
                'ManifestDetails' => 
                    array (
                        'AWB' => '$row[alternateno]',    
                        'PaymentStatus' => 'COD',   
                    ),
                );
    }
    mysqli_free_result($result);
}

mysqli_close($con);

$url = "http://1234.1234.1234.1234";
$data = json_encode($data);

$curl = curl_init($url);
$curl_response = curl_exec($curl);
curl_close($curl);
echo $curl_response ."\n";

Every time when I call URL in a browser, its display below error message:

"ReturnMessage":"AWB no with same order id ($row[order_id]) already exists","AWBNo":"$row[alternateno]"

But if I give static values for OrderNo (16445) & AWB (16445), then it works fine:

"ReturnMessage":"successful","AWBNo":"16445"

So it seems I am not passing values properly, please guide me on this.

Answer 1

mysqli_fetch_row() generates an array of indexed arrays. Access the result set data using the column indexes [0] for order_id and [1] for alternateno. You must also remove the single quotes when storing $row[0] and $row[1] in $data.

Right now, your query will be returning a result set of n rows. your while() loop will be overwriting and overwriting and overwriting $data and only preserving the final iteration's row data.

If you want to store all of the rows' data to $data, then write $data[] to push new row-data into your $data array.

Untested Code:

if (!$con = mysqli_connect("localhost", "root", "", "do_management")) {
    echo "connection error";
} elseif (!$result = mysqli_query($con, "SELECT order_id, alternateno FROM do_order")) {
    echo "query error";
} else {
    $url = "http://114.143.206.69:803/StandardForwardStagingService.svc/AddManifestDetails";
    while ($row = mysqli_fetch_row($result)) { // fetch_row does not generate associative keys
        $data = [
            'OrderNo' => $row[0], 
            'ManifestDetails' => ['AWB' => $row[1], 'PaymentStatus' => 'COD']
        ];

        $curl = curl_init($url);
        curl_setopt($curl, CURLOPT_HTTPHEADER, ["Content-Type: application/json"]);
        curl_setopt($curl, CURLOPT_HTTPAUTH, CURLAUTH_BASIC);
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($curl, CURLOPT_POST, true);
        curl_setopt($curl, CURLOPT_POSTFIELDS, json_encode($data));
        curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($curl, CURLOPT_VERBOSE, true);

        $curl_response = curl_exec($curl);
        curl_close($curl);
        echo $curl_response ."\n";
    }
    mysqli_free_result($result);
}