INSERTING RECORDS INTO MULTIPLE TABLES
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 normal table while the last 2 tables are cross-reference tables, which store 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.