SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    South Africa
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Insert 2 dependant on insert 1's auto_increment value

    Hi,

    After a form is submitted, I need to insert data into two tables, the second table needs to use the auto_increment value from the first. How do I acheive this?

    I would guess one option would be to use a mySQL function to retrieve a 'last inserted id', but what happens if another record is added to the first table before that id is returned for use with the second?

    Another option might be to use a 'known' value (randomly generated value, for example) as the PK, so that I can use the same value when adding a record to the second table (as a foreign key).

    Is there a better way of doing this?

    TIA.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    use the LAST_INSERT_ID function, and read about it in the mysql docs to assuage any concerns you might have about simultaneous threads
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    South Africa
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess I should have known this, since SQL Server behaves the same way with @@IDENTITY !.

    Thanks again, Rudy.


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
  •