Forum Moderators: open
I'm new to this forum, hope to get some help :)
I'm fairly new to MySQL scripting, and I'm in need of help.
Basicly I have a db, where the visitors from my site are stored. Their IP, referrer, os, browser, date visited, etc.
Now after reading some posts in this forum I made a little script to show how many visitors came LAST week
$lweek_command='SELECT COUNT(*) AS total FROM visits WHERE YEARweek( DATE ) = YEARweek(CURRENT_DATE - interval 7 DAY)';
$lweek_query=mysql_query($lweek_command, $connect);
$fetch_lweek=mysql_fetch_array($lweek_query);
$lastweek=$fetch_lweek['total'];
THIS week
$week_command='SELECT COUNT( * ) AS total FROM visits WHERE YEARweek( DATE ) = YEARweek( CURRENT_DATE )';
$week_query=mysql_query($week_command, $connect);
$fetch_week=mysql_fetch_array($week_query);
$thisweek=$fetch_week['total'];
It works perfectly, but the thing is, that MySQL shows from Sunday to Sunday. I need it to show Monday to Monday. I tried adding -1 DAY, but I get a MySQL error.
Any ideas?
[edited by: CodilX at 11:04 am (utc) on June 10, 2007]
YEARWEEK( DATE, 1 )
I tried it, but instead of showing this weeks visitors from monday (today) it shows me the last weeks visitors
DATE is the name of a field in my db, yyyy-mm-dd format
SELECT COUNT( * ) AS total FROM visits WHERE YEARWEEK( DATE, 1 ) = YEARWEEK( CURRENT_DATE, 1 )
it works perfectly with This Weeks visitors, but when I add this to the Last Weeks visitors, it doesn't change anything
SELECT COUNT(*) AS total FROM visitors WHERE YEARweek( DATE, 1 ) = YEARweek(CURRENT_DATE, 1 - interval 7 DAY)
SELECT COUNT(*) AS total FROM visitors WHERE YEARWEEK( datetime_column, 1 ) = YEARWEEK(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY), 1)
There may be a more elegant way to do it but if I had your problem I'd use PHP's strtotime() to get "last monday"
$lastmonday = strtotime("last monday");
$twomondaysago = $lastmonday - 60*60*24*7;
$query = "... yourdate >= DATE_SUB(".date($lastmonday).", INTERVAL 7 days) and yourdate <= ".date($lastmonday)." ... ";
Repeat as necessary for other weeks ...
p.s. Sorry if the above doesn't work, I haven't tested it ... just wanted to get you thinking about going about it in a more general way by using php to get specific dates ...
[edited by: physics at 12:08 am (utc) on June 14, 2007]
Hi CodilX and welcome to WebmasterWorld.com!
as i didn't notice or forgot to offer:
welcome WebmasterWorld, codilx!
glad we could help...
