Jump to content

Most popular searches method


shadowcaster

Recommended Posts

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?
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.