Menu
Community /Pin to ProfileBookmark
@NogDogOct 07.2021 — #Just wondering if (assuming PDO style place-holder for the team of interest):
Assuming that works, I might try something like this to get the longest winning streak (until I think of a better way):
Winning / Losing Streak
I have a simple mySQL table which has team1 and team2 along with team1Pts and team2Pts.
I have heaps of teams. But I’ll be doing the follow when considering a specific team ID. Say ID: 1.
Team ID 1, could be team1 or team2 in the database.
I need to find out the most number of times my TeamID 1 has had more points than any other team. AKA a winning streak?
I can list all the winning games with the use of CASE etc.. But not idea if this can be done.
Sign in
to post a comment12 Comments(s) ↴
0
@NogDogOct 07.2021 — #Just wondering if (assuming PDO style place-holder for the team of interest):[code=php]
$sql = <<<EOD
select
gameDate,
case
when Team1Score > Team2Score then true
else false
end as win
from (
(
select gameDate, Team1Points as Team1Score, Team2Points as Team2Score
from Games where Team1ID = :team_id
) union (
select gameDate, Team2Points as Team1Score, Team1Points as Team2Score
from Games where Team2ID = :team_id
)
) as games
order by gameDate
EOD;
[/code]Assuming that works, I might try something like this to get the longest winning streak (until I think of a better way):
[code=php]
$longest = $current = 0;
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
if($row['win']) {
$current++;
if($current > $longest) {
$longest = $current;
}
} else {
$current = 0;
}
}
[/code]