Wikis & learning

Insert a record and capture the generated identifier

A table can give every record an identifier, even when two rows share the same title. Let the database generate the ID and read it back on the same connection.

A table can give every record an identifier, even when two rows share the same title. Let the database generate the ID and read it back on the same connection.

Walkthrough

The table declares an integer primary key. The prepared statement sends the title separately from the SQL text. After execute returns, lastInsertId retrieves the identifier assigned by this connection.

PHP example

<?php
$db = new PDO('sqlite::memory:');
$db->exec('CREATE TABLE notes(id INTEGER PRIMARY KEY, title TEXT)');
$insert = $db->prepare('INSERT INTO notes(title) VALUES(?)');
$insert->execute(['April guide']);
echo 'Saved note ', $db->lastInsertId();

Expected output

Saved note 1

Change and check

Insert another title. Capture each ID directly after its insert, then retrieve both rows and check which title belongs to each one.