PHP Insert JSON to MySql Multitable

I’ve this multidimensional array to insert into mysql database :

{
  customer_id: "25",
  total: 238000,
  firstname: "oci",
  product: [
             {
              product_id: "6",
              product_name: "Orange"
             },
             {
              product_id: "5",
              product_name: "Melon"
             }
         ]
}

This my code

$_POST = json_decode(file_get_contents('php://input'), true);
$customer_id= $_POST['customer_id'];
$total= $_POST['total'];
$firstname= $_POST['firstname'];

foreach ($_POST['product'] as $q_product => $v) {
    $product_id = $v['product_id'];
    $product_name= $v['product_name'];

$query = "INSERT INTO tbl_order
(order_id,customer_id,total,firstname) VALUES (NULL,'$customer_id','$total','$firstname')";
    
$queryy = "INSERT INTO tbl_order_product
    (order_id,product_id,product_name) VALUES (LAST_INSERT_ID(),$product_id,'$product_name')";
}
$result = mysqli_query($link, $query);
$resultt = mysqli_query($link, $queryy);

I can’t insert “queryy” to tbl_order_product, only tbl_order table Please help me to insert ‘product’ array into tbl_order_product table too..

Answer

1. Your query execution should be inside loop otherwise only last product details will be populated in both tables.
2. Don’t use LAST_INSERT_ID() in php, instead use php function for the same.
3. Don’t pass order_id as null and make sure it is auto incremental.
4. Use prepared statement to prevent sql injection.

Simple update to your question to get it working(doesn’t include sql injection prevention)

foreach ($_POST['product'] as $q_product => $v) {
    $product_id    = $v['product_id'];
    $product_name  = $v['product_name'];

    $query   =  "INSERT INTO tbl_order (customer_id,total,firstname) 
                VALUES ('$customer_id','$total','$firstname')";
    
    $result  =   mysqli_query($link, $query);
    $last_id =   mysqli_insert_id($link);

    $queryy  =  "INSERT INTO tbl_order_product (order_id,product_id,product_name) 
                 VALUES ($last_id ,$product_id,'$product_name')";

    $resultt =   mysqli_query($link, $queryy);   
}

To use prepared statement to prevent sql injection, please have a look at these.

1. PHP MySQL Prepared Statement
2. INSERT query using PDO