dates between a certain week

[dannybrazil]

Hello there,

 

I have a DB table where everyday I insert my meetings for the future.

 

I want to ask you guys if anybody can help me with creating some sort of a weekly report.

 

EXAMPLE:

 

I have meeting from the beginning of the year (Mon-Sat)...could be 4-10 meeting per day, so a lot in general.

 

I want them to appear , with sql, under the WEEK they were in for example:

(a year has 52 weeks)

 

WEEK 1 (01-01-2011 -> 08-01-2011)

*M1

*M2

*M3

...

 

WEEK 2 (09-01-2011 -> 16-01-2011)

*M4

*M5

*M6

...

 

 

etc...

 

Is there a way to define the date's range after the week ended automatically?

 

Thanks

 

 

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_week

[WebStyles]

date("W");

will return the week's number.

Since your page is based on weeks, it could make sense to store the week along with the meeting date.

You could run a script, to check the week number of each meeting date:

$weekNr = date("W",strtotime($meetingDate));

and add a WeekNr field to your database to store the returned values, then slightly change you original script to also include the week number when inserting new meetings. That way all you'll need to do in the future is something like:

select * from `table_name` where `weekNr` = '$week'

(weekNr should be defined as an index, so speed up searches)

[Pikachu2000]

You could also just get the week number directly in the query, then display the week's header only when it changes in the results. Off the top of my head, something like this should get you on the right track if you want to do it that way.

 

$query = "SELECT meeting_name, WEEK(date_field, 7) AS wk FROM table ORDER BY date_field ASC";
$result = mysql_query( $query );
$wk = '';
while( $array = mysql_fetch_assoc($result) ) {
     if( $array['wk'] != $wk ) {
          echo $array['wk'] . '<br>';
     }
     $wk = $array['wk'];
     echo $array['meeting_name'] . '<br>';
     // etc.
}

[cunoodle2]

You can use this example that I just wrote for a buddy of mine.  It has a number of different date layouts. ..

 

<?php
$dow = (date('w') + 6) % 7;
$monday = strtotime("-$dow days");

echo "<b>Monday of this week is dated:</b> ";
echo date (' D jS M Y', $monday);               // Mon 29th Aug 2011 
echo "<br />\n";

echo "<b>Monday of last week is:</b> ";
$startTime = mktime(0, 0, 0, date('n'), date('j')-6, date('Y')) - ((date('N'))*3600*24);
$endTime = mktime(23, 59, 59, date('n'), date('j'), date('Y')) - ((date('N'))*3600*24);
echo date (' D jS M Y', $startTime)." - ".date (' D jS M Y', $endTime)."<br />\n";

echo "<b>Monday of 2 weeks ago is:</b> ";
$startTime = mktime(0, 0, 0, date('n'), date('j')-13, date('Y')) - ((date('N'))*3600*24);
$endTime = mktime(23, 59, 59, date('n'), date('j')-7, date('Y')) - ((date('N'))*3600*24);
echo date (' D jS M Y', $startTime)." - ".date (' D jS M Y', $endTime)."<br />\n";

echo "<b>Monday of 3 weeks ago is:</b> ";
$startTime = mktime(0, 0, 0, date('n'), date('j')-20, date('Y')) - ((date('N'))*3600*24);
$endTime = mktime(23, 59, 59, date('n'), date('j')-14, date('Y')) - ((date('N'))*3600*24);
echo date (' D jS M Y', $startTime)." - ".date (' D jS M Y', $endTime)."<br />\n";

echo "<b>Monday of 4 weeks ago is:</b> ";
$startTime = mktime(0, 0, 0, date('n'), date('j')-27, date('Y')) - ((date('N'))*3600*24);
$endTime = mktime(23, 59, 59, date('n'), date('j')-21, date('Y')) - ((date('N'))*3600*24);
echo date (' D jS M Y', $startTime)." - ".date (' D jS M Y', $endTime)."<br /><br />\n";

?>