PHP SQL Select date ranges, this week, last week and this month

Hi all. I have a table that holds completed surveys. I have made an admin type area that view’s completed surveys, stats and all that good stuff. What I cant figure out is how I can count all surveys that have been submitted this week, all surveys that were submitted last week and all of the surveys that have been submitted this month. I’m am not that familiar with php/sql dates and am drawing blanks. This is what I am working with now.


$sql = <<<SQL
  SELECT * FROM completesurvey WHERE date > DATE_SUB(NOW(), INTERVAL 1 WEEK)
SQL;

if(!$result = $db->query($sql)){
    die('There was an error while running the query [' . $db->error . ']');
}

This is as close as I have been able to get. I would rather do something like this but cant figure it out.


$date = date('W');
$sql = <<<SQL
  SELECT * FROM completesurvey WHERE date = $date
SQL;

So that it pulls all records that fall between Week 34 is from Monday August 19, 2013 until (and including) Sunday August 25, 2013.
And then just - 1 from date to pull last week is this possible?
date in the table is datetime

To get records that match the current week, your query is almost there - you just need to use MySQL’s WEEK() function to get just the week number from the date:


$week = date('W');
$sql = <<<SQL
  SELECT * FROM completesurvey WHERE WEEK(date, 3) = $week
SQL;

note that it’s important to pass 3 as the second argument to WEEK(), as this sets the calculation mode to make it consistant with PHP’s week calculation.

To get the data for the current month, you’ll need the MONTH() and YEAR() functions:


$month = '8';
$year  = '2013'
$sql = <<<SQL
  SELECT * FROM completesurvey WHERE MONTH(date) = $month AND YEAR(date) = $year
SQL;

Thanks fretburner. It works great.