Insert records into multiple tables – PHP – SitePoint Forums

I was trying to insert records into multiple tables in a single file. The first two queries went great, but the third one didn’t. 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 are 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 "";
        }
    }

    // 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’m going to put where I put the last insert id in the third query because it doesn’t depend on the previous cross-reference table.

Comments are closed.