将值插入mysql数据库并检索它们以插入另一个表

Question

I'm trying to insert values to mysql database table called pointofcontact and then retrieve the primary key called pocid to insert to another table called students.

Somehow my code always return the pocid to be 0 and i have no idea why. Gladly to get some help. Any help would be greatly appreciated! Here is my code:

$query="insert into pointofcontact(Username,Password,FirstName,LastName,ContactNumber,EmailAddress,Address,Gender,Status,BackupContactNumber,ProfilePic) values ('$username','$password','$firstname','$lastname','$mobilenumber','$email','$address','$gender','Normal','$backup','$attch')";
    if($con->query($query) === TRUE)
    {
        $query2="select POCID from pointofcontact where username= '$username'";
        $result2=$con->query($query2);
        if($result2 ->num_rows > 0)
        {
            while($row2 = $result2->fetch_assoc())
            {
                $pocid = $row2['POCID'];
                $query3= "insert into student(StudentFirstName, StudentLastName, Allergies, NRIC, POCID) values ('$cfirstname','$clastname','$callergies','$cnric','$POCID')";
            }
            if($con->query($query3) === TRUE)
            {

            }
            else
            {

            }
        }
    }
    else
    {
        echo "error";
    }

Answer 1

I haven't tested this, but it should do what you need:

// assuming proper validation and escaping is completed...

if($con->query("INSERT INTO pointofcontact (Username,Password,FirstName,LastName,ContactNumber,EmailAddress,Address,Gender,Status,BackupContactNumber,ProfilePic) VALUES ('$username','$password','$firstname','$lastname','$mobilenumber','$email','$address','$gender','Normal','$backup','$attch');"){
    $POCID=$con->insert_id;
    if($con->query("INSERT INTO student (StudentFirstName, StudentLastName, Allergies, NRIC, POCID) VALUES ('$cfirstname','$clastname','$callergies','$cnric','$POCID');")){
        // Pass
    }else{
        // Fail
    }
}else{
    // Fail
}