table with events unixtime to day statistics

Question

I have an on-line dictionary.

All inputs are saved in table "id-word-unixtime"

Now I want to make statistics of how many words were searched in an day, and then make graph or table with that data.

What is a best way to archive this?
Should i make another table for dates or write php script?
I just need basic direction.

Answer 1

You probably want to compute the answer once and then cache the result, to reduce the load on your server. Once the day is over, you only have to calculate all the statistics for it once.

Something important to thing about is when a day begins and ends. If your site has visitors form all over, you should probably use UTC. If 95% of your visitors are from the US, Eastern Time might make more sense.

Answer 2

You're right, it's a very basic SQL query. something like

SELECT word, count(word) FROM table 
WHERE unixtime BETWEEN (starttime AND endtime) GROUP BY word 

starttime and endtime you can calculate in either PHP or mysql

and sure, you will need to write a php script to draw a graph, but it's another question

Answer 3

Many questions there, but the main thing you seem concerned about is getting dates from unixtime.

MySQL has FROM_UNIXTIME().

Something like this should get you started:

  SELECT FROM_UNIXTIME(unixtime,'%Y-%m-%d') as the_date,
         count(word) as word_count
    FROM table
-- add conditions here
GROUP BY 1;

If you have PHP-specific questions regarding data presentation I suggest you open another question.

Answer 4

SELECT COUNT(*), FROM_UNIXTIME(unixtime, '%Y-%M-%D')
FROM table
WHERE unixtime BETWEEN $start AND $end
GROUP BY FROM_UNIXTIME(unixtime, '%Y-%M-%D')

This should give you each day with searches per day. It's quite an expensive search, so you may want to cache it.