Getting the id of the last entered record

[reubensant]

Hello,

I have two tables, a 'users' table and a 'log' table which is used to store all user activity such as login date and times. When the user is registered, I execute an 'INSERT' SQL statement and a user_id is generated automatically for that user, since the field user_id is set to auto_increment.

Now, when the user registers, I also need to add the first log to the 'log' table from the same PHP registration script. The 'log' table has these fields: 'log_id' (auto generated), 'user_id' (a user id) and 'msg' (with the actual log as a string). I need to add the folowing record:

INSERT INTO log (user_id, msg) VALUES ('xx', 'Registered at $datetime');

I need to get the auto generated user_id from the previous INSERT command to replace the 'xx'

I tried mysql_fetch_array after executing the insert command but I was not successful. afaik, ASP lets you to do this. Is there a way to do this with PHP without having to execute another SQL statement to fetch the last entered record?

[Anyango]

"SELECT MAX(user_id) as lastid from users"

[Anyango]

Actually it might be possible to even get it without executing another query but i think getting MAX id just after saving new record will be a good option, i always use that though.

[sned]

Have you looked at mysql_insert_id [us3.php.net]?

-sned

[reubensant]

I have always used Anyango's method, thank you for the suggestion. Also, thank you sned, I was not aware of that. It seems to be more efficient.

[jatar_k]

the main problem with select max is if an insert was done in between the time you inserted and you select max.

mysql_insert_id on the other hand

Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query.

so it knows which query/thread it is working with and will not make mistakes.

[coopster]

I don't use MAX for exactly that reason, jatar_k. Also, you are better off using the internal MySQL SQL function LAST_INSERT_ID() in an SQL query (for the CAUTION reason stated on the PHP mysql_insert_id() page). A quick search over the forum turned up a few good threads with examples from the past ...

[webmasterworld.com...]
[webmasterworld.com...]
[webmasterworld.com...]