如何从表单中获取结果并将它们发送到PHP和SQL中的不同表

Question

I am trying to take the results from my input form and sent the results to two different tables.

In my first table I have the following columns: PersonID, StudentID, FirstName, LastName,

In my second table I have the following columns: EventID, EventName

In my third linking table I have the following columns: PersonID, EventID and Time

From my form, I want to take all the inputs and distribute them across all three tables, but I'm not sure how to do this.

Here is my PHP:

  <form action="enter.php" method="post">
    <h2>Student ID: </h2>
    <input name="StudentID"/>
    <h2>First Name: </h2>
    <input name="FirstName"/>
    <h2>Last Name: </h2>
    <input name="LastName"/>
    <h2>Event Name: </h2>
    <input name="EventName"/>
    <h2>Event Time: </h2>
    <input name="EventTime"/>
    <br /><input type="submit" name="submit" value="Enter Results for Person"/>
  </form>

<?php
if(!mysqli_select_db($dbcon,'NAMEOFDATABASE')) {
    echo 'Database not selected!';
} 
if(isset($_POST['submit'])){

$StudentID = $_POST['StudentID'];
$FirstName = $_POST['FirstName'];
$LastName = $_POST['LastName'];

$sql = "INSERT INTO Persons (StudentID, FirstName, LastName) VALUES ('$StudentID','$FirstName', '$LastName')";
if(!mysqli_query($dbcon,$sql)) {
    echo 'Person was NOT inserted into Persons Table Succesfully';
} else {
    echo 'Person was inserted into Persons Table Succesfully';
}



}


?>

Any help is appreciated :)

Answer 1

steps (besides what @Jens says which is valid):

1) insert into person table like you do 
2) insert into Events in the same way (separate statement and mysqli_query statement) 
3) select person_id from persons where student_id = <that student id> -- let's hope that your table constraint makes student ID unique >
4) select event_id from Events where event_name = <event_name from above> -- same warning about event name being unique in the events table
5) insert the person_id event_id in the cross table 

Give this, some other logic must exist. If the person exists with that student id you should catch the violation. You could ignore it or update the students name maybe. If you have the same event? Same. If you can't insert the student for some reason other than the student already existing, you should not proceed. If you can't insert the event you probably don't need to undo thee student insert.

I would assume you are doing this as a learning exercise and want to code the logic by hand. Otherwise you would be using an ORM https://www.killerphp.com/articles/what-are-orm-frameworks/ .

If you have StudentID and EventName as primary keys you already have the IDs, so:

$sql1 = "INSERT INTO Persons (StudentID, FirstName, LastName) VALUES ('$StudentID','$FirstName', '$LastName')";
$sql2 = "INSERT INTO Events (EventName, EventTime) VALUES ('$EventName','$EventTime')";
$sql3 = "INSERT INTO EventPerson (EventName, StudentID) VALUES ('$EventName','$StudentID')";

// insert person
if(mysqli_query($dbcon,$sql1)) {
    echo 'Person was inserted into Persons Table Successfully';
    // set error and return  
} elsif (// error is already exists - primary key error){
    echo 'Person already existed';
} else {
    echo 'Person was NOT inserted into Persons Table Successfully';
    // set error and return
}

// insert event
if(mysqli_query($dbcon,$sql2)) {
    echo 'Event was inserted into Events Table Successfully'; 
} elif (// error is already exists - primary key error){
    echo 'Event already existed';
} else {
    echo 'Event was NOT inserted into Events Table Successfully';
    // set error and return
}

// insert cross reference table
if(mysqli_query($dbcon,$sq3)) {
    echo 'Reference was inserted successfully';  
} else {
    echo 'Reference was NOT inserted successfully';
    // set error and return
}

and this is still bad code, because of injection mentioned above. See and use: http://php.net/manual/en/mysqli-stmt.bind-param.php, for example:

$stmt = $mysqli->prepare("INSERT INTO Persons (StudentID, FirstName, LastName) VALUES (?, ?, ?)");
$stmt->bind_param('sss', $StudentID, $FirstName, $LastName);