retrieving auto incremented value

[nnichols]

Please can you tell me whether this is the best way of doing this -

Currently I have -

// insert data into user table
$db = connect_db();
$query = "insert load of user data in user table";
$result = mysql_query($query,$db);

// retrieve auto incremented USER_ID
$query = "SELECT USER_ID from user WHERE USERNAME='$username'";
$result = mysql_query($query,$db);
$row = mysql_fetch_array($result);
$user_id = $row["USER_ID"];

This works ok, I think. I have been using it for a few weeks. I am thinking though, that I should be using mysql_insert_id() instead, or possibly the MYSQL function LAST_INSERT_ID().

Please would you suggest the most reliable way of doing this.

Thanks in advance
Nick

[nikholmes]

If your usernames are unique (and validated as such!), your way will be reliable, but does two queries where you need only one.

Me no expert, but this seems to work well for me:

$result=mysql_query($insertquery);
if ($result) $insertID=mysql_insert_id();

My understanding is that if you don't supply a link identifier as the mysql_insert_id() parameter, the correct identifier for the last query run by the script is used automatically.

Which is lucky. :-)

[Anon]

Yes mysql_insert_id is the best way! try this...
(it is actaully much more reliable than the first method because of multi tasking)
// insert data into user table
$db = connect_db();
$query = "insert load of user data in user table";
$result = mysql_query($query,$db);
// retrieve auto incremented USER_ID
$user_id = mysql_insert_id();
Travis Harris