Jump to content

Querying consecutive outcomes in date order

Recommended Posts

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.

Share this post

Link to post
Share on other sites

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);
$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 first win, set start date for current streak
            $currentStreakStart = $row['date'];
        //Increment the win count for current streak
        //Set a 'lastWinDate' (used to determine the end of streak after 1st loss)
        $lastWinDate = $row['date'];
        //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 by Psycho

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.