SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Apr 2002
    Posts
    395
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inserting an item into 2 tables

    I have a simple database with 2 tables. Table 1 = Product Info, and Table 2 = Product Stats. I could have it all in one table, but I use two tables to keep things organized. I have a unique ID that ties the two tables together.

    Now, I have a little problem adding a new product to my database (with a php script). This is what I have:

    PHP Code:
    mysql_query("INSERT INTO products
    (name,description,price,size,weight) VALUES(''
    $name','$desc','$price','$size','$weight') ")
    or die(
    mysql_error());

    mysql_query("INSERT INTO stats
    (popularity,sales,opinions) VALUES('','','') "
    )
    or die(
    mysql_error()); 
    The problem is that sometimes when I add a new product, the ID will be different in each table. For example, I just added a new product, and the ID in table "products" was 114 while the ID in table "stats" was 115. This of course causes problems, each product has to have the same ID on both tables.

    Is there a better query I could use? The products table always has info inserted, but the stats tables starts-off with all values being empty (they get populated with time, as sales start, etc)

  2. #2
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can get the ID of the last inserted item by using mysql_insert_id(). You can then use this to populate the ID field of the second table (assuming you do not have auto increment set on the table).

    You can then change your second query to something like:

    PHP Code:
    mysql_query("INSERT INTO stats
    (ID,popularity,sales,opinions) VALUES('"
    .mysql_insert_id()."','','','') ")
    or die(
    mysql_error()); 

  3. #3
    SitePoint Addict
    Join Date
    Apr 2002
    Posts
    395
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    Well, I have both tables set for auto_increment. That alone would solve the problem. However, sometimes I delete some fields. So, as a result things become messed up. IE, both tables last ID is 77, but when I run my query, the products table next id is 78, while stats table next id is 81....

    So, is there something that would make sure that the same ID is in both tables?

  4. #4
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You do not want auto_increment set on the second table - you want to specify the ID from the product you have just entered. Relying on auto_increment to tie the tables together will not work (as you've discovered).

  5. #5
    SitePoint Addict
    Join Date
    Apr 2002
    Posts
    395
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahh, I see

    Thank You!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •