Inserting records into multiple tables

[olaoyesunday]

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
            $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 did wrong that is preventing the third query to run.

[phdr]

Firstly -

1. Do you have php’s error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your system, so that php will help you by reporting and displaying all the errors it detects?
2. The error handling you have for database statements isn’t helping you very much. The Prepare(), execute(), and query() calls can all fail with errors, but you don’t have consistent error handling. At this point, any errors from these statements are due to programming mistakes and are fatal problems. Later, when you get the code to work the way you want, you need to detect and handle duplicate errors. Instead of having conditional logic for all these, simply use exceptions for database statement errors and in most cases let php catch and handle the exception where php will use its error related settings to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed or logged the same as php errors.) You would then remove all the existing database error handling logic since it will no longer get executed upon an error (execution transfers to the nearest correct type of exception handler, which will be php in this case.)

Next, you should be executing these queries as part of a transaction so that they will all get rolled back should any error occur.

Lastly, there are some issues with the code -

1. In the logic for the 3rd query, you are calling $stmt->insert_id a 2nd time. Since you have executed a different insert query (the 2nd one) on the connection, it should be getting the last insert id from the 2nd query, not the first. Because you already have the last insert id from the 1st query, in $product_id, simply remove this additional call to $stmt->insert_id and use what is already in $product_id.
2. You are not initializing the $values array between the 2nd and 3rd queries. At a minimum, this will result in a bunch of size data being inserted into the product2pcat table.
3. is_number() allows decimal and scientific-notation numbers. If you only want integer numbers, use ctype_digit().

A secondary point of using a prepared query is they allow you to more efficiently re-execute a query you have already prepared. For the 2nd and 3rd queries, you would have less code, and the $values problem would go away, if you used prepared queries here. It would also simplify all the prepared query code if you switched to the much simpler PDO extension.