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) Quote 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 Quote 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! Quote Link to comment https://forums.phpfreaks.com/topic/262616-unique-search-count/#findComment-1346002 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.