Page 1 of 1

Insert Into Two Tables Using Correct Foreign Key

Posted: Sun Jun 08, 2008 10:33 am
by dgny06
Thanks in advance for any help......

I am doing a real estate site and I have a table to to hold information about a listing (tbListing) and a table that joins to to tbListing via ListingID (tbDescription). The table tbDescription holds only the ListingID primary key and a VARCHAR(1000) that will hold a long description for the listing. Can someone let me know the correct way to to do an insert into the two tables keeping in mind the correct foreign used in the join.

What I was planning on doing was creating a variable and setting its value to be the MAX ListingID from tbDescription + 1. I would then insert that value into tbListing since I know that it would be the next value entered into tbDescription via auto increment. This seems like it would work but I am sure it is not the standard to do what I am trying to do.

Re: Insert Into Two Tables Using Correct Foreign Key

Posted: Sun Jun 08, 2008 11:37 am
by onion2k
Assuming tbListing has an autoincrement primary key ... insert the record into the listing table, then use mysql_insert_id() to find out what ID it was given and insert the description into the tbDescription table with it.

Re: Insert Into Two Tables Using Correct Foreign Key

Posted: Sun Jun 08, 2008 12:14 pm
by Jaxolotl
I had problems once using mysql_insert_id(), in one server it makes the MySQL server to crash, so i made a function to retrieve it by making a simple sql query end then returning the result

Code: Select all

 
$query ="SELECT LAST_INSERT_ID()";
 
MySQL manual link
http://dev.mysql.com/doc/refman/5.0/en/ ... ue-id.html