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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
duckxtales
Forum Newbie
Posts: 1
Joined: Thu Jul 06, 2006 2:12 pm

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

Post 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
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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
		);
Last edited by Oren on Fri Jul 07, 2006 8:21 am, edited 1 time in total.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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)
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

jamiel's SQL logic will need some (minor) tweaking to add awareness of the year as well.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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.
Post Reply