PHP associative array

[bululu]

I have the following array:

$learners=array('Eliza'=87,  'Joe'=81, 'Anne'=69, 'Marley'=39, 'Teddy'=39, 'Jemma'=90, 'Sylvia'=87);

So far I have been able to separate the two arrays as follows:

$tudents=array_keys($learners);
$scores=array_values($learners);

The ranking is as follows:

Student    Score    Position
Jemma       90          1
Sylvia      87          2
Eliza       87          2
Joe         81          4
Anne        69          5
Marley      39          7
Teddy       69          7

I would like to create a new array with names as keys and positions as values i.e

$positions=array('Jemma'=1, 'Sylvia'=2, 'Eliza'=2, 'Joe'=4, 'Anne'=5, 'Marley'=7, 'Teddy'=7);

This will allow me to echo any name and position at any point on the script.

 

The ranking is not straightforward if the scores have duplicates. If there is a tie at number 2, the 3rd position is skipped. If the tie occurs at the end of the scores, then both scores will be placed at the last position and the preceding position will be skipped, in the example above, position 6 has been skipped and the two 39s occupy position 7.

 

Any help will be appreciated

[Barand]

No need to split the array

$learners=array(
    'Eliza'  => 87,  
    'Joe'    => 81, 
    'Anne'   => 69, 
    'Marley' => 39, 
    'Teddy'  => 39, 
    'Jemma'  => 90, 
    'Sylvia' => 87
    );

arsort($learners);

$ranking = array();
$k = $prev = 0;
$rank = 1;
foreach ($learners as $name => $score) {
    $k++;
    $rank = $score == $prev ? $rank : $k;
    $ranking[$name] = $rank;
    $prev = $score;
}

echo '<pre>',print_r($ranking, true),'</pre>';

/**** OUTPUT **********
Array
(
    [Jemma] => 1
    [Sylvia] => 2
    [Eliza] => 2
    [Joe] => 4
    [Anne] => 5
    [Marley] => 6
    [Teddy] => 6
)
***********************/

[vinny42]

 

If there is a tie at number 2, the 3rd position is skipped

 

 

Does this data come from a database perhaps? Because databased have functions to give you the ranking, no need for messing about with PHP.

[bululu]

Does this data come from a database perhaps? Because databased have functions to give you the ranking, no need for messing about with PHP.

If a tie occurs at the end, the preceding position is skipped, so if there are 3 students at number 9, we skip positions 7 and 8, so we should have. AS can be seen, 9 students sat for the test and the last scorers are at number 9. Positions are maintained though some have been forfeited e.g number 2, because two decided to be at number 1.

70           1
70           1
69           3
57           4
46           5
45           6
40           9
40           9
40           9

Yes, it comes from a MySQL Database. If the ranks/ positions were straightforward i.e just 1,2,3, etc then I would use mysql functions. I cannot see how to do it from MyQL as the data is being retrieved.

 

Can MySQL functions handle this degree of ranking?

Edited November 2, 2013 by bululu

[Barand]

Here's how to do it in a query

mysql> SELECT name,
    ->     @row := @row+1 as row,
    ->     @rank := IF(score=@prev, @rank, @row) as rank,
    ->     @prev := score as score
    ->     FROM score
    ->         JOIN (SELECT @row:=0, @rank:=0, @prev:=0) as init
    ->     ORDER BY score DESC;
+-------+------+------+-------+
| name  | row  | rank | score |
+-------+------+------+-------+
| David |    1 |    1 |   106 |
| Sam   |    2 |    2 |    76 |
| Bob   |    3 |    3 |    75 |
| Jane  |    4 |    3 |    75 |
| Joe   |    5 |    5 |    61 |
| Mike  |    6 |    5 |    61 |
| Mary  |    7 |    7 |    59 |
+-------+------+------+-------+

Edited November 2, 2013 by Barand

[bululu]

 

Here's how to do it in a query

mysql> SELECT name, score, rank
    -> FROM
    ->     (
    ->     SELECT name,
    ->     @row := @row+1 as row,
    ->     @rank := IF(score=@prev, @rank, @row) as rank,
    ->     @prev := score as score
    ->     FROM score
    ->         JOIN (SELECT @row:=0, @rank:=0, @prev:=0) as init
    ->     ORDER BY score DESC
    ->     ) as rankings;
+-------+-------+------+
| name  | score | rank |
+-------+-------+------+
| David |   106 | 1    |
| Sam   |    76 | 2    |
| Bob   |    75 | 3    |
| Jane  |    75 | 3    |
| Joe   |    61 | 5    |
| Mike  |    61 | 5    |
| Mary  |    59 | 7    |
+-------+-------+------+

This looks great. I do not have the rank column, only the name and score. Users provide a name and a score and when that data is queried, the ranks are to be calculated. Do I need to add a rank column to the database? How does it work since I just supply the data for name and score?

[kicken]

That query calculates the rank and row columns dynamically via variables. There is no need for either of those columns to be physically present in your tables.

[Barand]

As Kicken said, the row and rank are calculated. My score table looks like this:

+-------+-------+
| name  | score |
+-------+-------+
| Bob   |    75 |
| David |   106 |
| Jane  |    75 |
| Joe   |    61 |
| Mary  |    59 |
| Mike  |    61 |
| Sam   |    76 |
+-------+-------+

[vinny42]

Or you could just select the number of better scores per score:

 

 

SELECT scores.*, (SELECT COUNT(*)+1 FROM scores AS betterscores WHERE betterscores.score < scores.score) AS rank

FROM scores

ORDER BY rank;

 

or if your database supports windowing, just ask it for the ranking:

 

SELECT 

id,

score,

RANK() OVER (order by score ASC)

FROM scores

[Barand]

 

Or you could just select the number of better scores per score:

 

 

SELECT scores.*, (SELECT COUNT(*)+1 FROM scores AS betterscores WHERE betterscores.score < scores.score) AS rank

FROM scores

ORDER BY rank;

 

 

You could if you want a highly inefficient solution. That solution uses a dependent subquery where every row has to run another query on the table.

[vinny42]

You could if you want a highly inefficient solution. That solution uses a dependent subquery where every row has to run another query on the table.

 

 

True, if you have a few hundredthousand rows to  examine you'd probably want to do it differently, using a left join and a count and a group-by;

 

SELECT scores.id, COUNT(betterscores.score)+1

FROM scores

LEFT JOIN scores AS betterscores ON betterscores.score < scores.score

GROUP BY scores.id

ORDER BY 2 ASC

Edited November 3, 2013 by vinny42

[bululu]

Thanks guys - Barand, Vinny42 and Kicken, for sharing your expertise, I am learning alot and I really appreciate. This has been solved.

[Barand]

 

True, if you have a few hundredthousand rows to  examine you'd probably want to do it differently, using a left join and a count and a group-by;

 

SELECT scores.id, COUNT(betterscores.score)+1

FROM scores

LEFT JOIN scores AS betterscores ON betterscores.score < scores.score

GROUP BY scores.id

ORDER BY 2 ASC

 

 

On a table with 180,000+ rows both the dependent subquery and left join versions timed out. The version with the SQL variables took < 0.9 seconds.

[vinny42]

 

On a table with 180,000+ rows both the dependent subquery and left join versions timed out. The version with the SQL variables took < 0.9 seconds.

 

What was the test setup?

[vinny42]

Hmm, it seems that the join solution does indeed not work too well here. Looking at the explain paths it's logical because the join has to basically create a cartesian productand flatten that out to get the counts.

 

Of course I'm going to annoy you by saying that PostgreSQL's windowing functions did the trick on 300.000 results in 0.4 seconds on an old 2GB dualcore laptop. :-)

[Barand]

My 180,000 record table has no index on the "score" column so that could slow it

[vinny42]

Did you have any indexes when you tried the JOIN solutions? Because that would make your previous popst irrelevant.

[Barand]

Same table in all tests

[vinny42]

Right... well,  what can I say, except. Goodbye.

 

I'm done here, I'll leave you guys along in your happy little bubbles... I'm off to find intelligent life...

[ignace]

Ok. Bye.

Edited November 3, 2013 by ignace