Need to populate a foreign key in one DB with the value of the primary key in another

tryin_to_learn

I have a table called attendees that has attendeeID as a primary key. Then I have a table called relationships that has groupID as its primary key and attendeeID as a foreign key (from the attendees table). Both tables get populated from fields in a single HTML form and both of the primary keys are auto-incremented.

What I need to do is populate both tables at once when the submit button is clicked and insert whatever auto-incremented value attendeeID was given in the attendees table into the attendeeID field in relationships.

How do I do that?

Roger_Ramjet

You insert the record into the main table then use mysql insert id to find out what id it was given. You then use that for the insert into the foriegn key table.

tryin_to_learn

Thanks! That definitely seems to be what I need. I'm having trouble with syntax. I've tried several things -- this is the most recent. This one at least doesn't throw an error, but it also doesn't put any value in the attendeeID field in the 2nd table. What am I doing wrong?

$sql = "INSERT INTO attendees SET firstName='$firstName', lastName='$lastName', typeOfAttendeeID='1', conventionID='1'";
		$foreignAttendeeID = mysql_insert_id();
		$sql2 = "INSERT INTO relationships SET role = 'mother', conventionID='1', attendeeID = '$foreignAttendeeID'";
		$result = $db->query($sql);	
		$result2 = $db->query($sql2);

tryin_to_learn

I thought for sure I had already tried this, but I guess I hadn't because it's working now. Makes sense -- you have to do the query before you can get the last id:


 $sql = "INSERT INTO attendees SET firstName='$firstName', lastName='$lastName', address1='$address1', address2='$address2', city='$city', state='$state', zip='$zip', dayPhone='$dayPhone', evePhone='$evePhone', cellPhone='$cellPhone', fax='$fax', email='$email', typeOfAttendeeID='1', conventionID='1'";

	$result = $db->query($sql);	
	$foreignAttendeeID = mysql_insert_id();

	$sql2 = "INSERT INTO relationships SET role = 'mother', conventionID='1', attendeeID = '$foreignAttendeeID'";

	$result2 = $db->query($sql2);