如何将curl响应值插入数据库

Question

I generated AWBNoSeries with help of below code in link :

ReturnMessage":"Successful","ReturnCode":100,"AWBNoGenRequestedDateTime":"20-11-2018 11:46:35","BatchID":"UQpyj61049","AWBNoSeries":["14104918100000","14104918100001",

<?php

$data = 
array (
  'BusinessUnit' => 'ECOM',
  'ServiceType' => 'FORWARD',
  'BatchID'   =>   'UQpyj61049',
);


$url = "http://114.143.206.69:803/StandardForwardStagingService.svc/GetAWBNumberGeneratedSeries";
$data = json_encode($data);

$headers = array(
    "Content-Type: application/json", 
    "XBKey: QGfMthH1",
);

$curl = curl_init($url);

curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
curl_setopt($curl, CURLOPT_HTTPAUTH, CURLAUTH_BASIC);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
curl_setopt($curl, CURLOPT_POST, true);
curl_setopt($curl, CURLOPT_POSTFIELDS, $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;

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

$result = mysqli_query($con,"SELECT * FROM ecomexpress_awb"); 

//$id = $_POST['id']; 

$sql = $con->query('INSERT INTO ecomexpress_awb(awb) values ()'); 

mysqli_close($con); 

?>

Now i need to save these below AWBNoSeries in mysql table ecomexpress_awb & in column awb with each AWBNo in different row....

14104918100000","14104918100001","14104918100002","14104918100003"

I am not getting what i need to pass in values () in above query....

Answer 1

You will need to loop through all of the values found and add each one to the database. For this, I have had good experience with using Prepared Statements. Using this method, you can set up the statement and send it to the SQL server one time, then just change the parameter(s) you provide to the query (in this case, AWBNo and AWBNoGenRequestedDateTime), and execute for each value. The full code would look something like this:

<?php
ini_set('display_errors', 'On');
ini_set('html_errors', 0);
error_reporting(-1);

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$data =
array (
  'BusinessUnit' => 'ECOM',
  'ServiceType' => 'FORWARD',
  'BatchID'   =>   'UQpyj61049',
);


$url = "http://114.143.206.69:803/StandardForwardStagingService.svc/GetAWBNumberGeneratedSeries";
$data = json_encode($data);


$headers = array(
    "Content-Type: application/json",
    "XBKey: QGfMthH1",
);

$curl = curl_init($url);

curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
curl_setopt($curl, CURLOPT_HTTPAUTH, CURLAUTH_BASIC);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
curl_setopt($curl, CURLOPT_POST, true);
curl_setopt($curl, CURLOPT_POSTFIELDS, $data);
curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($curl, CURLOPT_VERBOSE, true);

$curl_response = curl_exec($curl);
curl_close($curl);
//we know this is working, no need to echo data
//echo $curl_response;

// justin code start

$mysqli = new mysqli("localhost", "root", "your_password", "do_management4");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$parsedData = json_decode($curl_response, true); //true: preserve associative arrays

if (!($stmt = $mysqli->prepare("INSERT INTO ecomexpress_awb(awb, created_at) VALUES (?,?)"))) {
     echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

//bind parameters: $awb will be the AWBNoSeries values, $genRequestDT will be the AWBNoGenRequestedDateTime (same for all AWBNoSeries values)
if (!$stmt->bind_param("is", $awb, $genRequestDT)) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}

//set $genRequestDT equal to AWBNoGenRequestedDateTime value to be inserted with each record (this only needs to be done once)
$genRequestDT = $parsedData['AWBNoGenRequestedDateTime'];

//loop through AWBNoSeries values and insert each one into the db
foreach ($parsedData['AWBNoSeries'] as $awb){
    if (!$stmt->execute()) {
        echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
    }
}

$stmt->close(); //close the statement

// justin code end

mysqli_close($mysqli);

?>