I'm doing a school project - a website with students performances in various sports. I have three tables:
TABLE1 - "students"
TABLE2 - "sports"
TABLE3 - "performances"
I want to make a form that adds data into the third table. That form should include:
...but I have no idea how to achieve this.
I could just create a form where user user adds value and then copy-pastes sport_id and student_id from tables below it, but that's unpractical.
I've been searching the internet for a while, but I haven't found any solution to this and if I did, it was only for one foreign key.
Does anyone know how to do this? If so, I would highly appreciate it! :)
EDIT: I should've mentioned that tables "students" and "sports" already have all the data in them, I just need to insert new performances using that data.
Since the data is already in the tables for students and sports, this information can be queried with some select statements in order to populate some HTML dropdowns. The advantage of using the select queries and the dropdowns is that value of the options can be set to the database ID while showing the user the human-readable text. Then, the page just needs to monitor for the form's submission and insert the IDs from the dropdowns along with the performance metric. I have not tested the code below, but here is a quicky example of how that might work.
Note: I like the PDO interface for preparing SQL queries in order to prevent injection attacks.
<?php
$user = 'user';
$password = 'password';
$con = new PDO('mysql:dbname=dbname;host=127.0.0.1;chartset=urf8', $user, $password);
$student_stmt = $con->prepare('select * from students');
$student_stmt->execute();
$sport_stmt = $con->prepare('select * from sports');
$sport_stmt->execute();
if (isset($_GET['student']) && isset($_GET['sport']) && isset($_GET['value'])) {
$student = $_GET['student'];
$sport = $_GET['sport'];
$value = $_GET['value'];
$insert_stmt = $con->prepare('insert into preformances (sport_id, student_id, value) values (:sport_id, :student_id, :value)');
$insert_stmt->bindParam(':sport_id', $sport);
$insert_stmt->bindParam(':student_id', $student);
$insert_stmt->bindParam(':value', $value);
$insert_stmt->execute();
}
?>
<html>
<head>
<title>Form</title>
</head>
<body>
<form action="self.php" method="get">
Student:
<select name="student">
<?php while ($row = $student_stmt->fetch(PDO::FETCH_ASSOC)) { ?>
<option value="<?php echo $row['id']; ?>"><?php echo $row['firstname'] . " " . $row['lastname']; ?></option>
<?php } ?>
</select>
Sport:
<select name="sport">
<?php while ($row = $sport_stmt->fetch(PDO::FETCH_ASSOC)) { ?>
<option value="<?php echo $row['sport_id']; ?>"><?php echo "$row['sportname']"; ?></option>
<?php } ?>
</select>
Performance: <input name="value" type="text" />
<button type="submit">Submit</button>
</form>
</body>
</html>
Edit: Made the changes in the code in the suggested comment.
I think all you need to do is to get input values from your form ($variable = $_GET["classinput"];) and then connect to database and write mysqli query with input query for every table.
like this:
$query = mysqli_query($connection, "INSERT INTO STUDENTS(id,class,firstname,lastname) VALUES (null,\"$class\",\"$firstname\",\"$lastname\")");
And do this for all your tables.