Show results for one week?

I have this piece of code that show the number of pages surfed for the last 7 days:

SELECT vtp_members.id, vtp_members.name, vtp_members.email, count(vtp_tracking.id) surfs
FROM vtp_members, vtp_tracking
WHERE vtp_tracking.credit_members_id=vtp_members.id AND vtp_tracking.action_date > DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY vtp_members.id
ORDER BY surfs DESC

How can I make it count for within a timeframe..say monday to monday?

this is probably better asked in the mysql forum?

let me try asking anotherway , or alternativ; From the previous code:

vtp_tracking.action_date > DATE_SUB(CURDATE(), INTERVAL 7 DAY)

is there away to substitue "DATE_SUB(CURDATE(), INTERVAL 7 DAY)" with a specific date?

I know the action.date format is "2011-03-28 10:40:30"

but saying "2011-03-28 10:40:30 > 2011-03-23 10:40:30" ; doesn't really make sense.

Hi

You can check a date range using BETWEEN.

All the best

Keith

Hi Keith

I tried that, but couldn't get it too work.

If I could use somthing like this: "2011-03-28 10:40:30 > 2011-03-23 10:40:30"

it would be exellent, as I can setup a cron to change the dates!

(maybe the "2011-03-28 10:40:30" could be converted to one number (20110323104030)?

Hi

If you are doing it as a CRON job, what date range are you looking for? For example, are you looking for any date which is the same week as the job is running in?

All the best

Keith

Well, it's a little hard too explain, since I'm pretty new too all this php...

I have a surf script, that add the the time, everytime a member views 1 page.

So the code I posted, returns the name of the surfer and how many pages they've surfed.

Problem is the code will show last 7 days from current date, and not this week. So..

if I could make the codesomthing like this: "2011-03-28 10:40:30 > 2011-03-23 10:40:30",

I could setup a cron to change the date every Sunday at midnight, by simple updating the "LESS THAN" value.

Hope it makes sense

Hi

Think you can do it far more easily than that.

Select all the records where the year and week match the current year and week.

SELECT vtp_members.id, vtp_members.name, vtp_members.email, count(vtp_tracking.id) surfs
FROM vtp_members, vtp_tracking
ON vtp_tracking.credit_members_id=vtp_members.id 
WHERE YEARWEEK(vtp_tracking.action_date = YEARWEEK(CURDATE())
GROUP BY vtp_members.id
ORDER BY surfs DESC

All the best

Keith

It all depends..How is WEEK defined? Is WEEK from monday to sunday or what?

Hi

YearWeek accepts a 2nd optional parameter to define whether Sunday or Monday is the first day of the week (explained in the week function).

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_week

All the best

Keith

I tried, using the code you suggested:

SELECT vtp_members.id, vtp_members.name, vtp_members.email, count(vtp_tracking.id) surfs
FROM vtp_members, vtp_tracking
ON vtp_tracking.credit_members_id=vtp_members.id 
WHERE YEARWEEK(vtp_tracking.action_date = YEARWEEK(CURDATE())
GROUP BY vtp_members.id
ORDER BY surfs DESC

This however did not return any results (and it should). Anyway I can do a test just return the result of YEARWEEK?

Hi

Is it not returning anything or is it giving an error? There is a missing bracket which would result in an error:-

SELECT vtp_members.id, vtp_members.name, vtp_members.email, count(vtp_tracking.id) surfs

FROM vtp_members, vtp_tracking

ON vtp_tracking.credit_members_id=vtp_members.id

WHERE YEARWEEK(vtp_tracking.action_date) = YEARWEEK(CURDATE())

GROUP BY vtp_members.id

ORDER BY surfs DESC

You can put the YEARWEEK into the SELECT clause if you want to check it, but you will also need to put it in the GROUP BY clause.

All the best

Keith