[FIXED] How to update database table with sql dense ranking function

Issue

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

Solution

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);
}

Answered By - Indra Kumar S

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)