Inserting records into multiple tables

PHP
#1

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.

#2 
// 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>";
        }
    }

    // get the product's primary key
    $product_id = $stmt->insert_id;

    // if the product entry was inserted successfully, check for sizes
    if (($stmt->affected_rows > 0) && isset($_POST['size'])) 
    {
        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'])) 
    {
            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;
                }
            }
        }

Just declare the product id before the if block and use it in both queries

#3

This set of three queries must be part of a transaction, so that an error at any point will cause them to be rolled back/not committed.

Also, as your comment for the 3rd query states - if the product entry was inserted successfully, check for product categories, the logic must actually do that. Testing if the $sizeError is not set doesn’t have anything to do with the first query executing without error.

You should store errors in an array, using a descriptive name as the array index. You can then test at any point if there are/are-not any errors or if there is/is-not a specific error.

You should validate all the input data before using any of it. Testing if the size or product_cat values are integers at the point of using them is too late to recover from an incorrect value. Also, is_numeric() allows values other than just integers, but applying (int) to the values will produce an integer from the leading part of the value or a php error, depending on what the value actually is. I recommend that you use fiter_var() with the FILTER_VALIDATE_INT filter, when you validate these inputs prior to using them.

You must also address duplicate data. What happens if the product already exists or there’s a duplicate size or product_cat value, either because of existing data or a duplicate among the submitted data? The simplest way of doing this is to setup unique (composite) indexes in the tables, then test for duplicate index errors in the error handling for each query.

Speaking of error handling for the database statements that can fail - connection, query, prepare, and execute. You need specific error handling for each query. I recommend that you use exceptions for database statement errors and only catch and handle the exception for recoverable errors, such as when inserting/updating duplicate or out of range values. For all other cases, simply let php catch and handle the database statement exceptions. For the first query, if the execute fails, you would catch the exception and test if the error number is for a duplicate index error. If it is, you would setup a message for the user telling them what was wrong with the data that they submitted. For all other error numbers, just re-throw the exception and let php handle it. You would do the same for the error handling for the other two queries.

#4

Thanks, I will try and work on the suggestions and then revert!