Querying a mysql database for the last week of data

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Pezmc
Forum Commoner
Posts: 53
Joined: Mon Nov 06, 2006 2:15 pm

Querying a mysql database for the last week of data

Post 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?
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

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