This is the second of two buggy problems I’m having at the moment and hopefully someone can put me straight.
In attempting to calculate the longest goalscoring streak for a single player, I’ve hit a stumbling block with the >= PHP operator not performing as I thought it would.
[table]
[tr]
[td]date[/td]
[td]gls[/td]
[/tr]
[tr]
[td]1990-01-01[/td]
[td]2[/td]
[/tr]
[tr]
[td]1990-01-05[/td]
[td]1[/td]
[/tr]
[tr]
[td]1990-01-15[/td]
[td]1[/td]
[/tr]
[tr]
[td]1990-01-17[/td]
[td]0[/td]
[/tr]
[tr]
[td]1990-01-19[/td]
[td]1[/td]
[/tr]
[/table]
The PHP query I am using is as follows:
[php]SELECT gls, MIN(date) as StartDate, MAX(date) as EndDate, COUNT() as Games
FROM (SELECT gls, date,
(SELECT COUNT()
FROM goalsengine G
WHERE G.gls <> GE.gls
AND G.date <= GE.date) as RunGroup
FROM goalsengine GE) A
WHERE gls>=‘1’
GROUP BY gls, RunGroup
ORDER BY Games[/php]
I formed the query this way in the belief that >= would tally up streaks where this player had scored one or more goals in a game. From the table above, the first three entries would represent a scoring run of three games, for example.
Instead, the query is returning streaks but only where a certain number of goals are scored i.e. despite scoring in the first three games, the first entry (where two goals were scored, not one) is ignored when the streak is returned.
To return the results, I am using the following:
[php]while($row=mysql_fetch_assoc($result))
{
{$startrundate = date(“d F Y”,strtotime($row[‘StartDate’]));}
{$endrundate = date(“d F Y”,strtotime($row[‘EndDate’]));}
echo “
echo “
echo “
echo “
$rowCount += 1;
}[/php]
By comparison, WHERE gls=‘0’ is giving me the desired non-goalscoring streaks. I feel I have perhaps overlooked something straightforward but cannot see what.