Jump to content

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


Olumide

Recommended Posts

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 by Olumide
Link to comment
Share on other sites

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 |
+-----------+--------------------------------------------------------------------------------------+

 

Link to comment
Share on other sites

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.

form1.png

tblcourse.png

tblsubject.png

form_output.png

Link to comment
Share on other sites

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'),

Link to comment
Share on other sites

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');
 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by Olumide
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Olumide
Link to comment
Share on other sites

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');

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

--

Link to comment
Share on other sites

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 by Olumide
Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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 -->

 

Link to comment
Share on other sites

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

image.png.55df2688e8e0baa805450d7e1a5fe221.png

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

image.png.54169b4155db99070bafdc2af81e266e.png                                    image.png.300976d31c93fef921c8b1b843a33c22.png

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.

 

Link to comment
Share on other sites

7 minutes ago, Barand said:

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

image.png.55df2688e8e0baa805450d7e1a5fe221.png

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

image.png.54169b4155db99070bafdc2af81e266e.png                                    image.png.300976d31c93fef921c8b1b843a33c22.png

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.