Detect future duplicate values while iterating through MySQL results in PHP

Question

I am trying to calculate a ranking of a team in an ordered MySQL result set, and the issue I'm having is detecting ties for the first team to show up with the tied value.

For example, say the result set is the following:

team_id    pts
---------------
1          89
2          87
3          76
4          76
5          52

I calculate the ranking of the team with the following PHP:

$i = 0;
while($row = mysql_fetch_assoc($r)) { //iterate thru ordered (desc) SQL results
    ++$i;
    ($row['pts'] == $prev_val)
        ? $rnk = 'T-' . $rnk    //same as previous score, indicate tie
        : $rnk = $i;            //not same as previous score
    $rnk = str_replace('T-T-','T-',$rnk); //eliminate duplicative tie indicator
    if ($row['team_id'] == $team_id) { //current team in resultset matches team in question, set team's rank
        $arr_ranks['tp']['cat'] = 'Total Points';
        $arr_ranks['tp']['actual'] = number_format($row['pts'],1);
        $arr_ranks['tp']['league_rank'] = $rnk;
        $arr_ranks['tp']['div_rank'] = $div_rnk;
    }
    else if ($i == 1) { //current team is category leader (rank=1) and is not team in question, set current team as leader
        $arr_ranks['tp']['leader'] = "<a href='index.php?view=franchise&team_id=" . $row['team_id'] . "'>" . get_team_name($row['team_id']) . '</a> (' . number_format($row['pts'],1) . ')';
    }
    $prev_val = $row['pts']; //set current score as previous score for next iteration of loop
}

The "tie" logic above will capture team #4 as having tied with team #3, but not vice versa.

In other words, for team #3, $rnk = 3, while for team #4, $rnk = T-3. (Both should be "T-3".)

So the question becomes: how do I "look ahead" while iterating through the results to find out if the current score is a tie/duplicate of scores further down the list, so I can treat it as a tie along with the subsequent dupes?

Thanks.

EDIT: I can implement Ignacio's code if I first store results in a table, such as "wins" below:

select
    s1.team_id,
    t.division_id,
    sum(s1.score>s2.score) tot_wins,
            (   select count(*)
                from wins
                where team_id <> s1.team_id
                and wins > (select wins
                            from wins
                            where team_id = s1.team_id)
            )+1 as rnk
from
    scoreboard s1
    left join teams t
        on s1.team_id = t.team_id
    left join scoreboard s2
        on s1.year=s2.year and s1.week=s2.week and s1.playoffs=s2.playoffs and s1.game_id=s2.game_id and s1.location<>s2.location
group by s1.team_id
order by tot_wins desc;

This gives the following results:

team_id    division_id   tot_wins  rnk
--------------------------------------
10         1             44        1
2          1             42        2
3          2             42        2
8          1             39        4
5          2             37        5
. . .

However, it occurs to me that I was already getting to this result set, and this doesn't actually solve my problem.

To avoid confusion, I've posted the "follow-up" problem separately.

[Are you sure you need to solve this problem?](http://stackoverflow.com/questions/8767323/how-best-to-get-someones-rank-from-a-scores-table-with-php-and-mysql-without) — Ignacio Vazquez-Abrams, Jun 01 '12 at 18:24
Perhaps not! I tried searching for a preexisting solution but didn't come across yours. Let me try it out! — hannebaumsaway, Jun 01 '12 at 19:12
@IgnacioVazquez-Abrams I added a query to my question above in which I'm struggling to implement your solution. — hannebaumsaway, Jun 01 '12 at 21:03
@IgnacioVazquez-Abrams Just to clarify, I am having trouble implementing your `COUNT(*)...+1` solution in the above query, as I have to get my results in the query itself, and not from a prepopulated table. — hannebaumsaway, Jun 04 '12 at 17:20
@IgnacioVazquez-Abrams Please see my latest edit. My work isn't finished even with your brilliant ranking solution. :) — hannebaumsaway, Jun 11 '12 at 16:50

score 2 · Answer 1 · answered Jun 01 '12 at 20:40

2

I like Ignacio's link to his answer. But if you still wanted to use PHP, you could collect the ranks by SCORE and assign teams to each score. It's probably not the most efficient way to do it, but it would work.

$ranks = array();
while ($row = mysql_fetch_assoc($result)) {
    $ranks[$row['pts']][] = $row['team_id'];
}

$ranks would be an array that could look like...

$ranks[89] = array(1);
$ranks[87] = array(2);
$ranks[76] = array(3,4);
$ranks[52] = array(5);

Use a foreach on $ranks, and double check which way the points would come up (ascending or descending). You can use count() to see if there's a tie.

answered Jun 01 '12 at 20:40

Litty

1,856
1
16
35

It looks like that would work, thank you. I'll try to make Ignacio's solution work, as I think it would be more streamlined, but this is a sound approach as well! – hannebaumsaway Jun 01 '12 at 21:04
No worries! I definitely agree, if you can accomplish this at the SQL level, it would likely be more efficient. Good luck! – Litty Jun 02 '12 at 01:09

score 1 · Answer 2 · answered Jun 01 '12 at 18:36

1

$exists = array();
if ($row['team_id'] == $team_id && !in_array($row['pts'], $exists)) { //current team in resultset matches team in question, set team's rank
        $exists[] = $row['pts'];
        $arr_ranks['tp']['cat'] = 'Total Points';
        $arr_ranks['tp']['actual'] = number_format($row['pts'],1);
        $arr_ranks['tp']['league_rank'] = $rnk;
        $arr_ranks['tp']['div_rank'] = $div_rnk;
    }

answered Jun 01 '12 at 18:36

Bob

128
1
6

At first glance it looks like you would run into the same problem of the first score in the duplicate set not being caught as a tie. – hannebaumsaway Jun 01 '12 at 19:14

score 1 · Accepted Answer · edited May 23 '17 at 12:21

This question has been answered here.

The query:

SELECT a.team_id, a.wins, count(*) instances
FROM
    (SELECT
        s1.team_id,
        sum(s1.score>s2.score) wins
    FROM scoreboard s1
        LEFT JOIN scoreboard s2
            ON s1.year=s2.year
            AND s1.week=s2.week
            AND s1.playoffs=s2.playoffs
            AND s1.game_id=s2.game_id
            AND s1.location<>s2.location
    GROUP BY s1.team_id) AS a
    LEFT JOIN
        (SELECT
            sum(s1.score>s2.score) wins
        FROM scoreboard s1
            LEFT JOIN scoreboard s2
                ON s1.year=s2.year
                AND s1.week=s2.week
                AND s1.playoffs=s2.playoffs
                AND s1.game_id=s2.game_id
                AND s1.location<>s2.location
        GROUP BY s1.team_id) AS b
            ON a.wins = b.wins
GROUP BY a.team_id, b.wins
ORDER BY a.wins DESC;

This gives the output...

=================================
|team_id   | wins    |instances |
=================================
|10        | 44      |1         |
|2         | 42      |3         | //tie
|9         | 42      |3         | //tie
|5         | 42      |3         | //tie
|3         | 41      |1         |
|11        | 40      |1         |
|...       |         |          |
=================================

Then, in PHP, I'll be able to detect all ties by checking when $row['instances'] > 1.

I think posting a minimal solution here would be more helpful. I clicked on the link, and nothing popped out at me. — octopusgrabbus, Jun 13 '12 at 19:40

Detect future duplicate values while iterating through MySQL results in PHP

3 Answers3

Linked