PHP PHP MySQLi MySQLi Insert ID
Example
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
+----+--------+------+
