Querying consecutive outcomes in date order

[NiallAA]

Versions: Mysql 5.5, PHP 5.4

 

Evening folks,

 

To simplify this request, I have a database of competition results. 

 

The table structure is as follows

 

id (INT), date (DATE), pointsfor (TINYINT), pointsagainst (TINYINT)

 

To summarise, if pointsfor > pointsagainst, the outcome on that date is considered a win.

 

The result I'm looking to display is the 'winningest' run, i.e the start and end date of the most victories recorded in consecutive date order.

 

I have no idea where to start with this, as I have yet to deal much with dates. Any help would be greatly appreciated.

 

Thank you.

[Psycho]

I'm sure you could do it with a query, but I think it would be complicated. Similar to determining a "rank", but you would have to keep track of win counts and start end dates. How many records will be applicable to any one request? If it is not really huge, just pull all the relevant records and determine the win streaks when iterating over the results.

 

Not tested, but I think the logic is sound

 

//Run query for all the records ordering by date
// - 'win' will be a Boolean (1 or 0)
$query = "SELECT date, (pointsfor > pointsagainst) as win
          FROM table_name
          ORDER BY `date` ASC";
$sth = $dbh->prepare($query);
$sth->execute();
$result = $sth->fetchAll();
 
//Variable to hold the longest streak data
$longestStreakCount = 0;
$longestStreakStart = '';
$longestStreakEnd   = '';
 
//Variable to track 'current' streak counts
$currentStreakCount = 0;
 
foreach($result as $row)
{
    //Check if current record IS a win
    if($row['win'])
    {
        ## CURRENT RECORD IS A WIN
        //If first win, set start date for current streak
        if($currentStreakCount==0)
        {
            $currentStreakStart = $row['date'];
        }
        //Increment the win count for current streak
        $currentStreakCount++;
        //Set a 'lastWinDate' (used to determine the end of streak after 1st loss)
        $lastWinDate = $row['date'];
    }
    else
    {
        ## CURRENT RECORD IS NOT A WIN
        //Check if $current Streak is greater than the longest streak so far
        if($currentStreakCount > $longestStreakCount)
        {
            //Set new values for longest streak
            $longestStreakCount = $currentStreakCount;
            $longestStreakStart = $currentStreakStart;
            $longestStreakEnd   = $lastWinDate;
        }
        //Reset current streak count
        $currentStreakCount = 0;
    }
}

Edited March 16, 2017 by Psycho