ibazz — 2011-03-18T14:34:34-04:00 — #1
I thought I read a nifty solution from rudy a couple of weeks ago but I can't seem to find it.
I want to insert a new record to a table and get the last_insert_id but in the same query, I want to get the existing id if the recrd was already there.
here is how I currently insert and get last_insert_id
INSERT into file_sequencing
values ( '123', 'pages', 'Home', CURRENT_TIMESTAMP, ? )
and here is how I get last_insert_id
all done in a transaction.
I want to get the file_id for this file_name, when there is a new record created or it already exists.
Can it be done in one query?
system — 2011-03-18T15:37:49-04:00 — #2
I assume by transaction, you mean a single PHP statement and not a DB transaction?
You could do that using a stored procedure... or a mysql-i multi-query... but with straight PHP+MySQL, not possible in one statement, as far as I know.
r937 — 2011-03-18T16:45:44-04:00 — #3
bazz, you're thinking of INSERT with ON DUPLICATE KEY, and of course the ON DUPLICATE KEY requires that you have defined a unique key in the table other than the auto_increment
then getting the id whether the insert succeeded or not will depend on a second query which searches on the unique key
system — 2011-03-18T18:50:09-04:00 — #4
Can you do an ON DUPLICATE KEY SELECT id?
I've never seen that trigger used that way.
Would be pretty slick though!!!
r937 — 2011-03-18T18:55:59-04:00 — #5
sadly, da manual he say no
ibazz — 2011-03-19T22:05:51-04:00 — #6
on dulpicate key would be ok except I wanted to get the id (or the last_insert_id), as appropriate to what was/was not, entered.
I have done it with two queries now - insert and then queried for the id where conditions are true.