CODEWITHSUNDEEP
phpmysqldatesorting
Jack Hillard
Jack Hillard

Reputation: 596

Need to display mysql table ordered by date but separated by week

I'm using PHP and MySQL. I have a page that displays meetings created by people with a link to view details. Right now I'm using just a simple table to display everything sorted by date. The table in the database is called 'meetings' and there are 3 columns -

'meetingid' (int)

'time' (datetime)

'creator' (text)

My problem is that it looks a little messy and difficult to read when there are quite a few meetings created since they are all in just one big clump. I'd like to split them up by week (starting Monday, ending Sunday - or Sunday-Saturday if that's easier). I've linked to a doc at the bottom showing what I currently have (first page) and something more like what I want (second page). The week labels (ex. September 3rd - September 9th) would need to only go on for as long as there are meetings booked. So, if the latest meeting is October 7th then the last week shown should be 'October 1st - October 7th'. Figuring out how to separate them by month seems easy enough but I can't wrap my head around how to do it by week. I'm assuming that there's some php date function that will help greatly with this but I can't find it. Hoping you all can help.

What is the best way to do this?

I haven't decided yet whether or not I'd want the weeks where there are no meetings to show the week label or not. (Ex. There are no meetings between September 10th - September 16th. - so do or do not show that label.

Link to examples (no need to sign into google) https://docs.google.com/document/d/16cvRfPmovNBsx9QQ0U5qhVoW8bo0xjEABil3wTtEUrA/edit

Upvotes: 1

Views: 224

Answers (2)

codewrangler
codewrangler

Reputation: 350

Without knowing how your data is structured; you could use the week number returned by the date() function to keep track of which week you are in and break it up that way.

$currentWeekNumber = -1;
$rows = array(
    array('id' => 5, 'started_by' => 'Ben', 'when' => '2012-09-06 09:00:00'),
    array('id' => 6, 'started_by' => 'Julie', 'when' => '2012-09-07 18:00:00'),    
    array('id' => 18, 'started_by' => 'Ben', 'when' => '2012-09-18 20:00:00')
);

foreach($rows as $row) {
    $eventSeconds = strtotime($row['when']);

    $rowWeek = intval(date('W', $eventSeconds));

    if( $rowWeek !== $currentWeekNumber && $currentWeekNumber !== -1) {
        echo "----- Week Break ------\n";
    }

    $currentWeekNumber = $rowWeek;
    echo "Meeting #{$row['id']}, started by {$row['started_by']}, occurs at ".strftime('%c', $eventSeconds)."\n";
}

Which produces the following output:

Meeting #5, started by Ben, occurs at Thu Sep  6 09:00:00 2012
Meeting #6, started by Julie, occurs at Fri Sep  7 18:00:00 2012
----- Week Break ------
Meeting #18, started by Ben, occurs at Tue Sep 18 20:00:00 2012

Upvotes: 0

r r
r r

Reputation: 40

Use date("W") to get the week number of the year.Then you can separate your results according to the week number.

Upvotes: 1

Related Questions