Mysql – How to populate a table with a foreign key from a dropdown list

MySQLPHP

I am building an event planning application and require some help. To begin the user will create an event. They will then be required to register participants to this event using a user form.

The eventID is a foreign key in the participants table and all participants registered must be associated with a particular event.

So far, I have managed to create a drop down list that shows only the event ids that are already generated. I need to be able to create a participant and select an event id from the dropdown and store these in the participants table. However, I keep receiving the below error message:

Failed. Cannot add or update a child row: a foreign key constraint fails (event_planner.participants, CONSTRAINT participants_ibfk_1 FOREIGN KEY (eventID) REFERENCES events (eventID) ON UPDATE CASCADE).

Any help would be greatly appreciated.

<!DOCTYPE html>

<?php
$link=mysqli_connect("localhost","root","");
mysqli_select_db($link,"event_planner");
?>

<html>
<head>
    <title>Participant's Registration Page</title>
    <link rel="stylesheet" type="text/css" href="style.css">
</head>
<body
    <div id="form">
        <form action="partregisterprocess.php" method="POST">

        <h1 align="center">Participant's Registration Page</h1>

        <p>
        Name:<br />
            <input type="text" id="name" name="name" required  />
            </p>

        <p>
        Surname:<br />
            <input type="text" id="surname" name="surname" required  />
            </p>

        <p>
        Date of Birth:<br />
            <input type="date" id="dob" name="dob" required  />
            </p>

        <p>
        Age at Camp:<br />
            <input type="text" id="age" name="age" required />
            </p>

        <p>
        Gender:<br />
            Male<input type="radio" value="male" name="gender"  />
            Female<input type="radio" value="female" name="gender" required />
            </p>

        <p>
        Address:<br />
            <input type="text" id="address" name="address" required />
            </p>


        <p>
        Contact No:<br />
            <input type="text" id="contact" name="contact" required />
        </p>


        <p>
        Next of Kin:<br />
            <input type="text" id="nextkin" name="nextkin" required />
            </p>

        <p>
        Next of Kin's Contact No:<br />
            <input type="text" id="nextContact" name="nextContact" required />
            </p>

        <p>
        Attendance at Camp:<br />
            <input type="text" id="attendcamp" name="attendcamp" required />
            </p>

        <p>
        Attendance at Sunday School:<br />
            <input type="text" id="attendschool" name="attendschool" required />
            </p>

        <p>
        Comments:<br />
            <input type="text" id="comments" name="comments" />
            </p>




        <p>
            <input type="submit" id="btn" value="Register"  />
            </p>


        <label> EventID </label>            
        <id="eventID" name="eventID" /> 


        <select>
<?php
        $res=mysqli_query($link,"SELECT * FROM events");
        while($row=mysqli_fetch_array($res))
{
?>
         <option><?php echo $row["eventID"]; ?></option>

<?php
}


?> 

    </select>
    </form>
    </div>
    </body>
    </html>

PROCESS PAGE

    <?php

    $Name = $_POST['name'];
    $Surname = $_POST['surname'];
    $Date_of_Birth = $_POST['dob'];
    $Age_at_Camp = $_POST['age'];
    $Gender = $_POST['gender'];
    $Address = $_POST['address'];
    $Contact_No = $_POST['contact'];
    $Next_of_Kin = $_POST['nextkin'];
    $Kin_ContactNo = $_POST['nextContact'];
    $Attendance_Camp = $_POST['attendcamp'];
    $Attendance_School = $_POST['attendschool'];
    $Comments = $_POST['comments'];
    $eventID = $_POST['eventID'];


mysql_connect("localhost", "root", "");
        mysql_select_db("event_planner");

$insert= mysql_query("INSERT INTO participants VALUES ('','$Name','$Surname','$Date_of_Birth','$Age_at_Camp','$Gender','$Address','$Contact_No','$Next_of_Kin','$Kin_ContactNo','$Attendance_Camp','$Attendance_School','$Comments','$eventID')")
    or die("Failed. ".mysql_error());

?>

Best Answer

The error is telling you that you are choosing an EventID which is not in the events table.

There are a few things you should do or consider:

  1. Never, ever, write a mysql_query using user input without sanitizing (filtering). You are opening a very big door to SQL Injection. If any user would write the surname O'Brien you'd be in for a surprise. If they would write Ha ha '; drop database ; -- you could very easily get a nasty surprise. Make sure you sanitize user's input (by way of mysql_real_escape_string(), in your case). Or, much better still, use prepared statements.

  2. You are using mysql_query, which is deprecated. You are recommended to use then mysqli or PDO instead. mysql_query is not part of PHP 7.0, which is already up and running. Check Choosing a MySQL API.

  3. It's not good practice to write INSERT INTO statements without column names. If, at any time, you decide to add an extra column to your table, this INSERT statement will stop working. And it will stop working at the worst possible moment. It is also more difficult to check that you're actually putting the right values into the right columns, because you don't have that information. So, INSERT INTO table (col1, col2, ...) is not only future proof but also self-documenting.

  4. For debugging purposes, it is always much better to get your SQL statement in a variable (that can be echoed, or added to the die instruction).

So, to make the minimum number of changes, I'd rather write:

$sql = sprintf("INSERT INTO participants (name, surname, event_id) " .
               "                  VALUES ('%s', '%s',    %d)",
               mysql_real_escape_string($Name),
               mysql_real_escape_string($Surname),
               intval($eventID)) ;

$insert = mysql_query($sql) 
    or die("Failed. " . mysql_error() . ".\nSQL was: " . $sql);

(You need to add as many columns as needed, with the propoer names; I put three as an example, and assumed names and types)

Related Question