You should not SELECT MAX(userID), you should SELECT LAST_INSERT_ID(),
which gives you
tha last id *generated by your connection* regarless of any others,
This is general purpose. If you are willing to be MySQL specific, both C
and Java
(and, I presume, other languages) allow you to retrieve the last insert id
from the connection
without the need to do an explicit SELECT, thus saving the need for a
network operation to
the database server.
See
http://www.mysql.com/doc/G/e/Getting_unique_ID.html
And
http://www.mysql.com/doc/M/i/Miscellaneous_functions.html
--------------------------------------------------------------------------------
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.
Thus, my data will look like this:
UserTable
UserID FirstName LastName....
_________
1...Marc...Smith
2...Steve....Jones and so on
PreferencesTable
PreferenceID PreferenceName
____________________________
1....SomeText
2....SomeOtherText
3....MoreText
UserPreferences Table
UserID PreferenceID (these preference IDs are passed through checkboxes on
the form submission page)
______________________
1 1
1 3
2 2
2 3
So here's my question: I insert the new user, then query for the
max(UserID). What happens if user1 starts an insert; then user2 comes along
at the same millisecond and starts an insert. User2's insert takes far less
time because he only inserts the required fields. So his insert completes;
then the select statement runs to get the maxID. When that select statement
runs, whose ID am I getting? the user whose insert started first? or the
user whose insert completed first?
I'm new to non-transaction tables, but I'm trying them because, well, just
because.
Any answers are appreciated: bottom line, I want to make sure that
everytime
I query for maxID I'm getting the ID of the user that I've just inserted.
Thanks.
Marc
_____________________________________________________________________
This mail was sent by Cablespeed Webmail
---------------------------------------------------------------------
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
