I am doing a site of articles using php and mysql... I am storing view rate and like rate of
an article in database in different field... Now i want to select most popular article of a
day (means today), month and year based on view rate and like rate... Is there any algorithm
for finding most popular article using these data....?
Can someone help me?
To get Statistics Monthly statistics for year 2012
select page_id,sum(views) from tableName
where year(recordDate)='2012'
group by page_id, group by month(recordDate)
You could save the likes as single objects in the database like in this kind of table:
CREATE TABLE `like` (
`article_id` INT UNSIGNED NOT NULL ,
`user_id` INT UNSIGNED NOT NULL ,
`liked_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY ( `article_id` , `user_id` ) ,
INDEX ( `liked_at` )
);
Every time a user likes an article you inside a row (article_id, user_id, NOW()) into this table.
To get the top article, just select any tuples from today or this month or this year, group by them by article_id, count(article_id) as score, and finally order by this score.
To get today's 5 most liked articles:
SELECT article_id, count(user_id) as score
FROM like
WHERE liked_at > NOW() - 86400
GROUP BY article_id
ORDER BY score DESC
LIMIT 5
WHERE liked_at > NOW() - 86400 only selects likes that are not older than 24h.
If you know that you will only want today's, this month's and this year's like count, you can have a table like
CREATE TABLE `article` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`likes_today` INT NOT NULL DEFAULT '0',
`likes_this_month` INT NOT NULL DEFAULT '0',
`likes_this_year` INT NOT NULL DEFAULT '0'
);
For every like you increment the article's likes_today, likes_this_month and likes_this_year by one. Once a day you set likes_today to 0 for all articles. Once a month you set likes_this_month to 0 for all articles.
Today's 5 most liked article:
SELECT id, likes_today
FROM article
ORDER BY likes_today DESC
LIMIT 5
Beware that I used two different meanings for today. In the first example today means not older than 24 hours. In the second example today means since last reset.
SELECT title FROM today WHERE viewed DESC ./day
SELECT title FROM month WHERE viewed DESC ./day
SELECT title FROM year WHERE viewed DESC ./day
cinda explains what im trying to say :D