a php and mysql loop challenging me...

[cosmicbdog]

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

[cosmicbdog]

In other words, how does one go about "select entries from last month" instead of "select entries within the last month" AND "select entries from yesterday" instead of "select entries within the last day"?

[Rakuli]

I think if you're using Mysql timestamps, you don't need to use the <= comparison. You would just have to check if the day is the same using only the date part of the stamp.

Code: Select all

WHERE

DATE(site_refer.when) = DATE(DATE_SUB(CURRENT_DATE(), INTERVAL " . $i . " DAY))

ANd then for the month part, just compare the month part of the stamp

Code: Select all

WHERE

MONTH(site_refer.when) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL " . $i . " MONTH))

[cosmicbdog]

so easy... ! a whole rule removed from each... thank you heaps