-2

I am studying PHP and we have a project that we got yesterday. I need to build a system that manages a school. I need to create courses and students and I need to be able to assign a student to more than one course. I need to be able to show the list of students for each course and if I choose a student, I need to show all the courses he is listed to. My question is how to plan the DB correctly in order to be able to show all this data. I think I need to use an array of student ID's in each course but I have no idea where and how to start (We did not learn it... like many other things....).

Help will be appreciated.

New content:

I have created 3 tables. 1. students 2. courses 3. coursestudent (Which has: - id - studentid - courseid )

I need help writing a query that will count how many are listed to each course.

This is my code:

<table id="courses" class="table table-striped table-bordered" style="width:100%">
        <thead>
            <tr>
                <th class="text-center">#</th>
                <th class="text-center">שם הקורס</th>
                <th class="text-center">כמות סטודנטים בקורס</th>

            </tr>
        </thead>
        <tbody>
    <?php
    $counter=1;
    $sql = "SELECT * FROM courses";
    $result = mysqli_query($conn, $sql);

    while($row = mysqli_fetch_assoc($result)){ 
        echo '
            <tr class="text-center">
                <td>'. $counter .'</td>
                <td>'. $row['coursename'] .'</td>
                <td>';
                $sqlstudents = "SELECT * FROM coursestudent INNER JOIN students ON coursestudent.studentid=students.studentid WHERE courseid='$counter'";
                $resultstudents = mysqli_query($conn, $sqlstudents);
                $rowcount=mysqli_num_rows($resultstudents);
                echo  $rowcount.'</td><tr>';
            $counter++;  
    }           
        ?>       
        </tbody>
    </table>

It returns 0 for each course.

guy
  • 71
  • 1
  • 7
  • This is not the right place for this kind of question. People expect to see what you tried (your code), in a well formatted question. Help them help you. Please take the [**stack overflow tour**](http://stackoverflow.com/tour) to know more about this site, and read about **[how to ask](https://stackoverflow.com/help/how-to-ask)**. – YvesLeBorg Aug 01 '18 at 10:41
  • StackOverflow is not really meant for questions like this. But I'll try to nudge you in the right direction. What you are looking for is a many-to-many relationship (a student can have multiple courses and a course can have multiple students). To do this you can use a join table. Basically you'd have 1 table with students, 1 table with courses and a third table that links a student id to a course id. – Dirk Scholten Aug 01 '18 at 10:41
  • Read about Normalization in MySQL https://stackoverflow.com/a/1258776/2469308 – Madhur Bhaiya Aug 01 '18 at 10:45

2 Answers2

0

You will need three tables. One to store information about students, one for classes and one junction table to store which student is assigned to which class - basicly many-to-many relation. This answear may be helpful.

Matthew
  • 1
  • 3
0

This is not the right place for this kind of question. As i have some knowledge but still i am giving you the explanation of how to do.

From your question : My question is how to plan the DB correctly in order to be able to show all this data.

This is how you can plan database as per your requirement:

1) You have to create 3 tables mainly one will be students where you are going to 
   enter student information.


2)Another one will be courses table where you have all course information.



3)And another table will be Student_courseslist table where you can assign courses to 
   students and it will show you the  list of students assigned to that particular 
  course.
suresh
  • 439
  • 3
  • 18
  • Thank you. already did it. Right now I am trying to write a correct query to display the number of students listed to each course. – guy Aug 03 '18 at 12:02