How to Insert Data into two tables linked by a foreign key?

Question

I am trying to create a statement so that when the submit button is pressed the data is passed into both tables (properties, frent).

The PK is automatically added to the properties table. This PK then needs to be pulled and added to the frent table as FK, along with the rest of the data submitted.

When I modify the statement to insert data into only the properties table it works fine. However when the information for the second table is entered everything stops working. I know there must be something wrong with the statement but I don't know what. I would be grateful for any advice. thank you in advance.

PropertyID is the PK for properties and the FK for frent. It is an auto increment which is why it is not included in the first section of the code.

The Statement I am trying to create:

$sql = "START TRANSACTION ;
        INSERT INTO properties (  PropertyType, AH, Bedrooms, Bathrooms, Ensuite, Kitchen, LivingRoom, DiningRoom, UtilityRoom, Conservatory, Garage, Garden, Parking,Furnished, Description, PostCode, AddL1, AddL2, AddL3,Area,County,Country) 
        VALUES( '$PropertyType', '$AH', '$Bedrooms','$Bathrooms', '$Ensuite', '$Kitchen', '$LivingRoom', '$DiningRoom', '$UtilityRoom', '$Conservatory', '$Garage','$Garden', '$Parking','$Furnished', '$Description',                  '$PostCode','$AddL1', '$AddL2', '$AddL3','$Area','$County','$Country');
        INSERT INTO frent (FRent,LAST_INSERT_ID(PropertyID), MinCon, PaymentExp, RCost)
        VALUES ('FRent','$PropertyID', '$MinCon', '$PaymentExp','$RCost');
        COMMIT";

Check this thread, http://stackoverflow.com/questions/3837990/last-insert-id-mysql Other option is execute first insert query then use mysql_insert_id() to get last inserted id — Amit Sukapure, Mar 07 '14 at 16:54

EselDompteur · Answer 1 · 2014-03-07T17:29:34.787

I suppose that the error in the wording of the table fields located in the second table. To me it looks as if this is a function call was used instead of the correct name of the field

change your query to something like this:

$sql = "START TRANSACTION ;
    INSERT INTO properties (  PropertyType, AH, Bedrooms, Bathrooms, Ensuite, Kitchen, LivingRoom, DiningRoom, UtilityRoom, Conservatory, Garage, Garden, Parking,Furnished, Description, PostCode, AddL1, AddL2, AddL3,Area,County,Country) 
    VALUES( '$PropertyType', '$AH', '$Bedrooms','$Bathrooms', '$Ensuite', '$Kitchen', '$LivingRoom', '$DiningRoom', '$UtilityRoom', '$Conservatory', '$Garage','$Garden', '$Parking','$Furnished', '$Description',                  '$PostCode','$AddL1', '$AddL2', '$AddL3','$Area','$County','$Country');
    INSERT INTO frent (FRent, PropertyID, MinCon, PaymentExp, RCost)
    VALUES ('FRent',LAST_INSERT_ID(), '$MinCon', '$PaymentExp','$RCost');
    COMMIT";

How to Insert Data into two tables linked by a foreign key?

1 Answers1