Longest streaks using MySQL and two 'where' clauses

General PHP Help
#1

I’m building a page that displays various longest streaks from a table that contains data for one single team.

The data is presented in the following manner:

[table]
[tr]
[td]date[/td]
[td]result[/td]
[td]gf[/td]
[td]ga[/td]
[td]venue[/td]
[/tr]
[tr]
[td]1980-08-16[/td]
[td]W[/td]
[td]3[/td]
[td]0[/td]
[td]H[/td]
[/tr]
[tr]
[td]1980-08-19[/td]
[td]W[/td]
[td]3[/td]
[td]2[/td]
[td]A[/td]
[/tr]
[tr]
[td]1980-08-23[/td]
[td]W[/td]
[td]2[/td]
[td]1[/td]
[td]H[/td]
[/tr]
[tr]
[td]1980-08-25[/td]
[td]D[/td]
[td]0[/td]
[td]0[/td]
[td]A[/td]
[/tr]
[/table]

… and so on.

I am able to calculate overall streaks in the data for wins, defeats and undefeated etc, but I’m having trouble in further refining the query so that it returns streaks where a game is H or A. For example, the code I use to get the overall win streak is:

[php]SELECT result, type, MIN(date) as StartDate, MAX(date) as EndDate, COUNT() as Games
FROM (SELECT result, type, date, compfull, (SELECT COUNT()
FROM resultengine R
WHERE R.result <> RE.result
AND R.date <= RE.date) as RunGroup
FROM resultengine RE) A WHERE result=‘W’ GROUP BY result, RunGroup ORDER BY Games[/php]

I’ve tried various methods of introducing a subsequent where clause that refines results to those that are “h” for home games, such as this example:

[php]SELECT
result,
venue,
MIN(date) as StartDate,
MAX(date) as EndDate,
COUNT() as Games
FROM
(
SELECT
result,
venue,
date,
(
SELECT
COUNT()
FROM
resultengine R
WHERE
R.result <> RE.result
AND
(R.venue = ‘H’) <> (RE.venue = ‘H’)
AND
R.date <= RE.date
) as RunGroup
FROM
resultengine RE
) A
WHERE
venue = ‘H’
AND
result=‘W’
GROUP BY
result, RunGroup
ORDER BY
Games[/php]

It’s not working, however - the streaks it returns are being truncated and I can’t see for what reason they are. And I know they are because I’ve done manual counts of the data based on results that are returned. Sorry if this post is a bit long, but what am I not doing to get the desired result. Let me know if more info is needed.

#2

What is the table structure? Your queries look a bit over complicated for what you are after.

#3

Forgive me if this isn’t quite the info you were after, but the table structure is as follows:

“Date” (date)
“Result” (text)
“GF” (int(2))
“GA” (int(2))
“Venue” (text)

There are other columns but I don’t believe them to be relevant to what I’m looking for. If there is a simpler way of approaching this, I’m all for it

#4

I’ll take a stab here, this is untested…

[php]select MAX(@count:=IF(a.result = b.result, @count + 1, 0)) as LongestStreak
from
(select (select @rownum := @rownum + 1 from ( select @rownum := 0 ) RowTable)
as rownumber, re.* from resultengine as re order by date) a
,(select (select @rownum := @rownum + 1 from ( select @rownum := 0 ) RowTable)
as rownumber, re.* from resultengine as re order by date) b
where a.rownumber = b.rownumber and a.result = w and venue = “H”[/php]

#5

Honestly, I’ll have to wait until tomorrow to figure this out. I just finished a 64 oz margaritas, because the server didn’t say how big, the BIG ONE was! And it wasn’t shy on tequila!

Leave me alone, it’s been a rough wee!

#6

What kind of results do you get with this:
[php]
SELECT
result,
venue,
MIN(date) as StartDate,
MAX(date) as EndDate,
COUNT(*) as Games
FROM
resultengine
WHERE
venue = ‘H’
AND
result = ‘W’
GROUP BY
result, RunGroup
ORDER BY
Games[/php]

#7

I’m afraid I gave this suggestion a go and it has returned nothing at all.

#8

Thanks Topcoder - the problem is not knowing how to generate the output. How it’s constructed so far is thus:

[code]while($row=mysql_fetch_array($result))
{
$startrundate = date(“d F Y”,strtotime($row[‘StartDate’]));
$endrundate = date(“d F Y”,strtotime($row[‘EndDate’]));

echo "<tr>";   
	echo "<td>".$row['Games']."</td>";
	echo "<td class='tableprofile' style='text-align:right;'>".$startrundate." - ".$endrundate."</td>";  
echo "</tr>";    
$rowCount += 1;

}
[/code]

Is it a case of changing $row[‘Games’] to $result?

#9

I changed the SQL so you don’t have to change your code. But I didn’t incorporate the max/min date of the streak. This should just return the longest streak. Run it in phpadmin and see the results before you put it code.

[php]select MAX(@count:=IF(a.result = b.result, @count + 1, 0)) as games
from
(select (select @rownum := @rownum + 1 from ( select @rownum := 0 ) RowTable)
as rownumber, re.* from resultengine as re order by date) a
,(select (select @rownum := @rownum + 1 from ( select @rownum := 0 ) RowTable)
as rownumber, re.* from resultengine as re order by date) b
where a.rownumber = b.rownumber +1 and a.result = ‘W’ and venue = ‘H’[/php]

#10

I’ve given that a go in phpmyadmin - but the venue column is described as “ambiguous”

#11

Nm, I have to think about it…

I built a mini table in sql fiddle to test queries on…

http://sqlfiddle.com/#!9/0fa40/2

#12

OK - I got the longest steak code working - Now you just need to incorporate the min and max dates…

http://sqlfiddle.com/#!9/0fa40/3

[php]select MAX(@count:=IF(a.result = b.result, @count + 1, 1)) from (
select (select @rownum := @rownum + 1 from ( select @rownum := 0 ) RowTable)
as rownumber, re.* from resultengine as re order by date) a left join
(
select (select @rownum2 := @rownum2 + 1 from ( select @rownum2 := 0 ) RowTable)
as rownumber, re.* from resultengine as re order by date) b on a.rownumber = b.rownumber + 1
where a.result = ‘W’ and a.venue = ‘H’ [/php]

#13

Hi Topcoder, I can see it working in your example there - but I’ve added the data for a full season (not sure if you can see it) in which there is a four-match home win streak 20 Sep - 8 Nov but it’s not being recognised? The result returned continues to be “3”

#14

Yeah, I don’t see the full data.

Just trying to get the results this way with tricky SQL is a little crazy, might be better off calculating it in code.

Sponsor our Newsletter | Privacy Policy | Terms of Service