How to update database table with sql dense ranking function

Question

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

You need to have DENSE_RANK() OVER (PARTITION BY totals ORDER BY totals DESC) AS Rank in your select query and then update position against $outputs['rank'] — Adi, May 15 '21 at 16:41
Since you are updating position based of $i, it's bound to be incremental values ranging from 1 to loop end — Adi, May 15 '21 at 16:43
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, May 15 '21 at 17:51

score 0 · Accepted Answer · edited May 15 '22 at 00:38

0

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

edited May 15 '22 at 00:38

lemon

14,875
6
18
38

answered May 15 '21 at 16:42

Indra Kumar S

2,818
2
16
27

Well, this one way of doing it but the question specifically asks with sql dense ranking function – Adi May 15 '21 at 16:48
Thanks for your prompt response. The code ranked the scores. First highest numbers were 90 and 90, the code showed 2,2 instead of 1,1 and then it skipped 3. And so on with other ties – Victor May 15 '21 at 18:07
Sorry I made a mistake in executing the codes. It worked perfectly. Thanks – Victor May 15 '21 at 18:20

How to update database table with sql dense ranking function

1 Answers1