MySQL / PHP: Date functions for page view statistics and popularity...

Question

Hi, I have a table with number of page views per day. Something like this:

+------+------------+------+
|  id  | date       | hits |
+------+------------+------+
| 4876 | 2009-07-14 | 4362 |
+------+------------+------+
| 4876 | 2009-07-15 | 1324 |
+------+------------+------+
| 7653 | 2009-06-09 | 5643 |
+------+------------+------+

I need to create a function that compares the total hits between last two weeks and get the porcentage of change (popularity).

If the first week I have 1000 hits and the second week 2000 hits I have +200% of popularity

[ (1000 * 2000) / 100 ] = 200

If the first week I have more hits than in the second week the popularity will be in minus (-80% for example)

I have some questions:

1) I'm using the correct math formula??

2) How I select in MySQL the last two weeks from now?

3) Its possible that the first week we have hits, but the second week no hits at all so the table row will be empty for those dates or those specific days that there were no hits.. Is this a problem?

4) I'm sure there is a better way for doing this kind of statistics.. Some better ideas????

Answer 1

Use google analytics :)

Answer 2

1) Not quite. I think you want the percentage change. Going from 1000 hits to 2000 hits is an increase of 100%, not 200%. You want ((2000 - 1000)/100);

2) Two weeks ago: SELECT SUM(hits) as s FROM tbl WHERE date>=NOW() - INTERVAL 2 WEEK AND date<NOW()-INTERVAL 1 WEEK

One week ago: SELECT SUM(hits) as s FROM tbl WHERE date>=NOW()-INTERVAL 1 WEEK

3) Not with the query above. Any date gaps will simply not be part of the SUM().

4) This method seems pretty good to me actually. It should get the job done.

Answer 3

If the first week I have 1000 hits and the second week 2000 hits I have +200% of popularity

Surely in that case you have an increase of 100% ?

changeInHits = hitsThisMonth - hitsLastMonth
changeAsPercentageOfLastMonth = 100 x changeInHits / hitsLastMonth

so you want

[ (2000 / 1000) *  100 ] = 200

You might be able to write a query something like this (untested so probably wrong):

SELECT YEARWEEK(date) as weekno, sum(hits) as thisweek, 
   ( SELECT SUM(hits) FROM table WHERE YEARWEEK(date) = weekno - 1 ) as lastweek,
   IF (lastweek > 0, thisweek / lastweek * 100, 0) as increase
FROM table
GROUP BY weekno

although that would be every one week

Answer 4

Use this:

<?php
function fetch($sql)
{
    $query = mysql_query($sql);
    return mysql_fetch_assoc($query);
}

function calculate($week1, $week2)
{
    if ($week1 == 0)
    {
        $week1 = 1;
    }
    return ($week2 / $week1) * 100;
}

$q1 = "SELECT SUM(hits) as hits FROM table WHERE DATE_SUB(CURDATE(),INTERVAL 1 week) <= date";
$first_week_hits = fetch($q1);

$q2 = "SELECT SUM(hits) as hits FROM table WHERE DATE_SUB(CURDATE(),INTERVAL 2 week) <= date";
$second_week_hits = fetch($q2);

$percent = $str_percent = calculate($first_week_hits['hits'], $second_week_hits['hits']);
if ($percent > 0)
{
     $str_percent = '+'.$percent; // :)
}
echo "I have ".$str_percent."% of popularity";
?>