What I need to do is have a data structure that shows jobs organised into 14 day periods, but only when an id is the same. I've implemented all sorts of stuff, but they have failed miserably.

Ideally, maybe a SQL expert could handle all of this in the query.

Here is some of my code. You can assume all library stuff works as expected.

 $query = 'SELECT date, rig_id, comments FROM dor ORDER BY date DESC';

 $dors = Db::query(Database::SELECT, $query)->execute()->as_array();

This will return all jobs, but I need to have them organised by 14 day period with the same rig_id value.

$hitches = array();



           foreach($dors as $dor) {

                $rigId = $dor['rig_id'];

                $date = strtotime($dor['date']);


                if (empty($hitches)) {

                    $hitches[] = array(
                         'rigId' => $rigId,
                         'startDate' => $date,
                         'dors' => array($dor)
                        );

                } else {

                    $found = false;
                    foreach($hitches as $key => $hitch) {

                        $hitchStartDate = $hitch['startDate'];

                        $dateDifference = abs($hitchStartDate - $date);

                        $isSameHitchTimeFrame = $dateDifference < (Date::DAY * 14);

                        if ($rigId == $hitch['rigId'] AND $isSameHitchTimeFrame) {

                            $found = true;
                            $hitches[$key]['dors'][] = $dor;

                        } 



                    }

                    if ($found === false) {

                        $hitches[] = array(
                         'rigId' => $rigId,
                         'startDate' => $date,
                         'dors' => array($dor)
                        );

                    }

                }
}

This seems to work OK splitting up by rig_id, but not by date. I also think I'm doing it wrong because I need to check the earliest date.

Is it possible at all to do any of this in the database query?

To recap, here is my problem

• I have a list of jobs with all have a rig_id (many jobs can have the same) and a date.
• I need the data to be organised into hitches. That is, the rig_id must be the same per hitch, and they must span a 14 day period, in which the next 14 days with the same rig_id will be a new hitch.

Can someone please point me on the right track?

Cheers