SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple inserts and data integrity

    Wondering if anyone has some tips on the best way to handle stuff like this.

    I've frequently been in the situation where first I add a row to a "table a". Then I need to update a many to many table ( table b) with the primary key from table a.

    The solutions I've come up with basically looking something like this;

    "INSERT INTO TABLE A ..."
    "SELECT id FROM TABLE A WHERE ..."
    "INSERT INTO TABLE B aid = '$idfromtablea', ..."

    For the specific cases I've had this has been no problem because I've had something like a UNIQUE column (such as a username) in table a which I can also use to identify with.

    But that's not a generic solution - depends on the data I'm using. I'm wondering if there's something down the road of mysql_affected_rows but can't see it.

    Has anyone got any better ideas for doing this?

  2. #2
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    perhaps all one query:
    PHP Code:
    "INSERT INTO table2
    SELECT table1.aid
    FROM table1
    .
    .
    .
    ;" 
    Sketch
    Aaron Brazell
    Technosailor



  3. #3
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright! Had no idea you could do that. That's a big step forward - reduces the last two queries to one.

    Wonder if there's some trick you can pull for the first query that returns the primary key as an "error".

    Or perhaps there's something that can be done using the MySQL link id? Still pondering...

    Not really a problem you have in other dbs, where you can put foreign keys into another table.

  4. #4
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perhaps I'm hardheaded. What do you mean about returning the primary key as an error?

    Sketch
    Aaron Brazell
    Technosailor



  5. #5
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not too sure right now

    Was trying to think if there's a trick to generate a "non-critical" warning on the primary key column then use mysql_error to tell you what it's value is. Sounding dodgier the more I type. Probably not.

  6. #6
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  7. #7
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by HarryF
    Not too sure right now

    Was trying to think if there's a trick to generate a "non-critical" warning on the primary key column then use mysql_error to tell you what it's value is. Sounding dodgier the more I type. Probably not.
    Intriguing nonetheless. What would you want it to warn you of? What would you accomplish by getting an error message with the value that you couldn't by just reading the value from the db?

    Sketch
    Aaron Brazell
    Technosailor



  8. #8
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it sounds like you need to find out what the AUTO_INCREMENT number is that was just generated, for INSERTing into your other table? then aspen's function, mysql_insert_id(), is what you want:

    PHP Code:
    mysql_query('INSERT INTO table_a ...');
    $id_from_table_a mysql_insert_id();
    mysql_query("INSERT INTO table_b SET aid=$id_from_table_a"); 
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  9. #9
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whoa! That's a prime case for RTFM! mysql_insert_id () is exactly what I'm looking for.

    I've looked and looked for a function like that - honest!


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
  •