INSERT ID from foreign key table

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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)
    WHERE FirstName='$contact1'
    AND Surname='$contact1'

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

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

Any ideas?


Re: INSERT ID from foreign key table

zaphod wrote:
Quoted text here. Click to load it

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 for
more information on the LAST_INSERT_ID() function in MySQL.

Bill K.

Site Timeline