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

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
paladaxar
Forum Commoner
Posts: 85
Joined: Fri Jun 18, 2004 11:50 pm

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

Post 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.
Last edited by paladaxar on Thu Jun 08, 2006 9:24 am, edited 1 time in total.
User avatar
ambivalent
Forum Contributor
Posts: 173
Joined: Thu Apr 14, 2005 8:58 pm
Location: Toronto, ON

Post by ambivalent »

If I understand you correctly, you should take a look at mysql_insert_id()
paladaxar
Forum Commoner
Posts: 85
Joined: Fri Jun 18, 2004 11:50 pm

Post 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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
paladaxar
Forum Commoner
Posts: 85
Joined: Fri Jun 18, 2004 11:50 pm

Post by paladaxar »

Awesome. Thanks Weirdan. Problem solved.
Post Reply