I have this code in my project
if($run_query == true){ $i = 1; $query1 = "SELECT * FROM `scores` WHERE `subjects` = '{$result_subject}' AND `class` = '{$student_class}' AND `term` = '{$result_term}' AND `session` = '{$result_session}' ORDER BY `total` DESC "; $run_query1 = mysqli_query($connection, $query1); while($outputs = mysqli_fetch_assoc($run_query1)){ $subject_t = $outputs['total']; $sql = "UPDATE score SET position = '{$i}' WHERE total = '{$subject_t}'"; $run_query2 = mysqli_query($connection, $sql); $i++; }
The problem here is that the update query updates position column wit 1,2,3,4,5,6 etc and not observing dense ranking function. Please help
Advertisement
Answer
Use DISTINCT
to get unique total values of a subject. Then use it to update the ranking:
$query1 = "SELECT DISTINCT(`total`) FROM `scores` WHERE `subjects` = '{$result_subject}' AND `class` = '{$student_class}' AND `term` = '{$result_term}' AND `session` = '{$result_session}' ORDER BY `total` DESC "; $items = array(); while($outputs = mysqli_fetch_assoc($run_query1)){ $items[] = $outputs['total']; } // Note: table name is scores foreach ($items as $key => $total) { $sql = "UPDATE scores SET position = ".($key+1)." WHERE total = ".$total; $run_query2 = mysqli_query($connection, $sql); }