Jump to content


Photo

Most popular searches method


  • Please log in to reply
2 replies to this topic

#1 shadowcaster

shadowcaster
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationUK

Posted 26 October 2006 - 02:01 AM

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:

//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";
}
Am I doing this correctly or is there an easier way?
--Lurking out of sight, Night shall fall and feed my frenzy, Beneath an eerie moon a change comes over me--[br]Check out Symphony X (The oddysey album) if you like heavy metal

#2 btherl

btherl
  • Staff Alumni
  • Advanced Member
  • 3,893 posts
  • LocationAustralia

Posted 26 October 2006 - 03:40 AM

You can put all the complexity into sql like this:

SELECT searches, count(searches) as count
FROM search_table
GROUP BY searches
ORDER BY count DESC
LIMIT 100

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.

#3 shadowcaster

shadowcaster
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationUK

Posted 28 October 2006 - 01:21 AM

That worked brilliantly! Thank you so much!!  :D
--Lurking out of sight, Night shall fall and feed my frenzy, Beneath an eerie moon a change comes over me--[br]Check out Symphony X (The oddysey album) if you like heavy metal




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users