Hunter Noman Hunter Noman - 16 days ago 6
MySQL Question

Foreign Key constraint error when inserting a value in PHP/MySQL

I've inserted form values from an HTML page into a database with PHP but I have to couple this with another table. One query inserts some values to the address table, and this works. After pulling a MAXID value from the address query, I then have to use it to enter the other values into a 'staff' table. When I comment out the staff table query, it works. When the code is active I get this error:


Could not successfully run query
(INSERT INTO staff (first_name,
last_name, address_id, email, store_id) VALUES ('Hunter', 'Norman',
'', 'Test@mail.com', '2'))
from sakila: Cannot add or update a child
row: a foreign key constraint fails (
sakila
.
staff
, CONSTRAINT
fk_staff_address
FOREIGN KEY (
address_id
) REFERENCES
address

(
address_id
) ON UPDATE CASCADE)


I was previously helped to fix the associative index, but still have the error. Here's the code:

$maxQuery = "SELECT MAX(address_id) FROM address";
$result = mysqli_query($connect, $maxQuery);
$row = mysqli_fetch_assoc($result);
if (!$result)
{
die("Could not successfully run query ($userQuery) from $db: " .
mysqli_error($connect) );
}
else
{

print ("<p>Max Address ID:".$row['MAX(address_id)']."</p>");

}




$userQuery1 = "INSERT INTO staff (first_name, last_name, address_id, email, store_id)
VALUES ('$first_name', '$last_name', '{$row[MAX(address_id)]}', '$email', '$store_id')";
$staffResult = mysqli_query($connect, $userQuery1);
if (!$staffResult)
{
die("Could not successfully run query ($userQuery1) from $db: " .
mysqli_error($connect) );
}
else
{
print(" <h1>New Staff Record Added!</h1>");
print ("<p>The following record was added:</p>");
print("<table border='0'>
<tr><td>First Name</td><td>$first_name</td></tr>
<tr><td>Last Name</td><td>$last_name</td></tr>
<tr><td>Email</td><td>$email</td></tr>
<tr><td>Store ID</td><td>$store_id</td></tr>
<tr><td>Address</td><td>$address</td></tr>
<tr><td>City</td><td>$city</td></tr>
<tr><td>District</td><td>$district</td></tr>
<tr><td>Postal Code</td><td>$postal_code</td></tr>
<tr><td>Phone</td><td>$phone</td></tr>
</table>");
}

Answer

You're missing some quotes when you access the array. Also, assuming the FK is a number, the quotes in the query are unnecessary.

It should be:

$userQuery1 = "INSERT INTO staff (first_name, last_name, address_id, email, store_id) VALUES ('$first_name', '$last_name', {$row['MAX(address_id)']}, '$email', '$store_id')";

Hint: if you var_dump() the $userQuery1 you will see what it is running. Also, if you had PHP warnings turned on, you would see that it was trying to execute the PHP max() function with a constant, which it assumes is a string, address_id.