Page 1 of 1

Querying a mysql database for the last week of data

Posted: Fri Oct 19, 2007 7:14 am
by Pezmc
Hi, I am currently working on a advanced hit counter for mysql query practice and have hit a wall. How can I query for data in the past week? By that I mean monday-sunday not the past seven days.

In my database I have stored
Ip - 80.168.0.3
Date - 2007/10/19
Time - 12:35:57

I am trying to perform a query similar to this to return unique hits during this week

Code: Select all

// How many unique hits today
		$result = mysql_query ("SELECT DISTINCT(ip) FROM stats WHERE date='$serverdate' ORDER BY ip"); 
		$uniquehitstoday = mysql_num_rows($result);
How can I achieve this?

Posted: Fri Oct 19, 2007 8:22 am
by Zoxive
Change your `Date` table to timestamps.

Code: Select all

$D = Date('N'); // Get what Day we are at (1-7) Monday-Sunday

Code: Select all

SELECT * FROM stats WHERE date > SUBDATE(NOW(), INTERVAL $D DAY)

Alternate, All Mysql Calculation..

Code: Select all

SELECT * FROM stats WHERE date > SUBDATE(NOW(), INTERVAL (Date_Format(NOW(),'%w')+1) DAY)
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
http://php.net/date

*All of these are untested.