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?
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.
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
Can you do an ON DUPLICATE KEY SELECT id?
I've never seen that trigger used that way.
Would be pretty slick though!!!
sadly, da manual he say no
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.