CODEWITHSUNDEEP
phpmysql
hannebaumsaway
hannebaumsaway

Reputation: 2734

Detect future duplicate values while iterating through MySQL results in PHP

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.

Upvotes: 2

Views: 676

Answers (3)

hannebaumsaway
hannebaumsaway

Reputation: 2734

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.

Upvotes: 1

Litty
Litty

Reputation: 1876

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.

Upvotes: 2

Bob
Bob

Reputation: 128

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

Upvotes: 1

Related Questions