INSERTing INTO Multiple Tables

[CaptainChainsaw]

Hi there, I've been hunting thru the net trying to find some info on inserting data to multiple tables using MySQL.

I have two tables ( LookupTable and MainTable):

LookupTable(
FirstID
SecondID
)

MainTable(
ID
Name
Address
etc
etc
)

Once I insert data into the MainTable I want to also insert the ID from the MainTable to the LookupTable as the FirstID. Then I want to check for the highest ID number in the SecondID field that is associated with the First ID then add one to the highest value and insert that to the SecondID.
FirstID SecondID
1 1
1 2
1 3
1 4

In the above table I would want to insert 1 to the FirstID then find the highest number in the SecondID associated with the FirstID "1" then add one to it so that my next row in the table would be:

FirstID SecondID
1 5

How would I go about doing this? I've been looking thru Kevin Yank's book "Build Your Own Database Driven Website Using PHP & MySQL", its pretty good on the whole but updating two tables simultaneously seems to be lacking detail.

I hope I've made this clear enough, I may have repeated myself there but I was wanted to make sure that what I've written is cleary understood.

Thank you in advance, I'm off to get some sleep now!


CaptainChainsaw

[chrisjk]

I've never come across a way to insert data into more than one table at once. The easiest and probably the only way is to execute the inserts seperately one after the other

You can use the php function mysql_insert_id to get the id of the last record inserted so you may use it in the next table