PHP Insert Json Array Data as Separate Row in Mysql

Question

I have a json array list as below.I am trying to insert each item as separate row in mysql table

 {"order_array":

  [{
   "qty":"1",
   "menu_name":"PARATHA (KAZI FARMS) 20 PCS",
   "sub_total":"265.0"
   },
 
   {
    "qty":"2",
    "menu_name":"PARATHA (KAZI FARMS) 10 PCS",
    "sub_total":"300.0"
   }],
  
  "amount":
         {
          "sub_total":"565.0",
          "discount":"0",
          "wallet":"0",
          "total_price":595,
          "delivery_charges":30           
         }
 }

I have written below code but it is not inserting any data

$data = json_decode($_POST['json_order_list'], true);

foreach ($data as $row) {
   
   foreach ($array["order_array"] as $row) 
   {
      $item_name = $row['menu_name'];
      $qty = $row["qty"];
      $amount = $row['sub_total'];
      $order_id = '1';
      
      $sql_order = "INSERT INTO tbl_orders (item_name, qty, amount, order_id) 
            VALUES ('$item_name','$qty','$amount','$order_id')";
            
      mysqli_query($link, $sql_order);

    }
 }

what's wrong I am doing

If you look at the data structure, you will probably find you don't need the first `foreach ($data as $row) {`. — Nigel Ren, Sep 13 '20 at 08:39
no error! tried without foreach ($data as $row) { but nothing inserted — Mithu, Sep 13 '20 at 08:42

Slava Rozhnev · Accepted Answer · 2020-09-13T09:50:09.317

3

For your data structure nested loop is redundant. You can use next code as solution:

$stmt = mysqli_prepare(
    $link, 
    "INSERT INTO tbl_orders (item_name, qty, amount, order_id) VALUES (?,?,?,?)"
);
   
foreach ($data["order_array"] as $row) 
{
    $item_name = $row['menu_name'];
    $qty = $row["qty"];
    $amount = $row['sub_total'];
    $order_id = '1';
    
    mysqli_stmt_bind_param($stmt, 'sddd', $item_name, $qty, $amount, $order_id);
    
    mysqli_stmt_execute($stmt);

}

Look working code at PHPize.online

edited Sep 13 '20 at 09:50

answered Sep 13 '20 at 09:09

Slava Rozhnev

9,510
6
23
39

1

Dear @Cid, You absolutely right. I updated my answer. – Slava Rozhnev Sep 13 '20 at 09:51
Since `$order_id` is hardcoded and static, it should not be passed as a parameter, but written directly into the prepared statement. – mickmackusa Sep 13 '20 at 10:54

PHP Insert Json Array Data as Separate Row in Mysql

1 Answers1