OK, I have 2 tables, search id and search. search id would be 1 and search would be what was searched the first time.. search_id - search 1 - brawlsearch1 2 - brawlsearch2 3 - brawlsearch3 ok, basically, I want to list the most popular 10 searches, and also link to them, hopefully someone can help me. I want to list like this: 1 - search1 2 - search2 and also link them... check the image for my table structure etc EDIT: For the links, the link example is like this: http://www.boredombase.com/search/0/52-rya.html 52 = search_id rya = search
You either need to store the number of searches each phrase has in a 3rd column in your table (e.g. NumSearches), or keep adding every search phrase to the table (huge table). Using the first option, you'd need to query "SELECT * FROM `search` ORDER BY `NumSearches` LIMIT 10" Code (markup): .
If you just keep inserting each of the search queries to a new row in the database table, this code will get you the top 10 search queries with the number of times they were searched. $sql = "SELECT search, COUNT(search) as instance_count FROM table GROUP BY search ORDER BY instance_count LIMIT 10"; $result = mysql_query($sql) or die(mysql_error()); while($row = mysql_fetch_array($result)) { $search_keyword = $row['search']; $instance_count = $row['instance_count']; echo $search_keyword . " - " . $instance_count . "<BR>"; } PHP:
"SELECT * FROM `search` ORDER BY `NumSearches` LIMIT 10 ASC" this way will make your search_id going from ascennding
But that'd give you the 10 least popular search terms, so I'd go for SELECT * FROM `search` ORDER BY `NumSearches` DESC LIMIT 10 Code (markup): If I'm on the same wavelength as weknowtheworld, I wouldn't go for the GROUP BY route as that would mean the table would continually grow. I'd check whether the phrase already exists when a search is performed - if not, add it, otherwise increment the corresponding NumSearches value. Pruning rarely used search phrases would help keep things speedy (checking existence of phrases would get consistently slower over time), and don't forget using LIMIT 1 on the initial check - there's no point checking all the other phrases when the match has been found! (Apologies for the late reply, I've been staring at web stats the last few days planning some drastic changes for next year.)