Best Way To retrieve the True last inserted ID and Insert it elsewhere

[jimmyoneshot]

I've seen many topics on this but I'm wondering what is the best, truest and most reliable way to retrieve the true last inserted record id value from an autoincrementing table?

 

For example in my site a logged in user user posts an update into the updates table inserting a new record and incrementing the auto incrementing update_id value of that table.

 

However I want to be able to get the update_id of that last inserted update record and innsert it into a table called updates_users along with the logged in users user_id which I have already retrieved. How can I adapt the following code to do all this, in one query if possible or in 2 or more if not?

 

mysql_query("INSERT INTO updates (update_text) VALUES ($updatetext)");	

$latestupdateid = ???? NEED TO GET LATEST UPDATE_ID HERE ????

mysql_query("INSERT INTO updates_users (update_id, user_id) VALUES ($latestupdateid, $loggedinuserid)");

[kickstart]

Hi

 

You just use mysql_insert_id()

 

All the best

 

Keith

[jimmyoneshot]

Thanks kickstart. Is that reliable though?

 

I mean if I do this:-

 

mysql_query("INSERT INTO updates (update_text) VALUES ($updatetext)");

 

mysql_insert_id()

 

Then isn't there a split second for another user to possibly post another update after that first insert statement and then the id retrieved via the mysql_insert_id() won't be the one that this users just inserted or am I getting mixed up?

[kickstart]

Hi

 

It is reliable as it isn't relying on there not being a record inserted between the insert statement and the mysql_insert_id() call.

 

All the best

 

Keith

[floridaflatlander]

I was looking at this and will be looking at it again in a month or two.

 

For some reason when I was playing with it I couldn't get mysql_insert_id() to work and yes it was after the insert query. But I am going to try again later.

 

I was also reading the info on php.net and someone used a select statement right after the insert that had where name = 'xxx' and this = 'tttt' and time = '$time' with time, name & this being  the same as used in the insert. That looks promising.

 

Anyway, but what are the odds that someone would place a record at the fraction of a second that you do when using mysql_insert_id(), especially if you use it right after the insert.

[PFMaBiSmAd]

mysql_insert_id() is specific to the current database connection/session and will return the auto-increment from a INSERT query that was executed by the current invocation of a script.

 

If you inserted specific and unique data, yes, you can query for that data and get the id that was automatically generated. That however requires executing an additional query.

[kickstart]

Hi

 

If you have multiple database connections in use then you might need to specify which one with mysql_insert_id().

 

Using a SELECT is risk and does open up the problem you mentioned earlier.

 

All the best

 

Keith

[floridaflatlander]

If you have multiple database connections in use then you might need to specify which one with mysql_insert_id().

 

Multiple like $dbc1, $dbc2 or Multiple as in 2 or 3 people using $dbc at a time ?

 

Use mysql_insert_id($dbc); ?

 

[kickstart]

Hi

 

Multiple as in your script is connecting to more than 1 database. With the mysql I do that isn't that common, but it is entirely feasable to have a connection to a common security database (shared with many systems) as well as the database specific to your system.

 

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');

mysql_select_db('mydb');

mysql_query("INSERT INTO fredstable (somecolumn) values ('somevalue')");

echo mysql_insert_id($link);

 

All the best

 

Keith

[jimmyoneshot]

What does mysql_indert_id return though exactly?

 

I mean in my case I need it to return the value of the latest update_id so how can I get it to do that directly or if not how do I process the result it creates to retrieve that?

[kickstart]

Hi

 

An auto increment field is an integer (maybe a big int) and mysql_insert_id() will return an integer which is the auto increment id it has just inserted.

 

For example:-

 

mysql_query("INSERT INTO fredstable (id, somecolumn) values (NULL, 'somevalue')");

$inserted_id = mysql_insert_id();

$result = mysql_query("SELECT somecolumn FROM fredstable WHERE id = $inserted_id");

if ($row = mysql_fetch_array($result)) 
{
    echo $row['somecolumn'];
}

 

That is doing an insert and then getting the row back with the id just inserted and echoing out one of the columns.

 

All the best

 

Keith