How to select duplication rank with php from multiple array

Question

I am working on a Database that manipulates college students exam results. Basically, I am pulling the records from a MySql Database, puling one class at any given time. I want to rank the students, with the highest performer at number 1.

I have try to read this article but it show me only single array :

http://stackoverflow.com/questions/6163225/how-do-i-rank-array-values-with-duplicate-values-and-skipping-some-positions-if

Here is an illustration;

get result and make and array

$grades = array();
    foreach($results->result() as $row){

        $grades[] = array('rank'=>$row->total,'name'=>$student_name);

    }

array result :

$grades[] = array(
array('rank'=>"123","name"=>"samphors"),
array('rank'=>"123","name"=>"sovann"),
array('rank'=>"102","name"=>"dy vann"),
array('rank'=>"113","name"=>"koro vann")
);

So I want to capture Mysql data as a multiple array. Once I have the data in an array, I should then assign each student a position in the class such as 1/10 (number 1, the 123 score), 4/10 etc. Now the problem is that if there is a tie, then the next score skips a position and if there are 3 scores at one position then the next score skips 2 positions. So the scores above would be ranked as follows;

----------------------------------
| no | name       | score | rank |
| 1  |samphors    | 123   | 1    |
| 2  |sovann      | 123   | 1    |
| 3  |koro vann   | 113   | 2    |
| 4  |dy vann     | 102   | 3    |
----------------------------------

Is it possible (humanly possible /php possible) to use PHP to rank the scores and name of student above in such a way that it can handle possible ties such as 4 scores at one position? SADLY, I could not come up with a function to do this. I need a PHP function (or something ... PHP) that will take an array and produce a ranking as above.

Any help will be deeply appreciated, though I think I may be asking for too much. If it's possible to do this with MySQL query data without having it in an array, then that will also be helpful!

have you tried adding "order by rank asc" to the end of your sql query? — glend, Mar 23 '15 at 12:24
I don't understand the formula, how the rank should be calculated. Please, provide and example of such calculation. — user4035, Mar 23 '15 at 12:24

score 0 · Answer 1 · answered Mar 23 '15 at 12:23

0

You should alter your mysql query as

SELECT * FROM `your table name` ORDER BY `rank` ASC

answered Mar 23 '15 at 12:23

Usman Akram

139
7

score 0 · Answer 2 · answered Mar 23 '15 at 12:30

This will handle ranks with the same score. basically you will have to do it in your query.

SELECT  d.*, c.ranks
FROM
    (
      SELECT    Score, @rank:=@rank+1 Ranks
      FROM
              (
                  SELECT  DISTINCT Score 
                  FROM    tableName a
                  ORDER   BY score DESC
              ) t, (SELECT @rank:= 0) r
    ) c 
    INNER JOIN tableName d
        ON c.score = d.score

Rene Korss · Accepted Answer · 2015-03-23T12:46:49.313

My solution with PHP. This requires that your results are ordered by score ASC.

<?php

// Start array
$grades = array(
  array('score' => "123", "name" => "samphors"),
  array('score' => "123", "name" => "sovann"),
  array('score' => "113", "name" => "koro vann"),
  array('score' => "102", "name" => "dy vann")
);

$i         = 0;
$prevScore = 0;

foreach ( $grades as &$grade ) {
  // Increment rank only if scores different
  if( $grade[ 'score' ] != $prevScore ){
    $i++;
  }

  $prevScore = $grade[ 'score' ];
  $grade[ 'rank' ] = $i;
}

echo '<pre>';
print_r($grades);
echo '</pre>';

?>

Output:

Array
(
  [0] => Array
      (
          [score] => 123
          [name] => samphors
          [rank] => 1
      )

  [1] => Array
      (
          [score] => 123
          [name] => sovann
          [rank] => 1
      )

  [2] => Array
      (
          [score] => 113
          [name] => koro vann
          [rank] => 2
      )

  [3] => Array
      (
          [score] => 102
          [name] => dy vann
          [rank] => 3
      )

)

Even if you dont use ASC in your MySQL, you can do a usort in PHP to the Array to sort it. — harishannam, Mar 23 '15 at 14:53

How to select duplication rank with php from multiple array

3 Answers3