getting the total average hits for today (using unix timestamp)

[yjim]

ok, i need help on how i could code this in php.

 

i have a mysql table that stores the user's timestamp when he clicked on the link and it resets every 24 hours with a new timestamp and the hits also will reset.

 

here's an example table:

 

--------------------------------

USER TABLE

--------------------------------

user | timestamp | hits

-------------------------------------------------

user1 1261946304  1

user2 1261944605 2

user3 1261944530 2

user4 1261944470 1

user5 1261944067 2

 

im not sure how to approach this, but how could i get the total average number for the hits in the last 24 hours only?

[mikesta707]

mysql has an avg command for getting averages. an example

$query = "SELECT AVG(hits) as avgHits FROM table";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$avg = $row['avgHits'];

 

Edit: sorry didn't answer the whole question

 

using this AVG command, you could use the timestamps and the greater then operator to get hits from the last 24 hours. something like

$secsInDay = 60*60*24;//60 secs a min * 60 mins an hour * 24 hours a day
$timestamp = time() - $secsInDay;//gets current timestamp and subtracts amount of secs in day, thus 24 hours before

$query = "SELECT AVG(hits) as avgHits FROM table WHERE timestamp > ".$timestamp;
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$avg = $row['avgHits'];

 

Be aware this is untested, but the basic idea is there