Page 1 of 1

Getting back a generated unique id after insert - SOLVED

Posted: Mon Jun 25, 2007 1:00 pm
by ReverendDexter
I have a table that for data integrity reasons has an artificial primary key that's auto-incrementing. To insert into the table, I give it all the other values, and it generates a unique id for the row. For the sake of the situation, this is my job table

I also have a lookup table with some repeating values (a category table), and an intersection table that allows me to link the two in a many:many relationship, using the generated unique id from the job table as the foreign key.

However, to do this link at insert, I need the job_id number, and I'm wondering if there's a quick/dirty/easy way to have this fire back to me after the insert. Trying to do a select using the same data that I inserted isn't working (???), and I don't trust it anyway, as I could conceivably be pulling the wrong row, being that I'm not selecting on the primary key.

The only thought I had was to have a trigger fire on insert, that would put the most recently created id into a (really small) table, but that seems superfluous and prone to potential error.

Any thoughts?

-Dex

Posted: Mon Jun 25, 2007 1:09 pm
by volka

Posted: Mon Jun 25, 2007 1:10 pm
by ReverendDexter
Well, I discovered part of my issue - I was trying to select using a matching SYSDATE() call. :roll:

Posted: Mon Jun 25, 2007 1:21 pm
by ReverendDexter
I found there's a function mysql_insert_id() that does exactly what I need... which is exactly what you told me to look at.

Thanks volka!

:oops: Chalk this one up to insufficient research :oops: