Why is mysqli_insert_id() always returning 0?

Question

I have the following code. The mysqli_insert_id() (in this case "$last_row"), which is supposed to return the last row of the table, is always returning 0. Why is it so?

<?php

include 'connect-db.php';
$last_row = mysqli_insert_id($connection);

if ($content != '') {
    $sql = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";

    if (!mysqli_query($connection, $sql)) {
        die('Error: ' . mysqli_error($connection));
    }

    echo $last_row;
    mysqli_close($connection);
}

"*Return values: The value of the AUTO_INCREMENT field that was updated by the previous query. **Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value**.*" — DCoder, Mar 11 '13 at 06:14

score 31 · Accepted Answer · answered Mar 11 '13 at 06:14

mysqli_insert_id does not return the ID of the last row of the table. From the docs, it:

...returns the ID generated by a query on a table with a column having the AUTO_INCREMENT attribute. If the last query wasn't an INSERT or UPDATE statement or if the modified table does not have a column with the AUTO_INCREMENT attribute, this function will return zero.

(My emphasis)

That is, if you were to run it immediately after an insert that auto-generated an ID, on the same connection you did the insert with, it would return the ID generated for that insert.

This is illustrated by the example in the docs linked above:

$query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";
$mysqli->query($query);

printf ("New Record has id %d.\n", $mysqli->insert_id);

score 20 · Answer 2 · answered Mar 11 '13 at 06:17

20

To get the result, you should place the

$last_row = mysqli_insert_id($connection);

after your INSERT query

answered Mar 11 '13 at 06:17

Sudip Pal

2,041
1
13
16

2

Specifying the $connection within the mysqli_insert_id() is important. Or else the ID cannot be retrieved. – Devner Feb 11 '16 at 07:44
For anyone having my same problem, was using $stmt->insert_id and it always return 1. I had to close the statement and then get the id with mysqli_insert_id to get the actual id inserted. – Jh62 Feb 01 '20 at 11:40

score 3 · Answer 3 · edited Dec 23 '21 at 19:50

3

Maybe you tried INSERT IGNORE INTO and you have a UNIQUE value that was already inserted. In that case, this id is zero.

Also, you'll get "zero" if MySQL runs out of connections.

As you probably know PHP “mysql” extension supported persistent connections but they were disabled in new “mysqli” extension --Peter Zaitsev

edited Dec 23 '21 at 19:50

Dharman

30,962
25
85
135

answered Jun 02 '15 at 01:57

PJ Brunet

3,615
40
37

score 1 · Answer 4 · answered Jan 13 '20 at 01:37

Another gotcha with this function -- if you're doing:

INSERT INTO table (col1, col2) VALUES (1, 2) ON DUPLICATE KEY UPDATE col2=3

and insert doesn't happen because of a duplicate key and for the UPDATE, like in my case, if col2 was already set to 3, then mysqli_insert_id will also return 0.

Why is mysqli_insert_id() always returning 0?

4 Answers4

Linked

Related