How to get the id inserted by Auto increment

Post Reply
mister_v
Posts: 188
Joined: Thu Mar 04, 2010 9:19 pm

How to get the id inserted by Auto increment

Post by mister_v »

Hello,

I have a MySQL database with a product table that auto increments the id when a new item gets inserted.
The insert query is something like this

Code: Select all

INSERT INTO table (product,date_1) values ('item','$today');
I want to know what the id is of the latest insert I did.
I'm programming in php.
I can write a search query that searches on product and date,
but there must be a faster and easier way.
chris
Site Admin
Posts: 194
Joined: Mon Jul 21, 2008 9:52 am

Re: How to get the id inserted by Auto increment

Post by chris »

I have good results with

Code: Select all

mysql_query("INSERT INTO table (product,date_1) values ('item','$today')");
$lastid=mysql_insert_id();
Basically it gets the id generated from the previous insert.

If it doesn't work,
You can try to get it from table status:

Code: Select all

$result=mysql_query("SHOW TABLE STATUS LIKE 'table'");
$row=mysql_fetch_array($result);
$nextid=$row['Auto_increment'];
$lastid=$nextid-1;
Not sure if it is the best way,
If you have many updates, it might already be a new entry...

I have also see scripts that check on max(id)

Code: Select all

SELECT MAX(id) FROM table;
again, this no guaranty it is the correct entry;
But faster the a search on product and date.
chris
Site Admin
Posts: 194
Joined: Mon Jul 21, 2008 9:52 am

Re: How to get the id inserted by Auto increment

Post by chris »

With php and mysqli there is now a better way:

Code: Select all

$a_qry="INSERT INTO table (name) VALUES('$value')";
if(!$result=$db->query($a_qry)) $error.=$db->error;
$id=mysqli_insert_id($db); //last id:-)
Post Reply