I am stranded with this sql and php, trying to output subjects for a selected class

[Olumide]

I am trying to compute results for students, and I want to select a class so that it will list all the subjects associated with that class from the database instead of listing all the subjects in all classes, it should only list the selected subjects associated with that class names.

    <tr><!-- below is the code! -->
                                   $levelId=$_POST['levelId'];
        $sessionId=$_POST['semesterId'];
        $departmentId=$_POST['departmentId'];
        $facultyId=$_POST['facultyId'];
       
    
        $ret=mysqli_query($con,"SELECT tblcourse.Id,tblcourse.courseTitle, tbllevel.levelName,tblfaculty.facultyName,tbldepartment.departmentName,tblsemester.semesterName,
        tblcourse.levelId,tblcourse.semesterId,tblcourse.facultyId,tblcourse.departmentId,tblsubject.Subjects
        from tblcourse
        INNER JOIN tbllevel ON tbllevel.Id = tblcourse.levelId
        INNER JOIN tblsubject ON tblsubject.id = tblcourse.courseTitle
        INNER JOIN tblsemester ON tblsemester.Id = tblcourse.semesterId
        INNER JOIN tblfaculty ON tblfaculty.Id = tblcourse.facultyId
        INNER JOIN tbldepartment ON tbldepartment.Id = tblcourse.departmentId
<!-- This is where the problem is, if I should remove the below code for the weher functions, it will output what I need but won't show the exact class, it will only show all the classes registered in my database --->        
where tblcourse.levelId ='$levelId' and tblcourse.semesterId ='$semesterId' 
        and tblcourse.departmentId ='$departmentId' and tblcourse.facultyId ='$facultyId'");
        $cnt=1;
        while ($row=mysqli_fetch_array($ret)) {
                            ?>
        <tr>
        <td><?php echo $cnt;?></td>
       <!-- <td><?php  echo $row['firstName'].' '.$row['lastName'].' '.$row['otherName'];?></td>-->
       <td><?php echo $row['Subjects'];?></td>
       <!-- <td><?php  echo $row['matricNo'];?></td>-->
        <td><?php  echo $row['levelName'];?></td>
        <td><?php  echo $row['facultyName'];?></td>
        <td><?php  echo $row['departmentName'];?></td>
         <td><?php  echo $row['semesterName'];?></td>
        <!--<td><?php  echo $row['dateCreated'];?></td>-->

 

Edited August 24, 2022 by Olumide

[Barand]

We haven't a clue what your schema looks like.

If I were doing this from the schema at the bottom of this page ( http://barringtondrew.co.uk/?page=3 )

I would use this query...

mysql> SELECT classname
    ->      , GROUP_CONCAT(DISTINCT subject ORDER BY subject SEPARATOR ', ') as subjects
    -> FROM class c
    ->      JOIN pupil USING (classid)
    ->      JOIN choice USING (pupilid)
    ->      JOIN subject USING (subjectid)
    -> GROUP BY classname;
+-----------+--------------------------------------------------------------------------------------+
| classname | subjects                                                                             |
+-----------+--------------------------------------------------------------------------------------+
| Class A   | Biology, Computing, Economics, English, Geography, German, History, Maths, Physics   |
| Class B   | Chemistry, Computing, Economics, English, Geography, German, History, Maths, Physics |
| Class C   | Chemistry, Computing, Economics, English, Geography, German, Maths, Physics          |
| Class D   | Biology, Chemistry, Computing, Economics, English, German, History, Maths, Physics   |
| Class E   | Biology, Computing, Economics, English, Geography, German, History, Maths, Physics   |
| Class F   | Biology, Chemistry, Computing, Economics, Geography, German, History, Maths, Physics |
+-----------+--------------------------------------------------------------------------------------+

 

[benanamen]

Aside from your posted question, STOP prefixing tables with tbl. Just stop it.

[Olumide]

Thank you sir for your prompt response. But this is what am trying to do sir, I select a class, lets assume, CLASS A is selected, it will output all the subjects in class A in tabular form like the attached picture but this is not showing only the selected class but it showed all the classes in the database. If a class is selected, it will output the subjects in that selected class and I can click on say English or any subjects such that it will redirect me to another link to computer scores for the students in that selected class offering that subject.

[Barand]

I can't try anything out from a picture. All it's useful for is printing and hanging on a nail in the bathroom. Good luck.

[Olumide]

1 minute ago, Barand said:

I can't try anything out from a picture. All it's useful for is printing and hanging on a nail in the bathroom. Good luck.

oh am sorry, here are my tables:

 

CREATE TABLE `tblcourse` (
  `Id` int(11) NOT NULL,
  `courseTitle` varchar(255) NOT NULL,
  `courseCode` varchar(255) NOT NULL,
  `courseUnit` int(10) NOT NULL,
  `facultyId` varchar(255) NOT NULL,
  `departmentId` varchar(255) NOT NULL,
  `levelId` varchar(10) NOT NULL,
  `semesterId` varchar(20) NOT NULL,
  `dateAdded` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tblcourse`
--

INSERT INTO `tblcourse` (`Id`, `courseTitle`, `courseCode`, `courseUnit`, `facultyId`, `departmentId`, `levelId`, `semesterId`, `dateAdded`) VALUES
(34, '2', 'MAT', 3, '5', '23', '7', '1', '2022-08-22'),
(33, '1', 'ENG', 3, '5', '23', '7', '1', '2022-08-22'),
(32, '16', 'HISA', 3, '9', '7', '3', '1', '2022-08-20'),
(31, '12', 'YORA', 3, '9', '7', '3', '1', '2022-08-20'),
(30, '24', 'PREA', 3, '9', '7', '3', '1', '2022-08-20'),
(29, '23', 'NVEA', 3, '9', '7', '3', '1', '2022-08-20'),
(28, '22', 'CCAA', 3, '9', '7', '3', '1', '2022-08-20'),
(27, '21', 'CRKA', 3, '9', '7', '3', '1', '2022-08-20'),
(26, '20', 'CPSA', 3, '9', '7', '3', '1', '2022-08-20'),
(25, '19', 'BUSA', 3, '9', '7', '3', '1', '2022-08-20'),
(24, '15', 'BSTA', 3, '9', '7', '3', '1', '2022-08-20'),
(23, '13', 'FRCA', 3, '9', '7', '3', '1', '2022-08-20'),
(22, '2', 'MATA', 3, '9', '7', '3', '1', '2022-08-20'),
(21, '1', 'ENGA', 3, '9', '7', '3', '1', '2022-08-20');

-- --------------------------------------------------------

--
-- Table structure for table `tbldepartment`
--

CREATE TABLE `tbldepartment` (
  `Id` int(20) NOT NULL,
  `departmentName` varchar(255) NOT NULL,
  `facultyId` int(20) NOT NULL,
  `dateCreated` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tbldepartment`
--

INSERT INTO `tbldepartment` (`Id`, `departmentName`, `facultyId`, `dateCreated`) VALUES
(1, 'IT Dept', 1, '2022-06-13'),
(2, 'Accounting', 2, '2022-06-15'),
(3, 'Law Science', 3, '2022-06-15'),
(4, 'Industrial Engineering', 4, '2022-06-15'),
(5, 'Electrical Engineering', 4, '2022-06-15'),
(6, 'Law Science', 2, '2022-08-20'),
(7, 'Year 7', 9, '2022-08-20'),
(8, 'Year 8', 9, '2022-08-20'),
(9, 'Year 9', 9, '2022-08-20'),
(10, 'Year 7', 10, '2022-08-20'),
(11, 'Year 8', 10, '2022-08-20'),
(12, 'Year 9', 10, '2022-08-20'),
(13, 'Year 7', 8, '2022-08-20'),
(14, 'Year 8', 8, '2022-08-20'),
(15, 'Year 9', 8, '2022-08-20'),
(16, 'Year 10', 7, '2022-08-20'),
(17, 'Year 11', 7, '2022-08-20'),
(18, 'Year 12', 7, '2022-08-20'),
(19, 'Year 10', 6, '2022-08-20'),
(20, 'Year 11', 6, '2022-08-20'),
(21, 'Year 12', 6, '2022-08-20'),
(22, 'Year 10', 5, '2022-08-20'),
(23, 'Year 11', 5, '2022-08-20'),
(24, 'Year 12', 5, '2022-08-20');

-- --------------------------------------------------------

--
-- Table structure for table `tblfaculty`
--

CREATE TABLE `tblfaculty` (
  `Id` int(20) NOT NULL,
  `facultyName` varchar(255) NOT NULL,
  `dateCreated` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tblfaculty`
--

INSERT INTO `tblfaculty` (`Id`, `facultyName`, `dateCreated`) VALUES
(1, 'Faculty 1', '2022-06-13'),
(2, 'Faculty 2', '2022-06-15'),
(3, 'Faculty 3', '2022-06-15'),
(4, 'Faculty 4', '2022-06-15'),
(5, 'Science', '2022-08-20'),
(6, 'Arts', '2022-08-20'),
(7, 'Commercial', '2022-08-20'),
(8, 'Magnificient', '2022-08-20'),
(9, 'Adventurous', '2022-08-20'),
(10, 'Blossom', '2022-08-20');

-- --------------------------------------------------------

CREATE TABLE `tblsemester` (
  `Id` int(20) NOT NULL,
  `semesterName` varchar(100) NOT NULL,
  `isActive` int(5) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tblsemester`
--

INSERT INTO `tblsemester` (`Id`, `semesterName`, `isActive`) VALUES
(1, 'First Term', 1),
(2, 'Second Term', 0),
(3, 'Third Term', 0);
 

CREATE TABLE `tblsession` (
  `Id` int(20) NOT NULL,
  `sessionName` varchar(30) NOT NULL,
  `isActive` int(5) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tblsession`
--

INSERT INTO `tblsession` (`Id`, `sessionName`, `isActive`) VALUES
(1, '2022/2023', 1),
(2, '2020/2021', 0);

 

 

CREATE TABLE `tblsubject` (
  `id` int(100) NOT NULL,
  `Subjects` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tblsubject`
--

INSERT INTO `tblsubject` (`id`, `Subjects`) VALUES
(1, 'English Language'),
(2, 'Mathematics'),
(3, 'Biology'),
(4, 'Physics'),
(5, 'Chemistry'),
(6, 'Geography'),
(7, 'Further Mathematics'),
(8, 'Agricultural Science'),
(9, 'Economics'),
(10, 'Home Economics'),
(11, 'Technical Drawing'),
(12, 'Yoruba'),
(13, 'French'),
(14, 'Christian Religious Studies'),
(15, 'Basic Science and Technology'),
(16, 'History'),
(17, 'Government'),
(18, 'Civic Education'),
(19, 'Business Studies'),

[benanamen]

You are mixing Database Engines. (MyISAM, InnoDB)

Just use InnoDB and add foreign keys while you are at it.

[Olumide]

1 minute ago, benanamen said:

You are mixing Database Engines. (MyISAM, InnoDB)

Just use InnoDB and add foreign keys while you are at it.

Okay. Thank you, but can that be the issue? Please

[Barand]

Tbllevel missing

[Olumide]

2 minutes ago, Barand said:

Tbllevel missing

Am sorry sir for the omission, here is it:

 

CREATE TABLE `tbllevel` (
  `Id` int(20) NOT NULL,
  `levelName` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tbllevel`
--

INSERT INTO `tbllevel` (`Id`, `levelName`) VALUES
(1, 'Level One'),
(2, 'Level Two'),
(3, 'Year 7'),
(4, 'Year 8'),
(5, 'Year 9'),
(6, 'Year 10'),
(7, 'Year 11'),
(8, 'Year 12');
 

[Barand]

1 hour ago, Olumide said:

I select a class, lets assume, CLASS A is selected, it will output all the subjects in class A in tabular form like the attached picture but this is not showing only the selected class but it showed all the classes in the database. If a class is selected, it will output the subjects in that selected class and I can click on say English or any subjects such that it will redirect me to another link to computer scores for the students in that selected class offering that subject

Your question is all about classes. I cannot find a reference to a class entity anywhere in your tables. (I was hoping it might be in that missing table, but no)

So what, in your database, constitutes a class?

[Olumide]

8 minutes ago, Barand said:

Your question is all about classes. I cannot find a reference to a class entity anywhere in your tables. (I was hoping it might be in that missing table, but no)

So what, in your database, constitutes a class?

I named my class as tbllevel. Am sorry for the confusion. I classified my class as level Sir. 

Edited August 24, 2022 by Olumide

[Barand]

I ran your query, providing some values in the WHERE clause

SELECT tblcourse.Id, tblcourse.courseTitle, tbllevel.levelName,
       tblfaculty.facultyName, tbldepartment.departmentName,
       tblsemester.semesterName, tblcourse.levelId, tblcourse.semesterId,
       tblcourse.facultyId, tblcourse.departmentId, tblsubject.Subjects
        from tblcourse
        INNER JOIN tbllevel ON tbllevel.Id = tblcourse.levelId
        INNER JOIN tblsubject ON tblsubject.id = tblcourse.courseTitle
        INNER JOIN tblsemester ON tblsemester.Id = tblcourse.semesterId
        INNER JOIN tblfaculty ON tblfaculty.Id = tblcourse.facultyId
        INNER JOIN tbldepartment ON tbldepartment.Id = tblcourse.departmentId
where tblcourse.levelId ='3' and tblcourse.semesterId ='1' 
        and tblcourse.departmentId ='7' and tblcourse.facultyId ='9'; 

The output contains a single "class", ie Year 7...

+----+-------------+-----------+-------------+----------------+--------------+---------+------------+-----------+--------------+------------------------------+
| Id | courseTitle | levelName | facultyName | departmentName | semesterName | levelId | semesterId | facultyId | departmentId | Subjects                     |
+----+-------------+-----------+-------------+----------------+--------------+---------+------------+-----------+--------------+------------------------------+
| 21 | 1           | Year 7    | Adventurous | Year 7         | First Term   | 3       | 1          | 9         | 7            | English Language             |
| 22 | 2           | Year 7    | Adventurous | Year 7         | First Term   | 3       | 1          | 9         | 7            | Mathematics                  |
| 31 | 12          | Year 7    | Adventurous | Year 7         | First Term   | 3       | 1          | 9         | 7            | Yoruba                       |
| 23 | 13          | Year 7    | Adventurous | Year 7         | First Term   | 3       | 1          | 9         | 7            | French                       |
| 24 | 15          | Year 7    | Adventurous | Year 7         | First Term   | 3       | 1          | 9         | 7            | Basic Science and Technology |
| 32 | 16          | Year 7    | Adventurous | Year 7         | First Term   | 3       | 1          | 9         | 7            | History                      |
| 25 | 19          | Year 7    | Adventurous | Year 7         | First Term   | 3       | 1          | 9         | 7            | Business Studies             |
+----+-------------+-----------+-------------+----------------+--------------+---------+------------+-----------+--------------+------------------------------+

Can you explain better just what the problem is that you are having? You gave the impression that all classes were being output.

[Olumide]

12 minutes ago, Barand said:

I ran your query, providing some values in the WHERE clause

SELECT tblcourse.Id, tblcourse.courseTitle, tbllevel.levelName,
       tblfaculty.facultyName, tbldepartment.departmentName,
       tblsemester.semesterName, tblcourse.levelId, tblcourse.semesterId,
       tblcourse.facultyId, tblcourse.departmentId, tblsubject.Subjects
        from tblcourse
        INNER JOIN tbllevel ON tbllevel.Id = tblcourse.levelId
        INNER JOIN tblsubject ON tblsubject.id = tblcourse.courseTitle
        INNER JOIN tblsemester ON tblsemester.Id = tblcourse.semesterId
        INNER JOIN tblfaculty ON tblfaculty.Id = tblcourse.facultyId
        INNER JOIN tbldepartment ON tbldepartment.Id = tblcourse.departmentId
where tblcourse.levelId ='3' and tblcourse.semesterId ='1' 
        and tblcourse.departmentId ='7' and tblcourse.facultyId ='9'; 

The output contains a single "class", ie Year 7...

+----+-------------+-----------+-------------+----------------+--------------+---------+------------+-----------+--------------+------------------------------+
| Id | courseTitle | levelName | facultyName | departmentName | semesterName | levelId | semesterId | facultyId | departmentId | Subjects                     |
+----+-------------+-----------+-------------+----------------+--------------+---------+------------+-----------+--------------+------------------------------+
| 21 | 1           | Year 7    | Adventurous | Year 7         | First Term   | 3       | 1          | 9         | 7            | English Language             |
| 22 | 2           | Year 7    | Adventurous | Year 7         | First Term   | 3       | 1          | 9         | 7            | Mathematics                  |
| 31 | 12          | Year 7    | Adventurous | Year 7         | First Term   | 3       | 1          | 9         | 7            | Yoruba                       |
| 23 | 13          | Year 7    | Adventurous | Year 7         | First Term   | 3       | 1          | 9         | 7            | French                       |
| 24 | 15          | Year 7    | Adventurous | Year 7         | First Term   | 3       | 1          | 9         | 7            | Basic Science and Technology |
| 32 | 16          | Year 7    | Adventurous | Year 7         | First Term   | 3       | 1          | 9         | 7            | History                      |
| 25 | 19          | Year 7    | Adventurous | Year 7         | First Term   | 3       | 1          | 9         | 7            | Business Studies             |
+----+-------------+-----------+-------------+----------------+--------------+---------+------------+-----------+--------------+------------------------------+

Can you explain better just what the problem is that you are having? You gave the impression that all classes were being output.

Okay Sir. This is my problem Sir, what am trying to do is electronic result and I used a secondary school as a case study in my country here. I want to designed it in such a way that each teachers will able to login to computer results from any students taking that subject. Let assume, am teaching Mathematics for Class A, if I login, I will select class A, and it will show the list of subjects class A are offering, so from the view course, I can click the one that relate to my subject which is Mathematics, this will now open to generate the list of students in class A with a text box to enter scores for each students for the subject Mathematics. I am sorry for my poor English. 

Edited August 24, 2022 by Olumide

[Barand]

Thus far we have a list of the subjects for Year 7. Mathematics is in the list so we can click on that.

What is missing now is table of students, and which subjects the students are taking, so we can find the ones taking maths

[Olumide]

5 minutes ago, Barand said:

Thus far we have a list of the subjects for Year 7. Mathematics is in the list so we can click on that.

What is missing now is table of students, and which subjects the students are taking, so we can find the ones taking maths

Thanks sir for your effort and am sorry for the late reply, we don't have light here.

Here is the table for students and all the students are taking Mathematics

 

-- Table structure for table `tblstudent`
--

CREATE TABLE `tblstudent` (
  `Id` int(20) NOT NULL,
  `firstName` varchar(255) NOT NULL,
  `lastName` varchar(255) NOT NULL,
  `otherName` varchar(255) NOT NULL,
  `matricNo` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `levelId` int(10) NOT NULL,
  `DOB` varchar(100) CHARACTER SET latin1 NOT NULL,
  `Phone` varchar(100) CHARACTER SET latin1 NOT NULL,
  `Email` varchar(100) CHARACTER SET latin1 NOT NULL,
  `facultyId` int(10) NOT NULL,
  `departmentId` int(10) NOT NULL,
  `sessionId` int(10) NOT NULL,
  `image` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `dateCreated` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tblstudent`
--

INSERT INTO `tblstudent` (`Id`, `firstName`, `lastName`, `otherName`, `matricNo`, `password`, `levelId`, `DOB`, `Phone`, `Email`, `facultyId`, `departmentId`, `sessionId`, `image`, `dateCreated`) VALUES
(35, 'Ojo', 'SHEBA', 'John', 'MAB/2018/20057', '123456', 7, '2008-04-09', '081400009', 'ani@yahoo.com', 5, 23, 1, 'f3e9961302568e44ef60c009e852ec921661074548.jpg', '2022-08-21'),
(36, 'Dada', 'Idowu', 'ELIAS', 'MAB/2018/14229', '123456', 7, '2008-09-12', '08034757757', '080@gmail.com', 5, 23, 1, '21f24bd277df7b56c438acd26664115e1661075094.jpg', '2022-08-21'),
(37, 'Shade', 'Peter', 'OLUWAFEMI', 'MAB/2018/14299', '123456', 7, '2008-07-18', '0803444444', 'sample@yahoo.com', 5, 23, 1, 'f41ca8eb5f82744baf9212714bfbfba61661075244.jpg', '2022-08-21'),
(38, 'Desola', 'Pelumi', '', 'MAB/2018/18984', '123456', 7, '2010-05-18', '08059430000', '080as0000@gmail.com', 5, 23, 1, '838792531cf234c19ffc81ed8b7f0e781661075367.jpg', '2022-08-21'),
(39, 'Aina', 'ADESUYI', 'Sowu', 'MAB/2018/13878', 'codeastro', 7, '2007-10-30', '08065555550', 'emia@yahoo.com', 5, 23, 1, '3362a7e6c8d9118e4a38b6009b340dc21661075555.jpg', '2022-08-21'),
(40, 'Baba', 'Eniola', 'MICHAEL', 'MAB/2018/99483', '123456', 7, '2008-12-01', '08155555559', 'example@gmail.com', 5, 23, 1, 'b939949d3f5afd782198760a9fd871791661075700.jpg', '2022-08-21'),
(41, 'Iyami', 'Mama', 'EBRUPHIHO', 'MAB/2018/16916', '123456', 7, '2007-07-24', '0808888826', 'oy@yahoo.com', 5, 23, 1, '97c02bbe49220ae56f2eb75a5432a2ef1661075870.jpg', '2022-08-21');

-- --------------------------------------------------------

--

[Barand]

There should be something that lets you know which students are taking each subject

[Olumide]

15 minutes ago, Barand said:

Sir, for instance now, class/level Year 7, they are taking the same subjects combination, and same thing applies to Year 8 and 9. Except Year 10, 11 and 12 which we have Science, Arts and Commercial. Science subjects are different from Arts and from Commercial. Though English and Maths are compulsory for all. If I could get something like, if a class or level is selected, It listed the mapped subjects with that class so that I can easily compute result for them. But you are a genus while am a LEARNER and a poor novice. 

 

Edited August 24, 2022 by Olumide

[Barand]

It seems mathematics and Year 7 are a poor example if everyone in Year 7 takes mathematics.

In Years 10/11/12 where some students take Science, some Arts and some Commercial, how do you then know who takes which subject?

(I may know my way around databases but I do not know the relationships between all the entities in your specific scenario.)

[Olumide]

1 minute ago, Barand said:

It seems mathematics and Year 7 are a poor example if everyone in Year 7 takes mathematics.

In Years 10/11/12 where some students take Science, some Arts and some Commercial, how do you then know who takes which subject?

(I may know my way around databases but I do not know the relationships between all the entities in your specific scenario.)

Well sir, I have only added English and Mathematics for Year 11, under tblcourse. Though I have all the subjects in table tblsubjects, and what I planned to do is to add the subjects to each class/level as per their requirement and to know who takes which subject, from the form, I select level as Year 11, and I select faculty as science, I assumed I should fetch the list of sciences subjects registered under Year 11 Science. Am sorry sir, I can't post code cos am on phone as my computer has shutdown due to power failure. Am a Nigerian, so we don't have dependable light and a Computer science student trying to learn. 

[Barand]

58 minutes ago, Olumide said:

Am a Nigerian, so we don't have dependable light

I'm pretty sure that it will be like that in Europe soon thanks to Vlad the Invader

[Olumide]

8 hours ago, Barand said:

I'm pretty sure that it will be like that in Europe soon thanks to Vlad the Invader

Good morning sir, as it is early morning here according to our GMT +1, and I appreciate your effort in helping me to get my long time headache resolved. As I said yesterday, I don't know much as am still learning and am always eager to learn more cos I love programming and want to be a good programmer. I am pretty sure, I still have many errors in my code but at least if am able to get it solved one by one. Below is my code for the class list, such that if I select say Year 11 Science, and I click on view, it should output the list of subjects associated with only Year 11 Science.

<form method="Post" action="">
                                            
                                            <div class="row">
                                                <div class="col-6">
                                                    <div class="form-group">
                                                      <label for="x_card_code" class="control-label mb-1">Level</label>
                                                    <?php 
                                                $query=mysqli_query($con,"select * from tbllevel");                        
                                                $count = mysqli_num_rows($query);
                                                if($count > 0){                       
                                                    echo ' <select required name="levelId" class="custom-select form-control">';
                                                    echo'<option value="">--Select Level--</option>';
                                                    while ($row = mysqli_fetch_array($query)) {
                                                    echo'<option value="'.$row['Id'].'" >'.$row['levelName'].'</option>';
                                                        }
                                                            echo '</select>';
                                                        }
                                                ?>   
                                                    </div>
                                                </div>
                                            <div class="col-6">
                                                <div class="form-group">
                                                     <label for="x_card_code" class="control-label mb-1">Semester</label>
                                                    <?php 
                                                    $query=mysqli_query($con,"select * from tblsemester where isActive = 1");                        
                                                    $count = mysqli_num_rows($query);
                                                    if($count > 0){                       
                                                        echo ' <select required name="semesterId" class="custom-select form-control">';
                                                        echo'<option value="">--Select Semester--</option>';
                                                        while ($row = mysqli_fetch_array($query)) {
                                                        echo'<option value="'.$row['Id'].'" >'.$row['semesterName'].'</option>';
                                                            }
                                                                echo '</select>';
                                                            }
                                                ?>   
                                                </div>
                                            </div>
                                        </div>
                                         <div class="row">
                                                <div class="col-6">
                                                    <div class="form-group">
                                                    <label for="x_card_code" class="control-label mb-1">Faculty</label>
                                                    <?php 
                                                    $query=mysqli_query($con,"select * from tblfaculty ORDER BY facultyName ASC");                        
                                                    $count = mysqli_num_rows($query);
                                                    if($count > 0){                       
                                                        echo ' <select required name="facultyId" onchange="showValues(this.value)" class="custom-select form-control">';
                                                        echo'<option value="">--Select Faculty--</option>';
                                                        while ($row = mysqli_fetch_array($query)) {
                                                        echo'<option value="'.$row['Id'].'" >'.$row['facultyName'].'</option>';
                                                            }
                                                                echo '</select>';
                                                            }
                                                    ?>                                                     
                                                 </div>
                                                </div>
                                                 <div class="col-6">
                                                    <div class="form-group">
                                                   <?php
                                                        echo"<div id='txtHint'></div>";
                                                    ?>                                    
                                                 </div>
                                                </div>
                                             </div>
                                                <div>
                                                <!-- Log on to codeastro.com for more projects! -->
                                                <button type="submit" name="submit" class="btn btn-success">View Subject</button>
                                            </div>
                                        </form>
                                    </div>
                                </div>
                            </div>
                        </div> <!-- .card -->
                    </div><!--/.col-->
               

                <br><br>

                    <div class="col-md-12">
                        <div class="card">
                            <div class="card-header">
                                <strong class="card-title"><h3 align="center">All Subjects</h3></strong>
                            </div>
                            <div class="card-body">
                                <table id="bootstrap-data-table" class="table table-hover table-striped table-bordered">
                                     <thead>
                                        <tr><!-- Log on to codeastro.com for more projects! -->
                                            <th>#</th>
                                            <th>Subject Name</th>
                                           <!-- <th>MatricNo</th>-->
                                            <th>Level</th>
                                            <th>Faculty</th>
                                            <th>Department</th>
                                            <th>Session</th>
                                           <!-- <th>Date Added</th>-->
                                           <th>First Semester</th>
                                            <th>Second Semester</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                      
                            <?php
                if(isset($_POST['submit']))
                {

                    $levelId=$_POST['levelId'];
                    $sessionId=$_POST['semesterId'];
                    $departmentId=$_POST['departmentId'];
                    $facultyId=$_POST['facultyId'];
                   

                    $ret=mysqli_query($con,"SELECT tblcourse.Id,tblcourse.courseTitle, tbllevel.levelName,tblfaculty.facultyName,tbldepartment.departmentName,tblsemester.semesterName,
                    tblcourse.levelId,tblcourse.semesterId,tblcourse.facultyId,tblcourse.departmentId,tblsubject.Subjects
                    from tblcourse
                    INNER JOIN tbllevel ON tbllevel.Id = tblcourse.levelId
                    INNER JOIN tblsubject ON tblsubject.id = tblcourse.courseTitle
                    INNER JOIN tblsemester ON tblsemester.Id = tblcourse.semesterId
                    INNER JOIN tblfaculty ON tblfaculty.Id = tblcourse.facultyId
                    INNER JOIN tbldepartment ON tbldepartment.Id = tblcourse.departmentId
                    where tblcourse.levelId ='$levelId' and tblcourse.semesterId ='$semesterId' 
                    and tblcourse.departmentId ='$departmentId' and tblcourse.facultyId ='$facultyId'");
                    $cnt=1;
                    while ($row=mysqli_fetch_array($ret)) {
                                        ?>
                    <tr>
                    <td><?php echo $cnt;?></td>
                   <!-- <td><?php  echo $row['firstName'].' '.$row['lastName'].' '.$row['otherName'];?></td>-->
                   <td><?php echo $row['Subjects'];?></td>
                   <!-- <td><?php  echo $row['matricNo'];?></td>-->
                    <td><?php  echo $row['levelName'];?></td>
                    <td><?php  echo $row['facultyName'];?></td>
                    <td><?php  echo $row['departmentName'];?></td>
                     <td><?php  echo $row['semesterName'];?></td>
                    <!--<td><?php  echo $row['dateCreated'];?></td>-->
                    <td><a href="courseList.php?semesterId=1&levelName=<?php echo $row['levelName'];?>&levelId=<?php echo $row['levelId'];?>&facultyId=<?php echo $row['facultyId'];?>&departmentId=<?php echo $row['departmentId'];?>&semesterId=<?php echo $row['semesterId'];?>" title="Edit Details"><i class="fa fa-eye fa-1x"></i> View Course</a></td>
                    <td><a href="courseList.php?semesterId=2&levelName=<?php echo $row['levelName'];?>&levelId=<?php echo $row['levelId'];?>&facultyId=<?php echo $row['facultyId'];?>&departmentId=<?php echo $row['departmentId'];?>&semesterId=<?php echo $row['semesterId'];?>" title="Edit Details"><i class="fa fa-eye fa-1x"></i> View Course</a></td>
                    </tr>
                    <?php 
                    $cnt=$cnt+1;
                    }
                }?>
                                                                               
                                </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
<!-- end of datatable -->

 

[Barand]

I have been looking at your data model - not a pretty sight

Foreign keys in a table should match the primary keys of the referenced table. Your primary keys are INT but the foreign keys in the course table are all VARCHAR - they too should be INT. And why is the FK of tblsubject called "courseTitle"? (I couldn't see any relationship between course and subject until I saw the join your query)

Date fields should be type DATE (yyyy-mm-dd). (Already changed in my version of tblstudent)

Departments belong to a Faculty, so if you know the department you know the faculty. There is no need for "facultyid" in tblcourse or tblstudent.

tblsession is only referenced by tblstudent. I would have thought there is a relationship between session (eg 2021/2022) and semester.

Other than via the class/level there is, surprisingly, no relationship between student and course which, to me, seems the main relationship in an educational environment.

Do your courses only last for a single semester?

Are course and course unit part of the same entity or does a course have many units?

YOUR CODE

                                   

I haven't got it to run yet as changes are required. Your form has Level/Semester/Faculty put your post is expected to contain Level/Semester/Department/Faculty.

For reasons known only to you, the semesterId is assigned to $sessionId.

The form input should be Department instead of Faculty as faculty is redundant if you have the department. The query will change accordingly.

As you only want to display data, POST should be GET.

 

[Olumide]

7 minutes ago, Barand said:

I have been looking at your data model - not a pretty sight

Foreign keys in a table should match the primary keys of the referenced table. Your primary keys are INT but the foreign keys in the course table are all VARCHAR - they too should be INT. And why is the FK of tblsubject called "courseTitle"? (I couldn't see any relationship between course and subject until I saw the join your query)

Date fields should be type DATE (yyyy-mm-dd). (Already changed in my version of tblstudent)

Departments belong to a Faculty, so if you know the department you know the faculty. There is no need for "facultyid" in tblcourse or tblstudent.

tblsession is only referenced by tblstudent. I would have thought there is a relationship between session (eg 2021/2022) and semester.

Other than via the class/level there is, surprisingly, no relationship between student and course which, to me, seems the main relationship in an educational environment.

Do your courses only last for a single semester?

Are course and course unit part of the same entity or does a course have many units?

YOUR CODE

                                   

I haven't got it to run yet as changes are required. Your form has Level/Semester/Faculty put your post is expected to contain Level/Semester/Department/Faculty.

For reasons known only to you, the semesterId is assigned to $sessionId.

The form input should be Department instead of Faculty as faculty is redundant if you have the department. The query will change accordingly.

As you only want to display data, POST should be GET.

 

Thank you sir for the great analysis. As I have said earlier, a novice will always be a novice until I become sound like you. I planned to remove the course unit as it is not needed for my project cos this is just for an upper elementary which require no course unit nor even faculty or department. I want to display data for a specified class to list the registered subjects such that if I click on a subject say English, it will take me to another form where I will be able to enter scores for each students in that selected class for the allotted subject 'English'. Please I will post the initial code for the tertiary in which I was trying to worked to change to the upper elementary.

[Barand]

A model along these lines would give greater flexibility