Jump to content
Larry Ullman's Book Forums

Inserting Records into multiple tables


Recommended Posts

Dear All,

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.

Link to comment
Share on other sites

Could you be more specific than "the query did not run"? Did PHP attempt to execute the query but it didn't succeed? And, if so, what was the MySQL error? Or is it not getting to that point in your code where PHP is even trying to execute the query? You may want to litter your code with print statements to see what is or isn't happening, and what values exist where. 

Link to comment
Share on other sites

Dear larry, 

 thanks very much for your reply. 

It did not give me any error on the screen it just said it was inserted successfully but when I check the PHPmyadmin, the first two queries inserted values to the product table and the product2size table which is the first cross reference table for a particular product which has many sizes in the sizes table, but the second cross-reference table which is product2pcat table which also supposed to get the insert product_id of the same product and many catecories_id which a product belong to. 

i know I should use the primary key of the  last->insertid but since I also want it to insert the primary key of the product table the product2pcat table. My question is  this what should I do for the third table that is product2pcat cross reference table to have  values when I click the submit button on the page.

Edited by olaoyesunday
Link to comment
Share on other sites

One issue I can see is that you call `$product_id = $stmt->insert_id;` a second time, but you already have the $product_id value, so that shouldn't be necessary. And there's a good chance `$stmt->insert_id` won't return anything a second time it's called, but I could be wrong about htat. 

Link to comment
Share on other sites

Sir, 

You are right. what I don't know is how to insert values into the third table that is why I repeated what I did in the second table. I also want to make use of proudict_id in the third table. 

If you can assist with this explanation I will be glad.

Link to comment
Share on other sites

It might be best if you start coding by documenting what you want to do and then convert those comments into code. For example:

  1. Add a record to the products table.
  2. Get the product ID for the record just added.
  3. If there are sizes, add one record to product2size for each size. This uses the product ID already fetched and the size ID, which comes from the HTML.
  4. If there are product categories, add one record to product2pcat for each category. This uses the product ID already fetched and the category ID, which comes from the HTML.

So with that written out like so you can see for starters that you don't need to fetch the product ID multiple times, just once will do. You'll also see that steps 3 & 4 are parallel, not dependent upon each other (from what I can tell). But your code for step 4 includes "isset($sizeError)". I don't see why that's there. That may be why the third query isn't running? From the code and your comments it looks like the second and third queries could be executed in either order as neither depends upon the other. 

There's nothing particularly special about running these three queries. I think you have some logic issues. As I recommended before, if you use a slew of print statements you could see what is or is not true or happening. It's a blunt debugging tool, but it works.

Another likely cause of confusion is you're using both prepared statements and non-prepared statements. That's not really a good idea, from the point of comprehension. Moreover, you're not using prepared statements for the two queries that would benefit the most from the prepared statements. By using prepared statements the second and third queries can be executed within the foreach loops, nicely. 

 

 

 

Link to comment
Share on other sites

 Share

×
×
  • Create New...