I had the following tables in my dabatase: tbl_students and tbl_courses.
I have in mind a project for a music school where a student can sign for 1 or more courses, for example, when I register a student more than one course can be chosen, like guitar and piano, deppending on his choice (for now i'm registering students by myself so this is just hypothetical).
I found that i had to deal with many to many relationships as 1 student can have many courses and in a course there are many students inscribed in them.
The next step I did was creating an intermediate table that i called tbl_students_courses with the primary keys of both tables.
My tables look like this:
tbl_students
--------- --------- --------------- ---------
| st_id | st_name | st_email | st_code |
--------- --------- --------------- ---------
| 1 |John Doe | [email protected] | 55555 |
--------- --------- --------------- ---------
tbl_courses
--------- ----------- --------------------------
| cr_id | cr_name | cr_desc |
--------- ----------- --------------------------
| 1 | Guitar | Guitar course description|
--------- ----------- --------------------------
| 2 | Bass | Bass course description |
--------- ----------- --------------------------
| 3 | Violin | Violin course description|
--------- ----------- --------------------------
| 4 | Drums | Drums course description |
--------- ----------- --------------------------
| 5 | Piano | Piano course description |
--------- ----------- --------------------------
tbl_students_courses
--------- --------- ---------
| st_id | cr_id |date_insc|
--------- --------- ---------
| ----- | ----- | ----- |
--------- --------- ---------
Now my problems are, I don't know how can i associate the tables with my php code. I assume that if i choose 2 courses when i sign up the student it should create 2 records on the intermediate table where only the cr_id differs from each other.
The other problem is getting the ID of the courses through the checkboxes with php, so when i select, for example, the piano and guitar checkboxes, it should return the id of both courses, run a query and insert the records.
Note: With my php code, the foreach loop is obviously just picking up the strings and not the ID of the course i wanted.
Here is my HTML:
<div >
<label >Select the courses</label>
<div >
<div >
<input name="course[]" type="checkbox" value="Guitar">
<label >Guitar</label>
</div>
<div >
<input name="course[]" type="checkbox" value="Bass">
<label >Bass</label>
</div>
<div >
<input name="course[]" type="checkbox" value="Violin">
<label >Violin</label>
</div>
<div >
<input name="course[]" type="checkbox" value="Drums">
<label >Drums</label>
</div>
<div >
<input name="course[]" type="checkbox" value="Piano">
<label >Piano</label>
</div>
</div>
Here is my PHP:
<?php
if (isset($_POST['submit'])) {
include_once '../includes/functions.php';
$studentName = addslashes($_POST['sname']);
$studentEmail = addslashes($_POST['semail']);
$studentCode = intval($_POST['scode']);
$studentcourse = $_POST['course'];
$query = "INSERT INTO tbl_students (st_name, st_id, st_code) "
. "VALUES ('$studentName', '$studentEmail', '$studentCode')";
$link = connection_db();
$result = mysqli_query($link, $query);
if($result){
$sql = "SELECT LAST_INSERT_ID()";
$result = mysqli_query($link, $sql);
$last = mysqli_fetch_assoc($result);
foreach($studentcourse as $value){
$data = date('Y-m-d H:i:s');
$lastID =($last["LAST_INSERT_ID()"]);
$sql = "INSERT INTO tbl_students_courses (st_id, cr_id, date_insc) VALUES ($lastID, '$value', '$data')";
$result = mysqli_query($link, $sql);
}
echo "<script>alert('Data saved successfully');</script>";
print "<script>top.location = 'index.php?id=2';</script>";
} else {
echo "<script>alert('ERROR! Could not save data');</script>";
}
}
?>
Any help or suggestions to improve the question are highly appreciated.
CodePudding user response：
Your code would actually work if you put your IDs into your checkboxes as values
so rather using
<input name="course[]" type="checkbox" value="Piano">
type this instead
<input name="course[]" type="checkbox" value="5">
ofc putting the IDs manually would not be that great so you have to read all your courses out of the database and generate the checkboxes based on your data.
So you could create function to get all courses from the database like this
function getAllCourses(mysqli $link){
$sql ="SELECT cr_id,cr_name,cr_desc FROM tbl_courses";
$result = mysqli_query($link, $sql);
$courses = [];
while($row = mysqli_fetch_assoc($result)){
$courses[]=$row;
}
return $courses;
}
and then at the place before you generate your HTML Code, you can get all of them
$courses = getAllCourses($link);
afterwards you could generate your HTML Code like this
<div >
<label >Select the courses</label>
<div >
<?php
foreach ($courses as $course): ?>
<div >
<input name="course[]" type="checkbox" value="<?= $course['cr_id'] ?>">
<label ><?= $course['cr_name'] ?></label>
</div>
<?php
endforeach; ?>
</div>
Also your student code seems to be your real identifier of your students, so you dont need to have an ID with auto increment in your database. you can also define your student code row as Primary key in your students table.
This way you dont need to get last insert IDs since you already have your studends.
Also i see that youre SQL Code is insecure, you just place the POST Values into SQL Query, with this an SQL Injection is possible, you need to use Prepare statements here. Where you prepare an SQL Statement with placeholder and execute them with values.
$query = "INSERT INTO tbl_students (st_name, st_email st_code) "
. "VALUES (?,?,?)";
$statement = mysqli_repapre($link, $query);
mysqli_stmt_bind_param($statement,"sis",$studentName,$studentEmail,$studentCode);
mysqli_stmt_execute($statement);
This code is not tested but i hope it gives you an idea.