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
How toMysql Query current week, last week, and last month...
Moderator: General Moderators
-
duckxtales
- Forum Newbie
- Posts: 1
- Joined: Thu Jul 06, 2006 2:12 pm
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:
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
This week:
Just Last Week:
Both last and this week:
Code: Select all
SELECT * FROM orders WHERE WEEK(CURDATE()) = WEEK(Odate)Code: Select all
SELECT * FROM orders WHERE WEEK(Odate) = (WEEK(CURDATE()) - 1)Code: Select all
SELECT * FROM orders WHERE WEEK(Odate) >= (WEEK(CURDATE()) - 1)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
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
Yep that's right since for example, both:
and:
will return 0, and therefore some checks need to be done.
Also note that this:
will return last week, this week AND any future dates.
Code: Select all
SELECT WEEK('2000-01-01')Code: Select all
SELECT WEEK('1900-01-01')Also note that this:
Code: Select all
SELECT * FROM orders WHERE WEEK(Odate) >= (WEEK(CURDATE()) - 1)