Getting back a generated unique id after insert - SOLVED

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Getting back a generated unique id after insert - SOLVED

Post 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
Last edited by ReverendDexter on Mon Jun 25, 2007 1:22 pm, edited 1 time in total.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

Well, I discovered part of my issue - I was trying to select using a matching SYSDATE() call. :roll:
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post 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:
Post Reply