Foreign Key Not Populated MySQL

Question

Foreign key not populating in child table with mysql. Total newbie in all things programming and sql. Creating Web site Form to populate person data using PHP/MYSQL which then launches a secondary Web Site Form to populate car maintenance data. Problem is the Foreign Key from the Parent Table is not updating the Child table. Any help is much appreciated.

Parent Table:

$sql = "CREATE TABLE persons 
(
Firstname CHAR(15),
Lastname CHAR(15),
Age INT,
PRIMARY KEY(Firstname)
)";

Child Table:

$sql = "CREATE TABLE cardata
(
CID INT NOT NULL AUTO_INCREMENT, 
Firstname CHAR(15) NOT NULL,  
Manufacturer CHAR(25),
Model CHAR(15),
Year INT,
`Oil Miles` INT,
`Oil Date` DATE,
`Rotation Miles` INT,
`Rotation Date` DATE,
PRIMARY KEY(CID),
FOREIGN KEY (Firstname) REFERENCES persons (Firstname)
ON  UPDATE CASCADE ON DELETE CASCADE
)";

Parent Table Insert:

$sql="INSERT INTO Persons (FirstName, LastName, Age)
VALUES
('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";

Child Table Insert:

$sql="INSERT INTO cardata (Firstname,Manufacturer)
VALUES
('LAST_INSERT_ID()','$_POST[manufacturer]')";

@JW. : What's the point of adding useless tags like the `[query]` tag you've been adding to all the questions you answer? — ypercubeᵀᴹ, Mar 17 '13 at 13:03

score 0 · Answer 1 · edited May 23 '17 at 12:04

It's because you are inserting a string. LAST_INSERT_ID() shouldn't be wrap with single quotes as this makes it string literal. Remove the single quotes and it will surely work.

$sql="INSERT INTO cardata (Firstname,Manufacturer)
VALUES (LAST_INSERT_ID(),'$_POST[manufacturer]')";

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

Foreign Key Not Populated MySQL

1 Answers1