hi everyone... I have a
php and mysql loop that retrieves the number of hits to a page for each day over the past 7 days, and also for each month over the past 12 months... it 'kinda' works but there is a problem in that it doesn't just pull out the stats that are on the day of today, and then the day of yesterday, but hits within the last 24 hours...
- Code: Select all
//loop for 7 days,counts
for( $i=0; $i<7; $i++ )
{
$dayafter = $i + 1;
// the query
$daySQL = "
SELECT COUNT(*)
FROM site_refer
WHERE thing='" . $row['thing'] . "'
AND (DATE(site_refer.when) <= DATE_SUB(CURRENT_DATE(), INTERVAL " . $i . " DAY))
AND (DATE(site_refer.when) > DATE_SUB(CURRENT_DATE(), INTERVAL " . $dayafter . " DAY))
";
$resthisday = mysql_query($daySQL);
$rowthisday = mysql_fetch_array($resthisday);
// results for each day
echo '<span style="font-size: 7pt;">' . $rowthisday[0] . '</span>, ';
}
echo '<br />';
// loop count for 12 months
for( $i=0; $i<12; $i++ )
{
// this is all the same code as above for the 7 days except months instead of days
$monthafter = $i + 1;
$daySQL = "
SELECT COUNT(*)
FROM site_refer
WHERE thing='" . $row['thing'] . "'
AND (DATE(site_refer.when) <= DATE_SUB(CURRENT_DATE(), INTERVAL " . $i . " MONTH))
AND (DATE(site_refer.when) > DATE_SUB(CURRENT_DATE(), INTERVAL " . $monthafter . " MONTH))
";
$resthisday = mysql_query($daySQL);
$rowthisday = mysql_fetch_array($resthisday);
echo '<span style="font-size: 7pt;">' . $rowthisday[0] . '</span>, ';
}
In its full glory, it produces results like:
eguide(12)
0, 12, 0, 0, 0, 0, 0,
12, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
Thanks for any pointers you can give on this,
B
p.s the field site_refer.when is a timestamp