Mysql Query Current Week, Last Week, and Current Month

[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

[coopster]

mysql_num_rows() [php.net] is only going to return the number of rows in the result set. Are you wondering how to process the result set? There is a thread in our PHP Forum Library [webmasterworld.com] that describes the Basics of extracting data from MySQL [webmasterworld.com].

[duckxtales]

I'm not trying to extract the data in the array. I just want to be able to count how many rows there are in a specific timeframe. I want to know how many rows were created this current week, last week, and last month. The thing we have to account for is not include the previous X amount of days starting from today. Let's say I want to know how many orders weree made last month in June. How would i do this? And lets say I want to know how many orders were made last week, not 7 days before..

[coopster]

OK, that clears it up. It is the statements you need. The easiest way to do this would be to use MySQL Date and Time Functions [dev.mysql.com].

-- 
-- current week (starting with Sunday) 
-- 
SELECT 
 COUNT(*) AS rows  
 FROM Orders  
 WHERE YEARWEEK(ODate) = YEARWEEK(CURRENT_DATE) 
; 
-- 
-- last week (starting with Sunday) 
-- 
SELECT 
 COUNT(*) AS rows  
 FROM Orders 
 WHERE YEARWEEK(ODate) = YEARWEEK(CURRENT_DATE - INTERVAL 7 DAY) 
; 
-- 
-- last month 
-- 
SELECT  
 COUNT(*) AS rows  
 FROM Orders 
 WHERE 
 SUBSTRING(ODate FROM 1 FOR 7) =  
 SUBSTRING(CURRENT_DATE - INTERVAL 1 MONTH FROM 1 FOR 7) 
;

[duckxtales]

thanks coopster... one last question. I wan't to fully understand the functions here. I get the first two where it returns the week # in a given year and mysql will search within that week.

But as for:

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

I don't understand FROM AND FOR syntax used here.

[coopster]

It is just standard SQL syntax. There are alternative syntaxes, such as MySQL's alternates:

SUBSTRING(str,pos) 
SUBSTRING(str FROM pos) 
SUBSTRING(str,pos,len) 
SUBSTRING(str FROM pos FOR len)

The forms without a

len

argument return a substring from string

str

starting at position

pos

. The forms with a

len

argument return a substring

len

characters long from string

str

, starting at position

pos

. The forms that use

FROM

are standard SQL syntax. It is also possible to use a negative value for

pos

. In this case, the beginning of the substring is

pos

characters from the end of the string, rather than the beginning. A negative value may be used for

pos

in any of the forms of this function.

Or are you asking about what the substring is returning? Well, let's say your date was '2006-07-10'. Then the WHERE clause would be using only the first 7 positions, or '2006-06', the year and month portion of the dates being compared. Cool, eh?

[FATAtama]

I want to do the same thing but instead of returning the count in my result set i want to return the values from the rows that fallinto the dates selected.