MySQL: Get AUTO_INCREAMENTED sequence number after Insert?

Question

I've MySQL Table with Primary key as AUTO_INCREAMENTED (UNSIGNED INT) as sequence number. Every Time I insert new row in this table, I want to give acknowledgement to the user showing inserted AUTO_INCREAMENTED UNSIGNED INT.

Currently after inserting row, I’m executing SELECT Query to get last inserted row. Is there any smarter way to get inserted value, immediately after INSERT (without SELECT) ?

Answer 1

Try with mysql_insert_id()

echo mysql_insert_id();

Refer THIS for more about mysql_insert_id()

And Try to avoid mysql_* statements due to the entire ext/mysql PHP extension, which provides all functions named with the prefix mysql_*, is officially deprecated as of PHP v5.5.0 and will be removed in the future.

There are two other MySQL extensions that you can better Use: MySQLi and PDO_MySQL, either of which can be used instead of ext/mysql.

In mysqli_* it will be like

echo mysqli_insert_id();

Answer 2

Use this

SELECT LAST_INSERT_ID()

Or mysqli_insert_id() in PHP scripts. You can also use PDO instead of mysqli

Answer 3

There is a function LAST_INSERT_ID() in the MySQL Reference Manual that can be used for that.

mysql> CREATE TABLE t1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(32));
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1(name) VALUES ("John Doe");
Query OK, 1 row affected (0.24 sec)

mysql> SELECT * FROM t1;
+----+----------+
| id | name     |
+----+----------+
|  1 | John Doe |
+----+----------+
1 row in set (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)