How to get the id inserted by Auto increment

[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]

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]

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:-)