Contridentuallity

Not a fact, a theory!?

MySQL Last Inserted ID

, , , ,

In some cases, you will need to retrieve the last inserted ID which was inserted into your database using PHP specifically when you are using an 'auto_increment' parameter for that field. Obviously there is a common way of doing this, but there is also a built in function which will help you to achieve the same result with less code.

The common way :
$query = "INSERT INTO table (id, value) VALUES ('', 'value')";
mysql_query($query) or die(mysql_error());

$id = mysql_result((mysql_query("SELECT * FROM table WHERE value = 'value'")), 0, 'id');

echo "The last inserted ID was :" . $id;


The easy way :
$query = "INSERT INTO table (id, value) VALUES ('', 'value')";
mysql_query($query) or die(mysql_error());

echo "The last inserted ID was :" . mysql_insert_id();


A useful snippet if you aren't familiar with the 'mysql_insert_id()' function. This will save you some coding and time if you've been using other common, longer ways in the past.

PHP Random stringAMD Athlon 64 x2 - My new processor

Comments

Skulled Monday, September 25, 2006 9:20:43 PM

Sometimes when either of the above fail, you can also fall back to MySQL directly.

Run "SHOW TABLE STATUS [FROM dbname] [LIKE tblname]" query through a mysql_query(), and the resulting result resource has the insert id for the key with auto_increment set in the table under the value "Auto_increment"

It's a more eloborate version than the straightforward mysql_insert_id() function. But it works. smile

Antonie PotgieterContrid Monday, September 25, 2006 9:35:57 PM

Yes, I've read about that method as well. It works just as well.

I think that when you are going to focus on optimization and speed, your method will most likely be a bit faster than having PHP do the work for you.

Thanks for the comment. wink

Brian Armsrongkillermonk Wednesday, June 20, 2007 6:06:16 PM

You can also, if you're set on using MySQL and not php run the following query:

SELECT LAST_INSERT_ID() AS `id`

Write a comment

You must be logged in to write a comment. If you're not a registered member, please sign up.

February 2012
S M T W T F S
January 2012March 2012
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29