views:

626

answers:

4

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????

A: 

Use google analytics :)

Lichtamberg
Nice but I want to show to the visitors the info, and also sort the data by popularity and more things of the kind.....
Jonathan
+2  A: 

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.

zombat
with this query I get the hits sum of the total two weeks.. I need the sum of the first week and then the sum of the second week. I need to use two queries one for each week or there is a better method?
Jonathan
Oh, I see. I would definitely use two queries. I'll edit it a bit.
zombat
A: 

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

Tom Haigh
The question is not how to subtract between variables but how to get the info from database and a better way for doing it...
Jonathan
what? Question 1 is "I'm using the correct math formula??"
Tom Haigh
Ah all right... I didn't get the "so yoy want [ (2000 / 1000) * 100 ] = 200" part... thanks!
Jonathan
+1  A: 

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";
?>
inakiabt
This is working, Thanks!!! But what if I want to get the sum of hits starting from yesterday and not from today. I mean, count two weeks from yesterday and not from today.I replaced "<= date" with "DATE_SUB(date,INTERVAL 1 day)" but its not working correctly... I get the first week hits correctly but then in the second week the function takes a day from the other week.... How can I fix this??
Jonathan
the simple way is changing all "DATE_SUB(CURDATE(),INTERVAL..." with: "DATE_SUB(DATE_SUB(CURDATE(),INTERVAL 1 day),INTERVAL..." :) good luck!
inakiabt