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?

Answer 1

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()

Answer 2

you should try mysqli example

$sql = "INSERT INTO user (firstname, lastname, email)
VALUES ('test', 'test', '[email protected]')";

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;
}

Answer 3

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!!