what happens if you are using connection pooling though?
-----Original Message----- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 08:09 To: [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct "last insert ID" From: "Jonnycattt" <[EMAIL PROTECTED]> > Hi all, > I know this has been asked a bunch of times, but i need some clarity (new > mySQL user). > I have an app that inserts a new user into one table, then inserts some user > preferences into another table. the procedure is as follows: > 1) insert new user > 2) query for that user's id using select max(userID) as LastUserID from .. > 3) insert into user preferences table using the previous query's LastUserID. > To be clear, this last insert adds mutliple rows to a table, not one row. If I were you, I'd use MySQL's LAST_INSERT_ID() function: http://www.mysql.com/doc/M/i/Miscellaneous_functions.html So long as your ID field is AUTO_INCREMENT, this will return the last auto-generated field in the current handle. Something like this: INSERT INTO UserTable... (your first user insert) SELECT LAST_INSERT_ID() FROM UserTable (this will return the userID of the user you just inserted) INSERT INTO OtherTables (pass the userID you just got to these queries) You'd mentioned worries that a user could be added while another user was still being processed, and the result would be the wrong userID being returned. LAST_INSERT_ID() is handle-based, though, so there should be no worries with that -- the sessions will be kept separate. Hope this helps! -- denonymous . : . : . AIM: denonymous http://www.coldcircuit.net ' : ' : ' http://24.91.199.33 "According to one of our readers, the new MacOS X contains another Satanic holdover from the 'BSD Unix' OS mentioned above; to open up certain locked files one has to run a program much like the DOS prompt in Microsoft Windows and type in a secret code: 'chmod 666'." --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
