How do I insert multiple rows of JSON object data in mySQL using PHP PDO?

Question

I making PHP API to insert multiple rows of JSON object data. My JSON data format is like this:

(I receive this below format in console log for console.log(this.state.cartItems[]). in REACT)

0: {SparePartID: "34", qty: 1, Price: "500", OrderID: "14"}
1: {SparePartID: "35", qty: 1, Price: "250", OrderID: "14"}
2: {SparePartID: "36", qty: 1, Price: "430", OrderID: "14"}

My PHP Api code is below:

---> part_order_details.php

header("Access-Control-Allow-Origin: http://localhost/Auth/");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");

include_once '../config/database.php';
include_once '../objects/order.php';


$database = new Database();
$db = $database->getConnection();


$order = new Order($db);

$a = file_get_contents("php://input");
$data = json_decode($a, true);

if($order->orderDetails($data)){

    http_response_code(200);
    echo json_encode(array(
        "message" => "All rows of Order Details are inserted.",

    ));
}
else{

    http_response_code(400);
    echo json_encode(array("message" => "Sorry! Error while inserting rows of order details"));
}

and -----> order.php is:

class Order{

    private $conn;

    public $SparePartID;
    public $OrderID;
    public $Price;
    public $Quantity;

    public function __construct($db){
        $this->conn = $db;
    }


    function orderDetails($arr)
    {
       $query=  "INSERT INTO sparepartorderdetails (SparePartID, OrderID, Quantity, Price) VALUES
      (:SparePartID, :OrderID, :qty, :Price) ";
      
        $stmt = $this->conn->prepare($query);

        foreach($arr as $item)
        {
            $stmt->bindValue(':SparePartID', $item[0]);
            $stmt->bindValue(':qty', $item[1]);
            $stmt->bindValue(':Price', $item[2]);
            $stmt->bindValue(':OrderID', $item[3]);

          if($stmt->execute()){
            return true;
        }
        else{
           $arr = $stmt->errorInfo();
            print_r($arr);
        }
        }
    }
}

For now, Im trying to test PHP API with POSTMAN. so Im sending this data in Postman body for POST request:

{
"0":
{"SparePartID": "34",
"qty": "1",
"Price": "500",
"OrderID": "14"},

"1":
{"SparePartID": "35",
"qty": "1",
"Price": "250",
"OrderID": "14"}

}

But the POSTMAN shows ERROR Status: 400 Bad Request with msg: { "message": "Sorry! Error while inserting rows of order details" }

I searched this problem a lot, but no solution. Am I missing something or using wrong way to insert multiple JSON rows?

Can you show the actual raw data that is posted? Because what you're showing up at the top there is not valid JSON. — Patrick Q
– Patrick Q, Commented Apr 17, 2019 at 14:31
@PatrickQ The raw data Im posting in POSTMAN is the one at the very end of my question. { "0": {"SparePartID": "34", "qty": "1", "Price": "500", "OrderID": "14"}, "1": {"SparePartID": "35", "qty": "1", "Price": "250", "OrderID": "14"} } — YellowMinion
– YellowMinion, Commented Apr 17, 2019 at 14:38
Okay, so first thing, if you are wanting to insert multiple records in one request, do not return within your loop. Perhaps keep track of which records inserted successfully and which didn't, and then report the results at the end. In terms of your execute seemingly returning false, look into PDO error handling to help you spot the exact issue. — Patrick Q
– Patrick Q, Commented Apr 17, 2019 at 14:42
You need to get a more useless error from the db class when inserting. — Raymond Nijland
– Raymond Nijland, Commented Apr 17, 2019 at 14:49

james · Accepted Answer · 2019-04-17 18:45:40Z

Your problem mainly lies on this line:

$data = json_decode($a, true);

in connection with the binding of data:

$stmt->bindValue(':SparePartID', $item[0]);
$stmt->bindValue(':qty', $item[1]);
$stmt->bindValue(':Price', $item[2]);
$stmt->bindValue(':OrderID', $item[3]);

When you json_decode with the true argument for associative, your resulting data will be an array of arrays whose keys are strings, not integers. So you would need to change how you access each property from integer indexes to string indexes. You could change the bindValue occurrences to something like this:

$stmt->bindValue(':SparePartID', $item['SparePartID']);
$stmt->bindValue(':qty', $item['qty']);
$stmt->bindValue(':Price', $item['Price']);
$stmt->bindValue(':OrderID', $item['OrderID']);

An alternative to the above solution would be to change how you use json_decode. Remove the true and update your bindValue occurrences to use object property access (since now the data will be decoded into Objects instead of Arrays):

$data = json_decode($a);

$stmt->bindValue(':SparePartID', $item->SparePartID);
$stmt->bindValue(':qty', $item->qty);
$stmt->bindValue(':Price', $item->Price);
$stmt->bindValue(':OrderID', $item->OrderID);

A final note on your $stmt->execute use is that after the first loop iteration your function will return true (if success). It won't ever get to finish the remaining items to insert. You should remove the early return statement and find a better way to return a success/failure indicator.

Collectives™ on Stack Overflow

How do I insert multiple rows of JSON object data in mySQL using PHP PDO?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related