calculate a streak

Anon

Hoi there,

Is it possible to calculate a streak from a mysql database ?
An example of a mysql database containing the following data

won
won
lost
lost
lost
tie
won
won
won
won
tie
won
won
won
lost
lost

Now the streaks

The longest winning (won) streak is 4 (the 7th, 8th, 9th and the 10th row)

The longest loosing (lost) streak is 3 (the 3th, 4th and the 5th row)
Is it posible to calculate this please ???

Hopend on an answer, I sent you my best regards

sencirely 
     Yelleke

Anon

This type of query is not possible with MYSQL
as you need support for either views or derived queries.

You will have to do it in the client.

Anon

What do you mean with:
"You will have to do it in the client"

Do you have an example for that, pls.
btw: thx for the reply

Anon

I mean by writing a PHP script that retrievs all rows and keep track of wins and losses.

untested code sample

$query = "select result from matches order by datePlayed";

$result = mysql_query($query);
$losses = 0;
$wins = 0;
$winstreak = 0;
$loosestreak = 0;
$previous = "";
while ($row = mysql_fetch_array($result)) {
if ( $previous != $row["result"] )
{
if ( $losses > $loosestreak ) $loosestrak = $losses;
if ( $wins > $winstreak)
$winstreak = $wins;
$wins = 0;
$losses = 0;
}
if ( $row["result"] == "won" ) $wins++;
if ( $row["result") == "loss" )$losses++;
$previous = $row["result"];
}

Anon

THX Lars Berg

Your CODE SAMPLE is just what I needed.
Many, many thx.

it has just 1 error

if ( $row["result") == "loss" )$losses++;
^
needs to be an ]

thanks again man.