Page 1 of 1

[solved] submit two entries, figure out the foreign key?

Posted: Tue Jun 06, 2006 11:20 am
by paladaxar
So here's my dilemma:

I have a site that needs three types of accounts: company, manager and employee.

I want a manager to be able to create a company account and manager account at the same time. So, after the single form is filled out, I need to make two entries...one to the company database, and one to the user database. The company table will have a primary key of comp_id but i need to insert that into the user table as a foreign key. Since my table auto increments the primary keys, how would i know what the key will be so that i can put it in the user table?

The only thing that i can think of right now is to enter the company info, then check for the max comp_id and use that as the foreign key. That doesnt sound like the best plan though...i think that would leave room for error.

Posted: Tue Jun 06, 2006 1:07 pm
by ambivalent
If I understand you correctly, you should take a look at mysql_insert_id()

Posted: Thu Jun 08, 2006 6:31 am
by paladaxar
I think that might be just what I am looking for. But...does that grab the last auto_incrament from a single user, single session only? Or does it grab the last auto incrament period? For example, if another query was being run on another table by a user in, say, another state, could that cause problems? Is there a chance that this function could get the auto incrament value from some other operation that just happened to be running on the db by some other user?

Posted: Thu Jun 08, 2006 8:55 am
by Weirdan
MySQL manual wrote: The value of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients.

Posted: Thu Jun 08, 2006 9:22 am
by paladaxar
Awesome. Thanks Weirdan. Problem solved.