I have order_detail table with the structure like this:

order_id is a foreign key from order table. On update cascade and on delete cascade. And that's not auto increment. So basically, for one order, it could have more than one product that will insert to order_detail.

For example: order with id = 4 have 3 product in order_detail with product_id = 1, 2, 3. But they have the same order_id (order_id = 4). If i just have one product to insert in order_detail, it's possible to fill th order_id with LAST_INSERT_ID(). But what if i have more than one? I've try with my own but it's still not work. Here's my code

for ($i = 0; $i < count($product_id); $i  ) {
    $productId = $product_id[$i];
    $totalProduct = $total_product[$i];
    $order_detail = mysqli_query($con, "INSERT INTO order_detail (order_id, product_id,
    total_product, total_price) VALUES ('LAST_INSERT_ID([$i])', '$productId', '$totalProduct', 
    null)")or die ("Gagal update".mysqli_error($con));
}

It's returned message error like this:

Cannot add or update a child row: a foreign key constraint fails (`rz_shop`.`order_detail`, CONSTRAINT `order_detail_order_id_foreign_key` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

So how can i insert the same order_id from id's order table, more than one but in once insert? I really hope u would help me for this issue. Thanks in advance.

CodePudding user response：

order table and order_detail table is "one to many" relation using order_id as foreign key, then the process should be :

1. order_detail need auto increment as ID (required)

2. Split the insert process into 2 steps

// Insert `order` table
mysqli_query($connection, "INSERT INTO order VALUES (....................)");

// Get Last Insert ID (order_id)
$insert_id = mysqli_insert_id($connection);

// Count your product list
$total = count($product_list);

// Loop product list
for ( $i = 0; $i < $total ; $i   ) {

   // Insert `order_detail` table
   $sql = "INSERT INTO order (id, order_id, product_id, total_product, total_price)
           VALUES (NULL, $insert_id, $product_id, $total_product, $total_price)";

   mysqli_query($connection, $sql);
}

CodePudding user response：

In above you are looping the $i value so every time the $i value change. you need to store a insert id in separate variable. like this

$InsId=$last_ins_id(); //assign the insert Id in variable

then in loop use $InsId instead of LAST_INSERT_ID([$i])

for ($i = 0; $i < count($product_id); $i  ) {
    $productId = $product_id[$i];
    $totalProduct = $total_product[$i];
    $order_detail = mysqli_query($con, "INSERT INTO order_detail (order_id, product_id,
    total_product, total_price) VALUES ('$InsId', '$productId', '$totalProduct', 
    null)")or die ("Gagal update".mysqli_error($con));
}