Selecting ID when its just been inserted?

[matty005]

Hello,

If i just insert an order into the database, how do i know the ID of that has just been inserted? (I am using auto_increment - meaning it goes up automatically)

The only way i can think of is doing SELECT MAX(ID) FROM orders....

and then you have the ID.

but is this way reliable? What if 2 people order at exactly the same time? Will it select the same id? And is there any other way to do it, or should i do it this way?

Thanks, Matty

[iBrightDev]

you need to select by id, name, and maybe even time (down to at least seconds) too. that way it will be more acturate

[krakjoe]

function get_next_id( $table )
{
if( !($result = mysql_query("SHOW TABLE STATUS LIKE '$table';")) ):
printf( "Cannot query %s", $table );
elseif( !( $array = mysql_fetch_assoc($result) ) ):
print( "Cannot fetch data" );
else:
return $array["Auto_increment"];
endif;
}
echo get_next_id( "categories" );

[Richard]

Or just use:


mysql_insert_id($databaselink);

If will give the ID assigned to the last "INSERT" sql query.

[matty005]

thanks krak_joe, i have used your code. Works perfect

[krakjoe]

always a pleasure ....

[stuffradio]

I believe you could also do...

$query = mysql_query("SELECT id FROM `table_name` LIMIT 0, 1 Order by id desc");
$result = mysql_fetch_array($query);

I think that's it... basically it'll select the last entry, and only that one entry :)

[Richard]

Or as I said, just use:

$the_id = mysql_insert_id($databaselink);

$the_id would store the ID assigned to the last "INSERT" query sent to mySQL.

[stuffradio]

weird I've never heard of mysql_insert_id before :S

http://ca3.php.net/mysql_insert_id

[Richard]

weird I've never heard of mysql_insert_id before :S

http://ca3.php.net/mysql_insert_id

I use it all the time :P It's a life saver, and it saves on mySQL querys and code size, increasing the speed of your script.

[themoose]

It seems that all you have to do is this:


$query = mysql_query("INSERT INTO blah <..etc..>");
$last_id = mysql_insert_id();

Actually easier than joe's code, although joe's code could be more useful in different situations.

[matty005]

I just tested what colin wrote and it works like joe's. Do both codes that you wrote work with all versions of mysql? Because if (for example) joe's doesnt work with mysql version x then i'd have to use the other one.

[Richard]

I just tested what colin wrote and it works like joe's. Do both codes that you wrote work with all versions of mysql? Because if (for example) joe's doesnt work with mysql version x then i'd have to use the other one.

Joes code would put extra pressure on the mySQL server, when PHP already stores a local copy of the ID internaly.

Why go to the shop to by a news paper, when you already have one sitting in front of you?

Plus - mysql_insert_id will work with all versions of mySQL.

[krakjoe]

Didn't know about mysql_insert_id, I don't know everything, was just giving you a way to do it, if theres a function to do as you ask, generally you should use it.

Although, the extra pressure argument is pretty much void, mysql_insert_id is just the same code as I have written there, only in c instead of interpreted php......