CODEWITHSUNDEEP
phpmysqlsqlselect
VA13
VA13

Reputation: 51

Retrieve from MySQL "most clicked items in the past 6 hours"?

When a user clicks on a link, new data with the following format is stored in a MySQL_DB table called tracking_table: 

id    url                 title             clicktime 
1     http://1.com       title 1        2014-12-07 21:33:53
2     http://2.com       title 2        2014-12-07 21:34:03
3     http://1.com       title 1        2014-12-07 19:30:00
4     http://3.com       title 3        2014-12-07 18:38:47
5     http://1.com       title 1        2014-12-07 22:23:54
6     http://2.com       title 2        2014-12-07 20:17:20
7     http://7.com       title 7        2014-12-07 10:20:12
8     http://1.com       title 1        2014-12-07 21:38:03

How could I display (in PHP) in descending order the top 5 titles that were clicked on over the past 6 hours? Given the data recorded so far (supposing that NOW is 2014-12-07 22.24.00), my result should look like:

#     url             no. of clicks    title
1     http://1.com        4            title 1
2     http://2.com        2            title 2
3     http://3.com        1            title 3

I have created the DB using this command:

CREATE TABLE `tracking_table` (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  url varchar(255) DEFAULT NULL,
  title varchar(255) DEFAULT NULL,
  clicktime timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

Any help would be much appreciated!

Upvotes: 1

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

I would suggest the following:

select tt.title, count(*)
from tracking_table tt
where clicktime >= date_sub(now(), interval - 6 hours)
group by title
order by count(*) desc
limit 5;

This is similar to Mueinik's answer, with one important exception: the where clause. First, this calculation should work and second clicktime is not an argument to a function. The latter means that an index on clicktime could be used. (There is not one in the table definition, but it might be a good idea for this type of query.)

Upvotes: 0

Mureinik
Mureinik

Reputation: 311978

You should group the clicks by url and title, count them and then use a limit clause to retrieve only the top 5:

SELECT   url, title, COUNT(*) 
FROM     tracking_table
WHERE    DATE_DIFF (NOW(), clicktime) * 24 <= 6
GROUP BY url, title
ORDER BY 3 DESC
LIMIT    5

Upvotes: 1

Related Questions