Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33

    Question Unanswered: Getting ID of record just inserted

    Hi,
    I searched this forum for this topic and could nt find it. Found something under MS SQL but it was MS-SQL specific solution.

    SO,
    After I insert a new record into a table, how do I get its record ID, making sure that it is the record in question? In theory for a very busy site two users can submit at the same time, and one of them get the other persons record ID.

    Option 1:
    Code:
      ...insert...
      sqlString="SELECT MAX(recordID) AS maxID FROM tbl_records"
      set maxRS = con.Execute(sqlString) 
      recordID = maxRS("maxID")
    CON: possibility that two users submit at the same time, and returns wrong ID. (this is what I use now, and I take a risk)

    Option 2:
    Code:
      ...insert...
      sqlString="SELECT recordID FROM tbl_records WHERE userID=" & userID & " ORDER BY recordID DESC LIMIT 0,1"
      set maxRS = con.Execute(sqlString) 
      recordID = maxRS("recordID ")
    CON: Is this efficient? Also will not work for guests/non-registered users, unless I store session ID and look that up... (yuck)

    Is there an option 3?

    Thx,
    Ziv.
    Last edited by zkenet; 01-15-04 at 13:31.

  2. #2
    Join Date
    Nov 2003
    Posts
    91
    OPTION 3

    SELECT last_insert_id();

    This only works if you execute this in the same session
    that you did your INSERT in.

  3. #3
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33
    Thx! Thats exactly what I was looking for!

    For anyone who wants to read more: http://www.mysql.com/doc/en/Informat...s.html#IDX1382

Posting Permissions

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