Page 1 of 1

How toMysql Query current week, last week, and last month...

Posted: Thu Jul 06, 2006 2:14 pm
by duckxtales
Hi,
I can't figure out and find much information on making Mysql queries for the following:

Retrieve all rows for the date range of the current week. ex. Since it is Thursday today, I want to know what rows have been created since Sunday of this week.

Basically what I am trying to do is create a reports page for how many orders was made:

- today
- yesterday
- current week
- last week
- last month

Right now i have it like this and I'm not sure if this is correct or not.

$today = mysql_num_rows(mysql_query("SELECT * FROM Orders WHERE ODate = CURDATE()"));

$yesterday = mysql_num_rows(mysql_query("SELECT * FROM Orders WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= ODate"));

$this_week =
$last_week =

$last_month = mysql_num_rows(mysql_query("SELECT * FROM Orders WHERE DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= ODate"));

Any help would be great..thanks

Posted: Fri Jul 07, 2006 5:47 am
by Oren
Edit: Forget everything I said and see jamiel's post below.

Maybe MySQL has special functions just for doing that, but I've never heard of such, so here is how I'd do it...
If you want all the records of this week:

Code: Select all

$start = strtotime('last sunday');
$start = date('Y-m-d', $start);

/* If today is Sunday */

if (date('w') == 0)
{
	$start = date('Y-m-d');
}


$end = strtotime('next sunday');
$end = date('Y-m-d', $end);

$this_week = sprintf(
			"SELECT * FROM `Orders`
			 WHERE `ODate` BETWEEN '%s' AND '%s'",

			$start, $end
		);

Posted: Fri Jul 07, 2006 8:04 am
by jamiel
This week:

Code: Select all

SELECT * FROM orders WHERE WEEK(CURDATE()) = WEEK(Odate)
Just Last Week:

Code: Select all

SELECT * FROM orders WHERE WEEK(Odate) = (WEEK(CURDATE()) - 1)
Both last and this week:

Code: Select all

SELECT * FROM orders WHERE WEEK(Odate) >= (WEEK(CURDATE()) - 1)

Posted: Fri Jul 07, 2006 8:31 am
by Oren
That's really great jamiel, I'm a real n00b when it comes to MySQL.
Although I've never seen this thing done before this way, I was pretty sure MySQL had a better way to do it, but since I'm a n00b when it comes to MySQL as I mentioned before, I searched for a PHP way so I could help duckxtales with his query.

I'm glad you posted that, 'cause I hoped someone would reply with a better way to do it... My way is too long and clumsy :P

Posted: Fri Jul 07, 2006 8:52 am
by feyd
jamiel's SQL logic will need some (minor) tweaking to add awareness of the year as well.

Posted: Fri Jul 07, 2006 9:02 am
by Oren
Yep that's right since for example, both:

Code: Select all

SELECT WEEK('2000-01-01')
and:

Code: Select all

SELECT WEEK('1900-01-01')
will return 0, and therefore some checks need to be done.

Also note that this:

Code: Select all

SELECT * FROM orders WHERE WEEK(Odate) >= (WEEK(CURDATE()) - 1)
will return last week, this week AND any future dates.