Querying a mysql database for the last week of data

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

	// 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?

uidzer0b

SELECT DISTINCT(ip) FROM stats WHERE date BETWEEN '2007-10-01' AND '2007-10-07'

Pezmc

That is not very helfull at all, it needs to be variable (take 7 days). Anyone else?

mikell

Assuming monday to be the first day of the week, the following will give you the dates of monday and sunday of last week.

//1-6 (mon-sun)
$today_numeric = date("N");

$days_since_monday_of_last_week = $today_numeric + 6;
$days_since_sunday_of_last_week = $today_numeric;

$start_date = date("Y-m-d", strtotime("-$days_since_monday_of_last_week day"));
$end_date = date("Y-m-d", strtotime("-$days_since_sunday_of_last_week day"));

I only tested it using todays date, but I'm pretty sure it should work on any date.

Bozebo

EDIT: Oops! I didn't realise you meant the actual weak and not the last seven days 🙁

(reply kept for.. whatever) :
You should have made a timestamp field when you first made the table >_>

then it could be:

$time = 604804; //number of seconds in a week
$query = "SELECT * FROM table WHERE timestamp > ". time()-$time;

But, because you don't have a timestamp field you can try what mikell said and use strtotime, to get your stored value into a timestamp. Or you could make a new field in the table, loop through it converting the date into the timestamp for every record then delete the data one (or leave it if you want) and use the timestamp method

uidzer0b

Pezmc wrote:
That is not very helfull at all, it needs to be variable (take 7 days). Anyone else?

Then maybe the question you post should have included "how do I use interpolation in PHP"

halojoy

Bozebo wrote:
But, because you don't have a timestamp field you can try what mikell said and use strtotime, to get your stored value into a timestamp.

Or you could make a new field in the table, loop through it converting the date into the timestamp for every record then delete the data one (or leave it if you want) and use the timestamp method

It is mostly much much better to store TimeStamp = the value of function [man]time/man

Because this is the base of many PHP functions.
Then is not too difficult to use simple numbers to determine what you need.

Depending on if want Mon-Sun or Sun-Sat week, you will have to use different method.
But in [man]date/man you have several format options to extract Day of Week and week number.

N	..... ISO-8601 numeric representation of the day of the week 
        (added in PHP 5.1.0)	1 (for Monday) through 7 (for Sunday)

w	..... Numeric representation of the day of the week	
        0 (for Sunday) through 6 (for Saturday)

W	..... ISO-8601 week number of year, weeks starting on Monday 
        (added in PHP 4.1.0) Example: 42 (the 42nd week in the year)