I have a "like" system on a community forum. All internal likes are stored in a database called log_like.
CREATE TABLE `log_like` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_member` mediumint(8) NOT NULL,
`id_message` int(10) NOT NULL,
`id_poster` mediumint(8) NOT NULL DEFAULT '0',
`time` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `id_member` (`id_member`),
KEY `id_message` (`id_message`)
) ENGINE=MyISAM;
I need to get the last month most liked member ID's (id_poster field in the DDL above). So I probably need to GROUP and COUNT how many times the same "id_poster" appears in records of the past month. I want to LIMIT it to the 10 most liked. The field "time" stores the unix time for reference.
Unfortunately, I have no idea how to achieve this. Can anyone help?
So, here is what I ended up with:
SELECT
id_poster,
COUNT(1) AS like_count
FROM
log_like
WHERE
time BETWEEN UNIX_TIMESTAMP('2012-11-01') AND UNIX_TIMESTAMP('2012-12-01')
GROUP BY
id_poster
ORDER BY
like_count
DESC
LIMIT 10
This way I get the past month (November 2012) top result, instead of the past 30 days from now as suggested before. (Big thanks goes to Barmar for all the work)