The Little Guy Posted May 16, 2012 Share Posted May 16, 2012 I am trying to get the top 10 most popular searches from my database, I have the following: select *, count(*) c FROM searches group by search_query order by c desc This will get the top ten, but it will also get values for people that searched for the same thing 2+ times. So if 2 people search for "Monkey", and one person searches for it 5 times and the other searches for that 3 times, it should show it was searched for 2 times. I am not sure how to format that query.... mysql> explain searches; +--------------+------------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+-------------------+----------------+ | search_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | ip | int(10) unsigned | YES | | NULL | | | search_query | varchar(100) | YES | | NULL | | | search_date | timestamp | YES | | CURRENT_TIMESTAMP | | +--------------+------------------+------+-----+-------------------+----------------+ 4 rows in set (0.00 sec) Link to comment https://forums.phpfreaks.com/topic/262616-unique-search-count/ Share on other sites More sharing options...
Illusion Posted May 16, 2012 Share Posted May 16, 2012 you should count IPs select search_query , count( distinct ip) c FROM searches group by search_query order by c desc Link to comment https://forums.phpfreaks.com/topic/262616-unique-search-count/#findComment-1345993 Share on other sites More sharing options...
The Little Guy Posted May 16, 2012 Author Share Posted May 16, 2012 perfect! Thanks for the help! Link to comment https://forums.phpfreaks.com/topic/262616-unique-search-count/#findComment-1346002 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.