在PHP的第二个查询中使用来自一个mysqli查询的值

Question

I'm trying to build a registration page. I'm trying to associate the kiddies who are going to camp with the parent that is paying for camp. I've established a foreign key, and these queries run fine in phpAdmin.

The first query inserting the new kid into the database works fine. -the second query asking for the kidID that was just added also returns the correct value.

HOWEVER my third query, which requires the result of the second query does not work. I'm pretty new to this world and would greatly appreciate any help that could be offered.

require_once 'login.php';
//sending data to server. 
$MySQL = new mysqli($db_hostName, $db_username, $db_password, $db_databaseName);



$MySQL->query("INSERT INTO `Kid`(`First_Name`, `Last_Name`, `Parent1_First_Name`, `Parent1_Last_Name`, `Parent2_First_Name`, `P2_Last_Name`, `P1_Phone`, `P2_Phone`, `P1_Email`, `P2_Email`, `EmergencyContact_FName`, `EmergencyContact_LName`, `EmergencyContact_Phone`, `KidStreetAddress`, `KidZip`, `SpecialNeeds`, `DOB`, `PickupID`, `kidSex`, `SwimID`) 
        VALUES ('$kidFirstName','$kidLastName','$p1FirstName','$p1LastName','$p2FirstName','$p2LastName','$p1FinPhone','$p2FinPhone','$p1Email','$p2Email','$eFirstName','$eLastName','$eFinPhone','$kidStreetAddress',$kidZip,'$kidSpecialNeeds','$cDOB','$kidPickup','$kidSex',(SELECT `SwimID` FROM `SwimmingStrength` WHERE `SwimID` = 1))");

$kidID = $MySQL->query("SELECT `KidID` FROM `Kid` ORDER BY `KidID` DESC LIMIT 1");
$kidID->data_seek($i);
$row = $kidID->fetch_row();
$kidFinID = $row[0];

$MySQL->query("INSERT INTO `Customer`(`First_Name`, `Last_Name`, `Billing_Address`, `Billing_Zip`, `Billing_State`, `Billing_Phone`, `Billing_Email`, `KidID`) 
VALUES ('$BillFirstName','$BillLastName','$BillFullAddress',$BillZip,'$BillState','$billFinPhone','$BillEmail',(SELECT `KidID` FROM `Kid` WHERE `KidID`=$kidFinID)");

$kidID->close();
$MySQL->close();

My guess is somehow I need to make sure PHP is grabbing the result of the second query before running the third, but not sure how to set that up.

Answer 1

INSERT has two forms. One is VALUES and takes constants. The other is SELECT. The two do not mix.

You want INSERT . . . SELECT only, used like this:

INSERT INTO `Customer`(`First_Name`, `Last_Name`, `Billing_Address`, `Billing_Zip`, 
                       `Billing_State`, `Billing_Phone`, `Billing_Email`, `KidID`) 
    select '$BillFirstName', '$BillLastName', '$BillFullAddress', $BillZip, '$BillState',
           '$billFinPhone', '$BillEmail', `KidID`
    from Kid
    where `KidID` = $kidFinID;

In other words, you can put constants on the SELECT list line. In fact, you don't really need the VALUES form of INSERT at all.