I am a novice and need help. I am working on a project regards school online result system. I don’t know how to do the position for each student in each term per subjects.
The images above is my output, the position column should be like 1st, 2nd, 3rd, 4th till the last rank. For instance, if there are 20 students in a class, the person that score the lowest mark will have the lowest position in that subject scores.
My database comprises of result table, semester table, student table, class table, subject table, student_class table.
switch($termno) {
case 1: $term_headings = "<th>1st<br>Term<br>100</th>
<!--<th>     </th>
<th>     </th>-->";
$res = $pdo->prepare("SELECT subjectname
, stname
, score
, @seq := IF(subjectname = @prevs, @seq+1, 1) as seq
, @rank := IF(score = @prev, @rank, @seq) as rank
, @prev := score as prev
, @prevs := subjectname as prevs
FROM (
SELECT cl.classname
, sb.subjectname
, concat(st.firstname, ' ', st.lastname) as stname
, round( sum( case exam when 'Exam' then score/70*100
else score*10
end
) / COUNT(distinct exam) ) as score
FROM student_class stc
JOIN student st ON stc.studentid = st.id
JOIN class cl ON stc.classid = cl.id
JOIN level l ON cl.levelid = l.id
JOIN course c ON l.id = c.levelid
JOIN subject sb ON c.subjectid = sb.id
JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id
WHERE stc.semesterid = ?
AND exam = 'CA1'
AND cl.id = ?
GROUP BY c.id, sb.id, st.id
ORDER BY subjectname, score DESC
LIMIT 9223372036854775807 -- MariaDB bug workaround
) ordered
JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init
");
$res->execute([ $semester, $class ]);
# $chkm = 'checked';
#$chke = $chky = '';
break;
case 2: $term_headings = "<th>1st<br>Term<br> </th>
<th>2nd<br>Term<br>100</th>
<th>     </th>";
$res = $pdo->prepare("SELECT subjectname
, stname
, score
, @seq := IF(subjectname = @prevs, @seq+1, 1) as seq
, @rank := IF(score = @prev, @rank, @seq) as rank
, @prev := score as prev
, @prevs := subjectname as prevs
FROM (
SELECT sb.subjectname
, concat(st.firstname, ' ', st.lastname) as stname
, round( sum( score )) as score
FROM student_class stc
JOIN student st ON stc.studentid = st.id
JOIN class cl ON stc.classid = cl.id
JOIN level l ON cl.levelid = l.id
JOIN course c ON l.id = c.levelid
JOIN subject sb ON c.subjectid = sb.id
JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id
WHERE stc.semesterid = ?
AND cl.id = ?
GROUP BY c.id, sb.id, st.id
ORDER BY subjectname, score DESC
LIMIT 9223372036854775807 -- MariaDB bug workaround
) ordered
JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init
");
$res->execute([ $semester, $class ]);
#$chke = 'checked';
#$chkm = $chky = '';
break;
default: $term_headings = "<th>1st<br>Term<br> </th>
<th>2nd<br>Term<br> </th>
<th>3rd<br>Term<br>100</th>";
$res = $pdo->prepare("SELECT subjectname
, stname
, score
, @seq := IF(subjectname = @prevs, @seq+1, 1) as seq
, @rank := IF(score = @prev, @rank, @seq) as rank
, @prev := score as prev
, @prevs := subjectname as prevs
FROM (
SELECT sb.subjectname
, concat(st.firstname, ' ', st.lastname) as stname
, round( sum( score )/count(distinct sm.id)) as score
FROM student_class stc
JOIN student st ON stc.studentid = st.id
JOIN class cl ON stc.classid = cl.id
JOIN level l ON cl.levelid = l.id
JOIN course c ON l.id = c.levelid
JOIN subject sb ON c.subjectid = sb.id
JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id
JOIN semester sm ON stc.semesterid = sm.id
JOIN session sn ON sm.sessionid = sn.id
WHERE sm.sessionid = ?
AND cl.id = ?
GROUP BY cl.id, sb.id, st.id
ORDER BY subjectname, score DESC
LIMIT 9223372036854775807 -- MariaDB bug workaround
) ordered
JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init
ORDER BY subjectname, score DESC
");
$res->execute([ $session, $class ]);
}
$report_title = $termno == 3 ? "End of Year Results" : "End of Term Results";
################################################################################
# Get scores and put in array with required output structure #
################################################################################
$res = $pdo->prepare("SELECT st.id as stid
, concat_ws(' ', st.lastname, st.firstname, st.othername) as stname
, st.image
, cl.classname
, st.dob
, st.matricno
, sc.classid
, l.id as level
, sn.sessionname
, sm.semestername
, sm.date_until
, sm.semestername+0 as term
, c.subjectid
, s.subjectname
, exam
, score
FROM result r
JOIN
(
student_class sc
JOIN class cl ON sc.classid = cl.id
JOIN level l ON cl.levelid = l.id
JOIN course c ON c.levelid = l.id
JOIN student st ON sc.studentid = st.id
JOIN semester sm ON sc.semesterid = sm.id
JOIN session sn ON sm.sessionid = sn.id
JOIN subject s ON c.subjectid = s.id
) ON r.studentclassid = sc.id AND r.courseid = c.id
WHERE sn.id = ?
AND studentid = ?
#AND sm.date_until = ?
AND sm.semestername+0 <= ?
AND cl.id = ?
ORDER BY c.levelid, sc.id, c.subjectid, sc.semesterid, exam
");
#############################################################################
#############################################################################
$res->execute( [ $session, $student, $termno, $clid ] );
$data = [];
// get data common to all rows from first row
$r = $res->fetch();
if ($r) {
$studentname = $r['stname'];
$studentdob = $r['dob'];
$studentmatricno = $r['matricno'];
$studentlevel = $r['classname'];
$studentsession = $r['sessionname'];
$studentterm = "- Term $termno";
$nextterm = $r['date_until'];
$passport = "images/" . $r['image']; ### provide image path here
$level = $r['level'];
// then process the rest of the row data in the first and remaining rows
do {
if (!isset($data[ $r['subjectid'] ])) {
$data[ $r['subjectid'] ] = [ 'name' => $r['subjectname'],
#'exams' => ['CA1'=>'', 'CA2'=>'', 'CA3'=>'', 'Exam'=>''],
'exams' => ['CA1'=>'', 'CA2'=>'', 'Exam'=>''],
'scores' => [ 1=>0, 0, 0 ],
'avg' => 0
];
}
if ($r['term'] == $termno && isset($data[$r['subjectid'] ]['exams'][ $r['exam']])) {
$data[ $r['subjectid'] ]['exams'][ $r['exam'] ] = $r['score'];
}
$data[ $r['subjectid'] ]['scores'][$r['term']] += $r['score'];
} while ($r = $res->fetch());
// get the avg scores for the class
$avgs = classAverageScores($pdo, $clid, $session, $termno);
foreach ($avgs as $s => $av) {
if (isset($data[$s]))
$data[$s]['avg'] = round($av,0);
}
###########my ranking here###############
foreach ($res as $r) {
if (!isset($data[$r['subjectname']])) {
$data[$r['subjectname']]['students'] = [];
}
$data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $r['rank'] ];
}
################################################################################
# Get pupil count #
################################################################################
$res = $pdo->prepare("SELECT COUNT(DISTINCT stc.studentid) AS pupils
FROM student_class stc
JOIN semester sm ON sm.id = stc.semesterid
JOIN result r ON stc.id = r.studentclassid
WHERE sm.id = ?
AND stc.classid = ?
");
$res->execute([ $semester, $clid ]);
$pupil_count = $res->fetchColumn();
################################################################################
# Loop through the data array to construct the output table rows #
################################################################################
$tdata = '';
$n = 1;
$grand_total = 0;
$subject_count = 0;
foreach ($data as $subid => $subdata) {
$tdata .= "<tr><td>$n</td><td>{$subdata['name']}</td>";
foreach ($subdata['exams'] as $s) {
$tdata .= "<td>" . ($s=='' ? '–' : $s) . "</td>";
}
foreach ($subdata['scores'] as $t => $s) {
if ($s==0) $s = '';
$tdata .= "<td>" . ($t <= $termno ? $s : '') . "</td>";
}
$temp = array_filter($subdata['scores']);
$total = $temp ? round(array_sum($temp)/count($temp)) : 0;
$grand_total += $total;
if ($total) {
list($grade, $comment) = getGradeComment($pdo, $total, $level);
$subject_count++;
}
else {
$grade = '-';
$comment = '-';
}
$clr = GRADE_COLOUR[$grade] ?? '#000';
$tdata .= "<td>$total</td><td>{$subdata['avg']}</td><td style='color:$clr; font-weight: 600;'>$grade</td><td>$comment</td></tr>\n";
++$n;
}
}
<div class='w3-responsive'>
<table border='0' id='result-tbl'>
<tr class='w3-border-bottom w3-dark-gray'>
<th> </th>
<th>Subject</th>
<th>CA 1<br> <br>20</th>
<th>CA 2<br> <br>20</th>
<!--<th>CA 3<br> <br>10</th>-->
<th>Exam<br> <br>60</th>
<?=$term_headings?>
<!-- <th>1st<br>Term<br> </th>
<th>2nd<br>Term<br> </th>
<th>3rd<br>Term<br>100</th> -->
<th>Total</th>
<th>Class<br>Avg</th>
<th>Position</th>
<th>Grade</th>
<th>Comment</th>
</tr>
<?= $tdata ?>
</table>
</div>