How to get last inserted id from table MySQL

Question

I am running 1 script in php for that I need last inserted id in subscription table. By using that id I want to make notification note for that subscription.

I used:

SELECT LAST_INSERT_ID() FROM subscription

I am getting 0 instead of real last inserted value.

Answer 1

If you use php to connect to mysql you can use mysql_insert_id() to point to last inserted id.

Like this :

mysql_query("INSERT INTO mytable (1, 2, 3, 'blah')");
$last_id = mysql_insert_id();

See this : mysql_insert_id()

Answer 2

LAST_INSERT_ID() returns the last id from a previous insert statement. If you want the most recently inserted record and are using Auto Increment Prime keys, you can use the code below:

SELECT MAX( id ) FROM subscription;

If you need to know what the NEXT id will be, you can get this from INFORMATION_SCHEMA

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'test'

mysql_insert_id

Answer 3

This question has already been answered many times: MySQL: LAST_INSERT_ID() returns 0

You are using that function out of context. It will only work if you inserted a row immediately prior thusly:

INSERT INTO 'subscription' (name) VALUES ('John Smith');
SELECT LAST_INSERT_ID() FROM subscription

You can however select the row with the highest id, which logically would be the most recently added...

SELECT MAX( id ) FROM subscription;

The standard approach however is to simply call mysqli_insert_id or mysql_insert_id (depending on whether you are using the mysqli or mysql PHP library. I should add that the mysql library is very inadvisable to use since it is almost completely deprecated). Here's what the whole thing would ideally look like:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$mysqli->query("INSERT INTO 'subscription' (name) VALUES ('John Smith');");
printf ("New Record has id %d.\n", $mysqli->insert_id);
//Output is something like: New Record has id 999

If however you didn't insert a subscription in the same script as collecting the most recent row ID, use the 'select max' approach. This seems unlikely given that you mentioned '1 script'

Also, if your ID's are non-consecutive, or you do not have an ID field, or you have row ID's higher than the one you just added you should probably consider a 'date_added' column to determine which one was really the latest. These scenarios are rather unlikely however.

Answer 4

this is the better approach 2 and 3 works but MAX(id) take more time to execute.

1. SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'dbName' AND TABLE_NAME = 'tableName';

2. SELECT tableName.id FROM tableName ORDER BY tableName.id DESC LIMIT 0,1;

3. SELECT MAX( id ) FROM tableName;

Answer 5

This will always give you the maximum id, which says the biggest number is the last inserted one

 SELECT MAX(id) as MaximumID FROM subscription;

Answer 6

$last_id=mysql_query("SELECT id FROM `table_name` ORDER BY id DESC LIMIT 0 , 1" );
$row=mysql_fetch_assoc($last_id);
echo $row['id'];

Replace id by your id field name in database and table_name by your table name.