Skip to content
Advertisement

How to update database table with sql dense ranking function

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

enter image description here

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);
}
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement