MySQL Forums
Forum List  »  PHP

INSERTING RECORDS INTO MULTIPLE TABLES
Posted by: Sunday Olaoye
Date: September 06, 2022 07:54AM

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.

Options: ReplyQuote


Subject
Written By
Posted
INSERTING RECORDS INTO MULTIPLE TABLES
September 06, 2022 07:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.