Jump to content

PHP associative array


bululu
Go to solution Solved by bululu,

Recommended Posts

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

Link to comment
Share on other sites

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
)
***********************/
Link to comment
Share on other sites

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 by bululu
Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

  • Solution

 

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?

Link to comment
Share on other sites

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
Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

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 by vinny42
Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

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. :-)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.