I have been googling since yesterday on how I could do this but with no avail. I can perfectly achieve ranking with ties and return rank of a specific student using php and mysql quesry. But I have one problem. The problem is that if a student fails in one subject (pass mark is given 33) out of group by subject (available different subjects) in the table, I want to skip calculating his ranking using mysql.
$query = "SELECT id, Names,
TOTALSCORE, Rank
FROM(
(SELECT t.*, IF(@p = TOTALSCORE, @n, @n := @n + 1)
AS Rank, @p := TOTALSCORE
FROM(
(SELECT id, Names,
SUM(score) TOTALSCORE
FROM exam, (SELECT @n := 0, @p := 0) n
GROUP BY id
ORDER BY TOTALSCORE DESC
) t
) r";
$myrank = mysql_query($query, $dbconnect) or die(mysql_error());
$i = 0;
$j = 0;
$data = array();
while($row_myrank = mysql_fetch_assoc($myrank)){
$data[$i] = $row_myrank;
if(isset($data[$i - 1]) && $data[$i - 1]['TOTALSCORE'] == $data[$i]['TOTALSCORE']){
$data[$i]['Rank'] = $j;
}else{
$data[$i]['Rank'] = ++$j;
}
$i++;
}
foreach($data as $key => $value){
if($value['id'] == $id){
if($value['Rank']>0){
return $value['Rank'];
} else{
return "<font color=red>Not get Ranking.</font>";
}
}
}
This is the sample shot of what I want to achieve. In this shot, I only have three different students, in the application I might have more than 300 different students. The subject where a student failed is marked red.
I am not quite sure what "
pass mark is given 33
" means.I assume that it is 33% of
Fullmark
,for example if
Fullmark
=75 then the pass score is 33% of 75 = 24,75 points,which means that a student must gain at least 24,75 points to pass the exam.
In this case a simple check with an antijoin can help:
the above means: "give me only these records for which does not exist any exam with pas mark < 33%"
A full query is shown below, and a demo here --> http://www.sqlfiddle.com/#!2/50ef8/2
-- EDIT -- The above query calculates incorrect rank values, here is improved version:
and demo: --> http://www.sqlfiddle.com/#!2/50ef8/4