php and mysql date stuff

[ikaris69]

so i have this database full of records, each one has a column that has a mysql date timestamp on it.

what i want to do is from php be able to get a count for the last month, 2 months ago, 3, and so on.

im horrible with date stuff.

thanks

jason

[SambaNeko]

Use queries with MySQL's Date and Time Functions...

SELECT * FROM myTable WHERE MONTH(myTimeStamp) > MONTH(CURDATE())-6 AND YEAR(myTimeStamp) = YEAR(CURDATE())

This query will get all rows where myTimeStamp is the same year, and within 6 past months of the current date. To get a count of these rows, rather than the rows themselves, change the query to:

SELECT COUNT(*) AS 'referringName' FROM myTable WHERE MONTH(myTimeStamp) > MONTH(CURDATE())-6 AND YEAR(myTimeStamp) = YEAR(CURDATE())

The "AS 'referringName'" part is so that when you get your result in PHP, you can use mysql_fetch_assoc and call it by $sql_row['referringName'].

[ikaris69]

awesome

now say i just want to get the count of anything of the month from 5 months ago.

would i do something like this?
SELECT * FROM myTable WHERE (MONTH(myTimeStamp) > MONTH(CURDATE())-6 and MONTH(myTimeStamp) < MONTH(CURDATE())-5) AND YEAR(myTimeStamp) = YEAR(CURDATE())

[SambaNeko]

Right idea, but you'd be one month off: that'd get the rows for the month 6 months ago. So just change "-6" to "-5" and "-5" to "-4".

[ikaris69]

now say its january and i go back 5 months. with that wouldn't the year be wrong on it? how would i get around that?

[SambaNeko]

True - try exploring the various functions on the MySQL page I linked. Here's an improved query that doesn't rely on equivalent years...

SELECT * FROM myTable WHERE (myTimeStamp BETWEEN DATE_SUB(CURDATE(),INTERVAL 5 MONTH) AND DATE_SUB(CURDATE(),INTERVAL 4 MONTH))

If you know the exact date range you want, you could always just use exact dates:

SELECT * FROM myTable WHERE (myTimeStamp BETWEEN '2009-03-01' AND '2009-03-31')