PHPMYADMIN LAST_INSERT_ID returns 0

Question

I have problem with LAST_INSERT_ID.

CREATE TABLE for_test(
id_test INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
test_name VARCHAR(30)
);

INSERT INTO for_test (test_name) VALUES ('test1');
INSERT INTO for_test (test_name) VALUES ('test2');

I added successfully two records - yet SELECT LAST_INSERT_ID() still returns 0. (I use InnoDB if that even matters)

Could anyone tell me what I'm doing wrong?

@atoms: mysql_insert_id is PHP function. on phpmyadmin it will return 0 always. Use it on procedure/function and you will get the result. — Riad, Oct 07 '16 at 11:10
just a side note: the last_insert_id could also be the last inserted id from someone else from some other table. **unless* you use transactions!! — Krish, Oct 07 '16 at 12:42

score 2 · Accepted Answer · answered Oct 07 '16 at 11:28

2

No other query after insert and they both should execute together. Be careful that. Also if you can not handle it you can use

SELECT id FROM for_test ORDER BY id DESC LIMIT 1;

and you can read this manual

How to use LAST_INSERT_ID()

answered Oct 07 '16 at 11:28

Onur Cete

253
1
2
9

1

Thanks for your answer, it helped a lot :) – Petrus Oct 07 '16 at 17:21
I am glad that it. So I hope you will click "This answer is useful button". Have a nice developments :) – Onur Cete Oct 10 '16 at 07:48

score 0 · Answer 2 · answered Oct 07 '16 at 11:10

0

you should try mysqli example

$sql = "INSERT INTO user (firstname, lastname, email)
VALUES ('test', 'test', 'test@example.com')";

if ($conn->query($sql) === TRUE) {
    $last_id = $conn->insert_id;
    echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

answered Oct 07 '16 at 11:10

pawan sen

708
5
14

As Riad said - mysqli it's PHP function and I'm using PHPMyAdmin console – Petrus Oct 07 '16 at 11:15

score 0 · Answer 3 · answered Oct 07 '16 at 12:00

CREATE TABLE for_test(
id_test INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
test_name VARCHAR(30)
);

INSERT INTO for_test (test_name) VALUES ('test1');
INSERT INTO for_test (test_name) VALUES ('test2');
SELECT LAST_INSERT_ID();

Note: it's working with Engine - InnoDB in phpMyAdmin. if you still have same problem, will you please send error snapshot, so that we can track exact issue.

Hope this will help you!!

PHPMYADMIN LAST_INSERT_ID returns 0

3 Answers3