INSERT ID from foreign key table

[zaphod]

I have a form used to populate 2 tables: Provider and Contacts. Provider has
a foriegn key column - Contact1ID - which has a foreign key relationship
with the prmary key - ID - in the Contacts table. The form submits data to
Contacts then Provider but I need the ID generated by the Contacts
submission to be entered into the Contact1ID column in the Provider table.

If I use this:

INSERT INTO Provider(Contact1ID)
SELECT ID FROM Contacts
WHERE FirstName='$contact1{FirstName}'
AND Surname='$contact1{Surname}'

.... all I get is a new row.

UPDATE doesn't seem to allow SELECT to grab the new ID either.

Any ideas?

zaphod

[Bill Karwin]

zaphod wrote:

I need the ID generated by the Contacts
submission to be entered into the Contact1ID column in the Provider table.

Use the LAST_INSERT_ID() function to get the last ID value generated by
an AUTO_INCREMENT field in your Contacts table. Use that as an
expression in your INSERT INTO Provider statement.

INSERT INTO Contacts (...fields, excluding autoincrement key...) VALUES
(...)
INSERT INTO Provider (Contact1ID, ...) VALUES (LAST_INSERT_ID(), ...)

See http://dev.mysql.com/doc/mysql/en/in...functions.html for
more information on the LAST_INSERT_ID() function in MySQL.

Regards,
Bill K.