CODEWITHSUNDEEP
phpmysqlarraysjsonpdo
YellowMinion
YellowMinion

Reputation: 242

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

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?

Upvotes: 2

Views: 3358

Answers (1)

james
james

Reputation: 26271

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.

Upvotes: 2

Related Questions