I was trying to insert records into multiple tables in one file. The first two queries ran very well but the third one did not run. The first table is a normal table while the last 2 tables are cross-reference tables, which store the primary keys of two tables each. Here is the code snippets for your help:
// initialize prepared statement
$stmt = $con->stmt_init();
$insert_product = "INSERT INTO products (cat_id, manufacturer_id,date,product_title,product_url,product_img1,product_img2,product_img3,product_price,product_keywords,product_desc,product_features,product_video,seo_keywords,product_label,product_sale ) VALUES(?, ?, NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
// bind parameters and execute statement
if ($stmt->prepare($insert_product)) {
// bind parameters and execute statement
$stmt->bind_param('iisssssisssssss', $cat, $manufacturer_id, $product_title, $product_url,$product_img1,$product_img2,$product_img3,$product_price,$product_keywords,$product_desc,$product_features,$product_video, $product_seo,$product_label,$product_sale );
$stmt->execute();
if ($stmt->affected_rows > 0) {
echo "<script>alert('Product has been inserted sucessfully')</script>";
}
}
// if the product entry was inserted successfully, check for sizes
if (($stmt->affected_rows > 0) && isset($_POST['size'])) {
// get the product's primary key
$product_id = $stmt->insert_id;
foreach ($_POST['size'] as $size_id) {
if (is_numeric($size_id)) {
$values[] = "($product_id, " . (int) $size_id . ')';
}
}
if ($values) {
$sql = 'INSERT INTO product2size (product_id, size_id) VALUES ' . implode(',', $values);
// execute the query and get error message if it fails
if (!$con->query($sql)) {
$sizeError = $con->error;
}
}
}
// third query
// if the product entry was inserted successfully, check for product categories
if (!isset($sizeError) && isset($_POST['product_cat'])) {
// get the product's primary key, my issue is this , the last query is parallel to this one, that is
// why I am still putting last insert id of product table and is not working
$product_id = $stmt->insert_id;
foreach ($_POST['product_cat'] as $p_cat_id) {
if (is_numeric($p_cat_id)) {
$values[] = "($product_id, " . (int) $p_cat_id . ')';
}
}
if ($values) {
$sql = 'INSERT INTO product2pcat (product_id, p_cat_id) VALUES ' . implode(',', $values);
// execute the query and get error message if it fails
if (!$con->query($sql)) {
$pcatError = $con->error;
}
}
}
My problem is that I wouldn’t know what I will put where I put last insert Id in the third query since, it does not depend on the previous cross reference table.