single query to insert and get last_id or get existing id?

[IBazz]

Hi,

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
  ( business_id
  , content_category
  , file_name_in_home_language
  , last_updated
  , last_updated_by
  )
  values ( '123', 'pages', 'Home', CURRENT_TIMESTAMP, ? )

and here is how I get last_insert_id

SELECT 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?

[transio]

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]

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

[transio]

R937,

Can you do an ON DUPLICATE KEY SELECT id?

I've never seen that trigger used that way.

Would be pretty slick though!!!

[r937]

Can you do an ON DUPLICATE KEY SELECT id?

sadly, da manual he say no

[IBazz]

thanks.

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.

bazz