Hunter Noman Hunter Noman - 16 days ago 8
MySQL Question

PHP/MYSQL Selecting a MAX value and using it as an inserted value

I have a small database project using HTML forms and PHP code. It is working perfectly except the last part. Basically, I have my database connection setup and working in my PHP, and upon hitting the Add button it should insert values from the form to the database. My instructor said that due to table constraints it has to be inserted in a certain order, basically address table first and then staff table. IF I comment out the staff part of code, my successful confirmation page appears and the address appears in the database every time with an auto incremented address_id. The issue is that I'm supposed to query for a MAX(Address_id) and use that for inserting the staff part, as it uses address_id as a foreign key. When I do that, I get a foreign key constraint error on update cascade. If I completely pull out the INSERT staff code, and put a 'debug' to print the MAX(address_id), it prints correctly. I just can't get it to insert to the staff table correctly so that everything from my form creates a staff record. Here is the code:

$userQuery = "INSERT INTO address (address, district, city_id, postal_code, phone)
VALUES ('$address', '$district', '$city', '$postal_code', '$phone') ";

$addressResult = mysqli_query($connect, $userQuery);
if (!$addressResult)
{
die("Could not successfully run query ($userQuery) from $db: " .
mysqli_error($connect) );
}



$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>Average hourly wage:".$row['MAX(address_id)']."</p>");

}**/


$userQuery1 = "INSERT INTO staff (first_name, last_name, address_id, email, store_id)
VALUES ('$first_name', '$last_name', '$row', '$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 are not calling the correct associative index. You are just calling the array:

$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')";