Most popular searches method

[shadowcaster]

Hi everyone. I have a search engine on my website that I wish to know what the most popular searches are. I would like to know if the mysql code below is the correct way of doing a 'most-popular-searches' query. Here is the code including using PHP:

[code]
//Find every unique search
$queryA = mysql_query("SELECT DISTINCT(searches) FROM search_table");

//create temporary table to hold search and number of times searched
$queryB = mysql_query("CREATE TEMPORARY TABLE searches_count (searches VARCHAR(255), count INT);");

//loop through the distinct searches
while ($row = mysql_fetch_array($queryA)){

        //count how many times each search is in table
$queryC = mysql_query("SELECT COUNT(searches) FROM search_table WHERE searches='".$row[0]."'");

        //insert results into temporary table (extracts count from queryC)
while($row2 = mysql_fetch_array($queryC)){
mysql_query("INSERT INTO searches_count VALUES('$row[0]','[$row2[0]');");
}
}

//sort and output the most popular searches
$results = mysql_query("SELECT * FROM searches_count ORDER BY count");
while($row = mysql_fetch_array($results)){
print $row[0]. " " .$row[1]. "<br>\n";
}
[/code]
Am I doing this correctly or is there an easier way?

[btherl]

You can put all the complexity into sql like this:

[code]SELECT searches, count(searches) as count
FROM search_table
GROUP BY searches
ORDER BY count DESC
LIMIT 100[/code]

The "group by searches" takes the place of your "select distinct" .. a "group by" will make the columns you group by distinct.  But when you group results, you need to use an aggregate like count(), sum(), max() on any other values which were not grouped (in this case you just want to count how many times each "searches" appears).

After that, the ordering and limiting is straightforward.  That query will give you the top 100 searches.

[shadowcaster]

That worked brilliantly! Thank you so much!!  :D