Search for most searched for words

[TheDust]

I know there has to be a way to do this, but I'm clueless as to how to go about it because I've never tried it...

If I make a record in the database for every text search done on our site, how can I then query what the most searched for words are later on? How can you search the MySQL database for the words that appear most often?

 

[cLFlaVA]

possibly something like:

  select distinct search_word
       , count(1) as total
    from search_table
group by search_word
order by total desc
   limit 5

5 most popular searches.

*cLFlaVA
----------------------------
[tt]a frickin' twelve-gauge, what do you think?[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[TheDust]

OK... makes sense. Would that work on values with multiple words inside of them? For instance...

In the database I have the "chicken horse dude". Would I be able to query a search for just the word "horse" using the method you're describing? Or do I instead have to separate the words upon submission and place them in separate fields?

 

[cLFlaVA]

Hmm

I think you'd want to split sentences and store each word in the sentence as a separate record...

Wouldn't you rather keep the sentences together though?

*cLFlaVA
----------------------------
[tt]a frickin' twelve-gauge, what do you think?[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[TheDust]

It depends on how the search function will act... I would include anything in quotes as a sentence, certainly, but if someone searches many terms in one search, we may want to break those terms up. Thanks, cLFLAVA... you da man.