CODEWITHSUNDEEP
mysqldatabasedatabase-design
dan
dan

Reputation: 67

How can I insert data into a table when I need to know the value of the foreign key from another table?

I have two tables:

Customer with the field ID which is auto increment and the Primary Key and

address with the field customer_ID which is a foreign key .

I have a form which asks for all of the customer info and their addresses ( can be more than 1)

I run the following two query's to update the database however I am currently inputting the value for Customer_ID manually to test it. 

mysql_query("INSERT INTO customer(First_Name, Surname, Company_Name, Telephone, Alt_Telephone)VALUES('$fName', '$lName', '$cName', '$tel', '$altTel')") ;
mysql_query("INSERT INTO address(line_1, line2, town, postcode, customer_ID)VALUES('$line1', '$line2', '$town', '$post_code', '2')") ;

The customer_ID field should be the same as the current ID field from the customer table how can I make sure that when the address table is filled in it is doing this automatically?

Upvotes: 0

Views: 213

Answers (2)

Filip Roséen
Filip Roséen

Reputation: 63797

Using PHP

The easiest solution to your problem would be to call mysql_insert_id in your php-code after your first call to mysql_query (inserting data into your table customer).

This function will return the last id inserted, though you'll have to make sure that the id of customer is a primary key which is auto-incremented.

The value returned by mysql_insert_id is local to the given connection/session, even if another connection inserts a record after you did you'll get the id inserted by you.

Using MySQL

You could use the SQL-function LAST_INSERT_ID() as the below, just make sure that the previous sql-query ran was the insert into customer (as well as checking so that the id is indeed a key and auto-incremented).

-- first insert into `customer`

INSERT INTO address
  address(line_1, line2, town, postcode, customer_ID)
VALUES
  ('$line1', '$line2', '$town', '$post_code', LAST_INSERT_ID());

Same thing here as with the php-version, LAST_INSERT_ID() will retrieve the last inserted ID by you.

You could also use a SQL feature which could be described as SELECT INTO, see the below snippet:

INSERT INTO address
 address(line_1, line2, town, postcode, customer_ID)
SELECT
  '$line1', '$line2', '$town', '$post_code', customer.id
FROM customer
WHERE <some_constraint>

Upvotes: 2

Puggan Se
Puggan Se

Reputation: 5846

you can (1) use mysql_insert_id() in php, or (2) LAST_INSERT_ID() direct in your sql

code exempl 1: 

mysql_query("INSERT INTO customer(First_Name, Surname, Company_Name, Telephone, Alt_Telephone)
    VALUES('$fName', '$lName', '$cName', '$tel', '$altTel')") ;
$customer_id = mysql_insert_id();
mysql_query("INSERT INTO address(line_1, line2, town, postcode, customer_ID) 
    VALUES('$line1', '$line2', '$town', '$post_code', {$customer_id})") ;

code exempl 2: 

mysql_query("INSERT INTO customer(First_Name, Surname, Company_Name, Telephone, Alt_Telephone)
    VALUES('$fName', '$lName', '$cName', '$tel', '$altTel')") ;
mysql_query("INSERT INTO address(line_1, line2, town, postcode, customer_ID) 
    VALUES('$line1', '$line2', '$town', '$post_code', LAST_INSERT_ID())") ;

Upvotes: 1

Related Questions