Having a problem inserting a foreign key data into a table using a PHP form
- by Gideon
I am newbee with PHP and MySQL and need help...
I have two tables (Staff and Position) in the database. The Staff table (StaffId, Name, PositionID (fk)). The Position table is populated with different positions (Manager, Supervisor, and so on). The two tables are linked with a PositionID foreign key in the Staff table. I have a staff registration form with textfields asking for the relevant attributes and a dynamically populated drop down list to choose the position. I need to insert the user's entry into the staff table along with the selected position. However, when inserting the data, I get the following error (Cannot add or update a child row: a foreign key constraint fails). How do I insert the position selected by the user into the staff table?
Here is some of my code...
...
echo "<tr>";
echo "<td>";
echo "*Position:";
echo "</td>";
echo "<td>";
//dynamically populate the staff position drop down list from the position table
$position="SELECT PositionId, PositionName
FROM Position
ORDER BY PositionId";
$exeposition = mysql_query ($position) or die (mysql_error());
echo "<select name=position value=''>Select Position</option>";
while($positionarray=mysql_fetch_array($exeposition))
{
echo "<option value=$positionarray[PositionId]>$positionarray[PositionName]</option>";
}
echo "</select>";
echo "</td>";
echo "</tr>"
//the form is processed with the code below
$FirstName = $_POST['firstname'];
$LastName = $_POST['lastname'];
$Address = $_POST['address'];
$City = $_POST['city'];
$PostCode = $_POST['postcode'];
$Country = $_POST['country'];
$Email = $_POST['email'];
$Password = $_POST['password'];
$ConfirmPass = $_POST['confirmpass'];
$Mobile = $_POST['mobile'];
$NI = $_POST['nationalinsurance'];
$PositionId = $_POST[$positionarray['PositionId']];
//format the dob for the database
$dobpart = explode("/", $_POST['dob']);
$formatdob = $dobpart[2]."-".$dobpart[1]."-".$dobpart[0];
$DOB = date("Y-m-d", strtotime($formatdob));
$newReg = "INSERT INTO Staff (FirstName, LastName, Address, City, PostCode,
Country, Email, Password, Mobile, DOB, NI, PositionId) VALUES ('".$FirstName."',
'".$LastName."', '".$Address."', '".$City."', '".$PostCode."', '".$Country."',
'".$Email."', '".$Password."', ".$Mobile.", '".$DOB."', '".$NI."', '".$PostionId."')";
Your time and help is surely appreciated.
