MySQLi Insert ID

Retrieve the last ID generated by an INSERT query on a table with an AUTO_INCREMENT column.

Object-oriented Style

$id = $conn->insert_id;

Procedural Style

$id = mysqli_insert_id($conn);

Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value.

Insert id when updating rows

Normally an UPDATE statement does not return an insert id, since an AUTO_INCREMENT id is only returned when a new row has been saved (or inserted). One way of making updates to the new id is to use INSERT ... ON DUPLICATE KEY UPDATE syntax for updating.

Setup for examples to follow:

CREATE TABLE iodku (
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(99) NOT NULL,
    misc INT NOT NULL,
    PRIMARY KEY(id),
    UNIQUE(name)
) ENGINE=InnoDB;

INSERT INTO iodku (name, misc)
    VALUES
    ('Leslie', 123),
    ('Sally', 456);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
+----+--------+------+
| id | name   | misc |
+----+--------+------+
|  1 | Leslie |  123 |
|  2 | Sally  |  456 |
+----+--------+------+

The case of IODKU performing an “update” and LAST_INSERT_ID() retrieving the relevant id:

$sql = "INSERT INTO iodku (name, misc)
    VALUES
    ('Sally', 3333)            -- should update
    ON DUPLICATE KEY UPDATE    -- `name` will trigger "duplicate key"
    id = LAST_INSERT_ID(id),
    misc = VALUES(misc)";
$conn->query($sql);
$id = $conn->insert_id;        -- picking up existing value (2)

The case where IODKU performs an “insert” and LAST_INSERT_ID() retrieves the new id:

$sql = "INSERT INTO iodku (name, misc)
    VALUES
    ('Dana', 789)            -- Should insert
    ON DUPLICATE KEY UPDATE
    id = LAST_INSERT_ID(id),
    misc = VALUES(misc);
$conn->query($sql);
$id = $conn->insert_id;      -- picking up new value (3)

Resulting table contents:

SELECT * FROM iodku;
+----+--------+------+
| id | name   | misc |
+----+--------+------+
|  1 | Leslie |  123 |
|  2 | Sally  | 3333 |  -- IODKU changed this
|  3 | Dana   |  789 |  -- IODKU added this
+----+--------+------+

Found a mistake? Have a question or improvement idea? Let me know.

Table Of Contents