How to get last inserted id from MySQL table

Question

I am running 1 script in php because I need the last inserted id in the subscription table. Using this id, I want to make a notification for this subscription.

I used:

SELECT LAST_INSERT_ID() FROM subscription 

I get 0 instead of the real last inserted value.

Answer 1

If you use php to connect to mysql , you can use mysql_insert_id() to indicate the last id inserted.

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 the previous insert statement. If you want the latest inserted record and use the auto-increase keys, you can use the following code:

 SELECT MAX( id ) FROM subscription; 

If you need to know what will happen with the NEXT ID, 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 use this function out of context. It will only work if you inserted a line immediately before this:

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

However, you can select the row with the highest identifier, which logically will be the most recently added ...

 SELECT MAX( id ) FROM subscription; 

However, the standard approach is to simply call mysqli_insert_id or mysql_insert_id (depending on whether you are using the mysqli or mysql mysql library.) I must add that the mysql library is very discouraged because it is almost completely deprecated). Here everything will look like this:

 $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 you did not enter the subscription in the same script as collecting the most recent row identifier, use the "select max" approach. This seems unlikely if you mentioned '1 script'

Also, if your identifier is not consistent or you do not have an identifier field, or you have a row identifier other than the one you just added, you should probably look at the "date_added" column to determine which one really was the last one. However, these scenarios are unlikely.

Answer 4

this is the best approach 2 and 3, but MAX (id) takes longer to execute.

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

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

• SELECT MAX( id ) FROM tableName;

Answer 5

This will always give you the maximum identifier, which says the largest number is the last inserted

  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 with your id field name in the database and table_name by the name of your table.