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.

Answers


There is no built-in mechanism for that; you have to do a separate SELECT.


Need Your Help

use str.format() in django logging

python django string logging format

I use django 1.6 and python 3.2 in Ubuntu 12.04.And i find django logging is using the python's built-in logging module.As the dictConfig format(python 3.x version) said, I can use 3 kind format st...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.