Getting ID of SQLite row that caused the IGNORE with PHP PDO
I have a SQLite table with a UNIQUE constraint, simplified here:
CREATE TABLE addresses ( addressID INTEGER PRIMARY KEY, officeName TEXT, address TEXT NOT NULL CHECK(address<>''), UNIQUE (officeName, address)
And I add new addresses to the list and get the new IDs with PHP PDO like
$sth = $db->prepare("INSERT OR IGNORE INTO addresses (officeName,address) VALUES (?,?)"); $sth->execute(array($office,$address)); $thisAddressID = $db->lastInsertId();
The first two lines add an address to the database if, and only if, the address doesn't already exist. The last line gives me the ID of the newly added address… if an address was added. Otherwise, it just gives me the ID of the last added address. What I'm wondering, is if there's some good way to get the ID of the row that caused the IGNORE if the INSERT was ignored due to a duplicate. I guess I can manually cache and compare the last inserted ID, and if I get the same one again, do a SELECT and search for the ID. But I'm kinda hoping there may be a more efficient built-in way of getting that value.
There is no built-in mechanism for that; you have to do a separate SELECT.