Deal with many to many database relationships and select/insert multiple checkbox values MYSQL using PHP

Question

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 | doe@gmail.com |  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 class="form-group">
                   <label class="col-form-label">Select the courses</label>
                   <div class="form-check">
                       <div class="form-check form-check-inline">
                           <input class="form-check-input" name="course[]" type="checkbox" value="Guitar">
                           <label class="form-check-label" >Guitar</label>
                       </div>
                       <div class="form-check form-check-inline">
                           <input class="form-check-input" name="course[]" type="checkbox" value="Bass">
                           <label class="form-check-label" >Bass</label>
                       </div>
                       <div class="form-check form-check-inline">
                           <input class="form-check-input" name="course[]" type="checkbox" value="Violin">
                           <label class="form-check-label" >Violin</label>
                       </div>
                       <div class="form-check form-check-inline">
                           <input class="form-check-input" name="course[]" type="checkbox" value="Drums">
                           <label class="form-check-label" >Drums</label>
                       </div>
                       <div class="form-check form-check-inline">
                           <input class="form-check-input" name="course[]" type="checkbox" value="Piano">
                           <label class="form-check-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.

Start by given in the check_buttons the id's of the courses so you can pick up the checked boxes in PHP by course id. Combine those with the student id and you're done. And start using prepared statements, as you're wide open to SQL injections now. — Michel, Dec 21 '21 at 14:12
Hello, that was the point of part of my question. How could i give the id's to the checkboxes based on the id's on the database... and don't worry about the sql injections, it's just a prototype for a project later on. — neptune17, Dec 21 '21 at 14:14
Well, I suppose you select the courses from the database when displaying the page. Select the id's with them and put them in the name attribute. And you should _start_ using prepared statements right away. There is imho no point in writing code - even a prototype - and later on having to change everything. — Michel, Dec 21 '21 at 14:18
And if the form is hardcoded, you can also hardcode the id's in the name attribute. — Michel, Dec 21 '21 at 14:20
I will look up at the prepared statements. The thing i'm in doubt here is: if i select 2 courses when i register the student, should it create 2 different records on the intermediate table, both differing in the id of the course only? — neptune17, Dec 21 '21 at 14:23
Yes you should. In that case you can select courses by student and students by course. And if a students has multiple courses and drops out of one, you can simply delete the row. — Michel, Dec 21 '21 at 14:25
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Dec 21 '21 at 14:41
I'm working on the prepared statements, wasn't also aware that the addslashes() is a poor choice to prevent the injections. — neptune17, Dec 21 '21 at 14:46

score 0 · Accepted Answer · answered Dec 21 '21 at 14:25

Your code would actually work if you put your IDs into your checkboxes as values

so rather using

 <input class="form-check-input" name="course[]" type="checkbox" value="Piano">

type this instead

 <input class="form-check-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 class="form-group">
    <label class="col-form-label">Select the courses</label>
    <div class="form-check">
        <?php
        foreach ($courses as $course): ?>
            <div class="form-check form-check-inline">
                <input class="form-check-input" name="course[]" type="checkbox" value="<?= $course['cr_id'] ?>">
                <label class="form-check-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.

I was thinking about that but didn't really know how to put my idea into the code, that helps a lot! — neptune17, Dec 21 '21 at 14:33
One last thing, the student code is used for the student to login into his account (working like a password) to see the contents of the classes so I don't really want it to be the identifier. I want the ID to be the real identifier, so i can just leave it as it is (with last insert id) right? — neptune17, Dec 21 '21 at 18:11
@neptune17 ah yeah use the right id then. btw there is function called mysqli_insert_id which returns you the last insert id easier — Vitalij Mik, Dec 21 '21 at 21:59
It worked fine, however it just stores in the database the id of the 1st course i choose on the checkboxes insthead of creating more records... I tried doing a while loop with a variable that I initialized with autoincrement inside the foreach loop and while variable was greater than one, it executed the query. Well it didn't work anyways so i assume the problem is in the foreach loop. I'm sorry for any inconvenience and im glad I have someone to help me out. — neptune17, Dec 22 '21 at 10:35
@neptune17 i need more information, i cant help here out. what does the code looks like now? maybe u misunderstood my answer and interpreted thhere other things. Also try to var_dump your post array to see what kind of values youll get there and try to follow along your code — Vitalij Mik, Dec 22 '21 at 10:55

Deal with many to many database relationships and select/insert multiple checkbox values MYSQL using PHP

1 Answers1