I have a project on student ranking. The sample school does three terminal exam in a year. I created a table where i stored all student test and exam according to their RegNo, year(session), semester, level and student arm(i.e A,B,C,D…) and all works perfectly.
The problem i’m facing now is the student overall position and subject position. For student subject position, I created a table called subject position where I want to have the position of students based on subject. And for the overall position I created a table that will automatically sum all the student result termly and rank them.
This is my table structure:
DROP TABLE IF EXISTS `subject_position`;
CREATE TABLE IF NOT EXISTS `subject_position` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`regNo` varchar(50) NOT NULL,
`subjectid` varchar(50) NOT NULL,
`armsLevelId` varchar(50) NOT NULL,
`armsId` varchar(50) NOT NULL,
`semesterid` varchar(11) NOT NULL,
`yearid` varchar(50) NOT NULL,
`total` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=73 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `subject_position`
--
INSERT INTO `subject_position` (`id`, `regNo`, `subjectid`, `armsLevelId`, `armsId`, `semesterid`, `yearid`, `total`) VALUES
(1, '4663', '1', '1', '1', '1', '1', '72'),
(2, '6073', '1', '1', '1', '1', '1', '73'),
(3, '4663', '2', '1', '1', '1', '1', '47'),
(4, '6073', '2', '1', '1', '1', '1', '61'),
(5, '4663', '3', '1', '1', '1', '1', '82'),
(6, '6073', '3', '1', '1', '1', '1', '61'),
(7, '4663', '4', '1', '1', '1', '1', '99'),
(8, '6073', '4', '1', '1', '1', '1', '95'),
(9, '4663', '5', '1', '1', '1', '1', '70'),
(10, '6073', '5', '1', '1', '1', '1', '100'),
(11, '4663', '6', '1', '1', '1', '1', '69'),
(12, '6073', '6', '1', '1', '1', '1', '67'),
(13, '4663', '7', '1', '1', '1', '1', '77'),
(14, '6073', '7', '1', '1', '1', '1', '80'),
(15, '4663', '8', '1', '1', '1', '1', '58'),
(16, '6073', '8', '1', '1', '1', '1', '77'),
(17, '4663', '9', '1', '1', '1', '1', '96'),
(18, '6073', '9', '1', '1', '1', '1', '96'),
(19, '4663', '10', '1', '1', '1', '1', '78'),
(20, '6073', '10', '1', '1', '1', '1', '77'),
(21, '4663', '11', '1', '1', '1', '1', '48'),
(22, '6073', '11', '1', '1', '1', '1', '88'),
(23, '4663', '12', '1', '1', '1', '1', '69'),
(24, '6073', '12', '1', '1', '1', '1', '94'),
(25, '4663', '1', '1', '1', '2', '1', '28'),
(26, '6073', '1', '1', '1', '2', '1', '70'),
(27, '4663', '2', '1', '1', '2', '1', '68'),
(28, '6073', '2', '1', '1', '2', '1', '59'),
(29, '4663', '3', '1', '1', '2', '1', '68'),
(30, '6073', '3', '1', '1', '2', '1', '70'),
(31, '4663', '4', '1', '1', '2', '1', '81'),
(32, '6073', '4', '1', '1', '2', '1', '72'),
(33, '4663', '5', '1', '1', '2', '1', '84'),
(34, '6073', '5', '1', '1', '2', '1', '72'),
(35, '4663', '6', '1', '1', '2', '1', '58'),
(36, '6073', '6', '1', '1', '2', '1', '72'),
(37, '4663', '7', '1', '1', '2', '1', '71'),
(38, '6073', '7', '1', '1', '2', '1', '70'),
(39, '4663', '8', '1', '1', '2', '1', '48'),
(40, '6073', '8', '1', '1', '2', '1', '55'),
(41, '4663', '9', '1', '1', '2', '1', '66'),
(42, '6073', '9', '1', '1', '2', '1', '51'),
(43, '4663', '10', '1', '1', '2', '1', '37'),
(44, '6073', '10', '1', '1', '2', '1', '58'),
(45, '4663', '11', '1', '1', '2', '1', '57'),
(46, '6073', '11', '1', '1', '2', '1', '59'),
(47, '4663', '12', '1', '1', '2', '1', '67'),
(48, '6073', '12', '1', '1', '2', '1', '69'),
(49, '4663', '1', '1', '1', '3', '1', '94'),
(50, '6073', '1', '1', '1', '3', '1', '82'),
(51, '4663', '2', '1', '1', '3', '1', '69'),
(52, '6073', '2', '1', '1', '3', '1', '76'),
(53, '4663', '3', '1', '1', '3', '1', '63'),
(54, '6073', '3', '1', '1', '3', '1', '81'),
(55, '4663', '4', '1', '1', '3', '1', '81'),
(56, '6073', '4', '1', '1', '3', '1', '77'),
(57, '4663', '5', '1', '1', '3', '1', '72'),
(58, '6073', '5', '1', '1', '3', '1', '83'),
(59, '4663', '6', '1', '1', '3', '1', '78'),
(60, '6073', '6', '1', '1', '3', '1', '83'),
(61, '4663', '7', '1', '1', '3', '1', '77'),
(62, '6073', '7', '1', '1', '3', '1', '75'),
(63, '4663', '8', '1', '1', '3', '1', '74'),
(64, '6073', '8', '1', '1', '3', '1', '82'),
(65, '4663', '9', '1', '1', '3', '1', '56'),
(66, '6073', '9', '1', '1', '3', '1', '95'),
(67, '4663', '10', '1', '1', '3', '1', '87'),
(68, '6073', '10', '1', '1', '3', '1', '79'),
(69, '4663', '11', '1', '1', '3', '1', '70'),
(70, '6073', '11', '1', '1', '3', '1', '71'),
(71, '4663', '12', '1', '1', '3', '1', '82'),
(72, '6073', '12', '1', '1', '3', '1', '90');
COMMIT;
Table name: dummy (This table stores all students scores depending of RegNo, year(session), semester, level and student arm(i.e A,B,C,D…) respectively)
id regNo subjectid armsLevelId armsId semesterid yearid firstCA seconCA exam total grade comment
1 4663 1 1 1 1 1 9 8 55 72 B2 V.Good
2 6073 1 1 1 1 1 10 8 55 73 B2 V.Good
3 4663 2 1 1 1 1 6 8 33 47 D7 Pass
4 6073 2 1 1 1 1 11 6 44 61 C4 Credit
Table name: subject_position (This table stores all students total scores termly depending of RegNo, year(session), semester, level and student arm(i.e A,B,C,D…) respectively)
id regNo subjectid armsLevelId armsId semesterid yearid total
1 4663 1 1 1 1 1 72
2 6073 1 1 1 1 1 73
3 4663 2 1 1 1 1 47
4 6073 2 1 1 1 1 61
.. .... . . . . . ..
.. .... . . . . . ..
.. .... . . . . . ..
71 4663 12 1 1 3 1 82
72 6073 12 1 1 3 1 90
Expected Output: for subject_position irrespective of the subject list
Note: Number of student here is two. we can have more than two(2) students
id regNo subjectid armsLevelId armsId semesterid yearid total SubjectPos
1 4663 1 1 1 1 1 72 2
2 6073 1 1 1 1 1 73 1
3 4663 2 1 1 1 1 47 2
4 6073 2 1 1 1 1 61 1
.. .... . . . . . .. ..
.. .... . . . . . .. ..
.. .... . . . . . .. ..
71 4663 12 1 1 3 1 82 2
72 6073 12 1 1 3 1 90 1
My query
/Where subject id $sub is coming from/
$sql_subject = "select * from dummy where regid='$_GET[name]' and armsLevelId='$_SESSION[level_id]' and armsId='$_SESSION[arms]' and yearid='$_SESSION[session]' group by subjectid";
$result = mysqli_query($con,$sql_subject);
$datas_subject = array();
while ($row_query=mysqli_fetch_array($result)){
$RegNo=$row_query["regid"];
$subject_id=$row_query["subjectid"];
$level_id=$row_query["armsLevelId"];
$arms=$row_query["armsId"];
$semester=$row_query["semesterid"];
$session=$row_query["yearid"];
$fca=$row_query["firstCA"];
$sca=$row_query["secondCA"];
$exam=$row_query["exam"];
$total =$row_query["total"];
$grade =$row_query["grade"];
$comment =$row_query["comment"];
$query_sub=mysqli_query($con,"select * from subject where id='".$subject_id."'");
while ($row_sub=mysqli_fetch_array($query_sub)){
$r_sub = $row_sub["subject_name"];
}
$datas_subject[] = $subject_id;
}
for ($i=0; $i<count($datas_subject); $i++) {
if(!empty($datas_subject[$i])){
$sub = $datas_subject[$i];
echo "Subject ID: ".$sub ."n"; // this printed all the list of subject ids
}else{
//echo "Error Occured";
}
}
$sql = "SELECT * FROM `subject_position` where regNo='$RegNo' and subjectid='$sub' and armsLevelId='$level_id' and armsId='$arms' and semesterid='$_SESSION[semester]' and yearid='$session' ORDER BY `total` DESC";
echo $sql . "n"; // This printed SELECT * FROM `subject_position` where regNo='4663' and subjectid='12' and armsLevelId='1' and armsId='1' and semesterid='1' and yearid='1' ORDER BY `total` DESC
//without looping through the subject Ids
$result = mysqli_query($con,$sql);
if( !$result ){
echo 'SQL Query Failed';
}else{
$rank = 0;
$last_score = false;
$rows = 0;
while( $row = mysqli_fetch_array( $result ) ){
$rows++;
if( $last_score!= $row['total'] ){
$last_score = $row['total'];
$rank = $rows;
}
echo "rank ".$rank." is ".$row['regNo']." with point ".$row['total'] . "n";
}