Update mysql from array

[radagast]

Hello

 

I am really having a brain freeze an am hoping that someone can help.

 

I will be receiving the below array from our courier every 3 hours and need to update our mysql database.

 

Array Snippet :

 

{"tracking":[{"waybill":"TCG3267158","booking_no":"94887"}]}{"tracking":[{"waybill":"TCG3240376","booking_no":"94870"}]}{"tracking":[{"waybill":"TCG3267284","booking_no":"94664"}]}{"tracking":[{"waybill":"TCG3343222","booking_no":"94892"}]}{"tracking":[{"waybill":"TCG3235404","booking_no":"93326"}]}{"tracking":[{"waybill":"TCG2976343","booking_no":"93547"}]}{"tracking":[{"waybill":"TCG3246653","booking_no":"93357"}]}{"tracking":[{"waybill":"TCG3199956","booking_no":"93517"}]}{"tracking":[{"waybill":"TCG3216882","booking_no":"93449"}]}{"tracking":[{"waybill":"TCG3170110","booking_no":"93879"}]}{"tracking":[{"waybill":"TCG3247064","booking_no":"93807"}]}{"tracking":[{"waybill":"TCG3252060","booking_no":"93439"}]}

 

 

I am unsure on how to retrieve the data from the array to insert into our database as per the below.

 

 

$sql = "UPDATE waybill SET waybill = '$waybill' WHERE booking_no = '$booking_no' AND waybill IS NULL";

$update=mysql_query($sql) or die(mysql_error());

 

 

All and any help will be greatly appreciated.

 

 

[objnoob]

That's not a PHP array.  That looks like JSON, and it also appears to be more than 1 object which is invalid if evaluated as a single entity.

Ensure that the tracking objects be contained in a single javascript array/object...

 

[ 

       {"tracking":[{"waybill":"TCG3267158","booking_no":"94887"}]}

   ,   {"tracking":[{"waybill":"TCG3240376","booking_no":"94870"}]}

]

 

Use PHP's buildin json_decode() function to convert the JSON string to a PHP array.

Now as a PHP array, you can loop through the tracking elements....

$javascript_array = '[ {"tracking":[{"waybill":"TCG3267158","booking_no":"94887"}]},{"tracking":[{"waybill":"TCG3240376","booking_no":"94870"}]}]';

$tracking_list = json_decode($javascript_array);
foreach($tracking_list as $track) {

    # SANITIZE TRACKING DATA HERE

     $sql = "UPDATE waybill SET waybill = '{$track['tracking']['waybill']}' WHERE booking_no = '{$track['tracking']['booking_no']}' AND waybill IS NULL";
     $update=mysql_query($sql) or die(mysql_error());
}

Be sure to sanitize the values going into the database before using them in your UPDATE statement.

[radagast]

Good Day objnoob

 

Thank you so much for your responds.

 

Yes you are right they have now confirm that it is in fact JSON and I have asked them to make it a single javascript array/object as you suggested and they seem to be more confused than me.

 

They sent me the below code.

 

$arr = array("waybill" => $WAYBILL, "booking_no" => $REFERENCE); 

 

 

 $data = array(

'tracking' => array()

);

$data['tracking'][ ] = $arr; 

 

 

header('Content-Type: application/json; charset=utf8');

echo json_encode($data); 

 

 

Your help once again will be greatly appreciated.

[objnoob]

Yep. All of the $data is encoded as 1 JSON object in that code.  You should be good to go!

[radagast]

Thanks very much for the help objnoob