Posted by NC on 09/14/05 03:04

toedipper wrote:
>
> can anyone tell me how to extract all data from the start of the
> current week?

The easiest is to compute the date for the beginning of the current
week in PHP and pass it to MySQL:

$bow = date('Y-m-d', strtotime('last Sunday'));
// alternatively:
// $bow = date('Y-m-d', strtotime('last Monday'));
$query = "SELECT * FROM the_table WHERE date => '$bow'";
mysql_query($query);

> Is there any way in Mysql to work out the start of the
> current week?

Not that I know of, but you can use the WEEK() function to
see whether your date belongs to the same week than the current
date:

SELECT * FROM the_table WHERE WEEK(date) = WEEK(CURDATE());

For more information, see MySQL documentation:

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

> The current month?

Same general idea, slightly different implementation...

Option 1 (compute limiting date in PHP):

$bom = date('Y-m-01', time());
$query = "SELECT * FROM the_table WHERE date => '$bom'";
mysql_query($query);

Option 2 (SQL-only):

SELECT * FROM the_table WHERE MONTH(date) = MONTH(CURDATE());

Cheers,
NC

[Back to original message]