CODEWITHSUNDEEP
mysqlsqldatabasedebugging
Greg McNulty
Greg McNulty

Reputation: 1466

LAST_INSERT_ID( ) returning multiple rows of 0?

Working in phpMyAdmin for now:

order table strucure:

OrderID     int(11)  auto_increment
CustomerID  varchar(50)
BillAddr    varchar(200)
ShipAddr    varchar(200)
Date            date
Total           double

The table currently has 4 rows of data, with different OrderIDs.

SQL:

SELECT LAST_INSERT_ID() FROM `order`

Result:

LAST_INSERT_ID()
0
0
0
0

I was expecting the fourth rows OrderID - just one number but got a 0 for each row in phpMyAdmin.

Upvotes: 3

Views: 9023

Answers (5)

iblue
iblue

Reputation: 30434

LAST_INSERT_ID() returns the id of the last inserted row and is not bound to any table. So if you create a new row:

INSERT INTO table VALUES('a', 'b', 'c');

It will return the last id (whatever value the new primary key has).

SELECT LAST_INSERT_ID();
=> 123

For details, please take a look at the manual:

LAST_INSERT_ID() (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column. For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this:

If you just want to get last ID in a table, you can do it like this:

SELECT id FROM table ORDER BY id DESC LIMIT 1;

Upvotes: 7

tony gil
tony gil

Reputation: 9574

by indicating a table, you repeat the select statement for EVERY row in said table.

it does not affect the result of your query, other than to repeat the same value.

if running in phpmyadmin, you might want to check that Persistent Connections is set to TRUE, otherwise you will ALWAYS receive 0 as a result of LAST_INSERT_ID().

Upvotes: 0

Ruwantha
Ruwantha

Reputation: 2653

I had the same issue, solution was do the the query at the same time. In other words first query and the second(last_insert_id) query should be executed at the same time. Not as two different execution.

Upvotes: 0

jai
jai

Reputation: 11

Do it like this:

INSERT INTO `one`(`id`, `name`) VALUES (NULL,'shivam');
select last_insert_id();

Perform no other query after insert and they both should execute together

Upvotes: 1

eggyal
eggyal

Reputation: 126015

As stated in the manual:

LAST_INSERT_ID() (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column. For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this:

mysql> SELECT LAST_INSERT_ID();
        -> 195

Upvotes: 3

Related Questions