mercredi 20 janvier 2016

SQLite Insert or Ignore/Replace with lastInsertID in one statement

I feel like this should be easy...

I have a table like this:

CREATE TABLE table_name (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  UNIQUE (name COLLATE NOCASE)
)

No two names are the same, case insensitive. Right now I have users adding names and it does this:

INSERT INTO table_name (name) VALUES ("my name");

And I need to get the id of the row, which is easy with PHP PDO's lastInsertID(). But I also want, if the user is adding a name that's already in the database, for nothing to be added to the database, but still get that id without having to do another database call. I was hoping for something like

INSERT OR REPLACE INTO table_name (name) VALUES ("my name");

And have it just overwrite the same data into the cell and return the lastInsertID (even though it wasn't inserted?). But that doesn't work. What are my other options? Will I have to do a separate database query to see if the name field already exists?

Aucun commentaire:

Enregistrer un commentaire