Hey, I have a search feature that searches for words in my database.
I am currently storing all searches that users make in a table called `search`. I need to list the top 10 words that are searched for the most, excluding common words like "the, and, or, etc".
Because the searches users make are stored into a table, it should be fairly easy.
Could anyone give an example of how to do this?

Here's how my sql table looks:

table name: search

columns:
id (autoincrement)
phrase
date
resultsfound
ip

Unfortunately, it's not as easy as you think it will be. Phrases will have to be split into individual word rows by cross joining a makeshift index table and sub-splitting the phrase into parts.

You'll have to do something like the following, which will pull the first 20 words in your search phrase. You can just add unions on the ints table to get more words.

SELECT word, COUNT(1) AS word_count FROM
    (SELECT *,
        SUBSTRING_INDEX(SUBSTRING_INDEX(s.phrase,' ',ints.i),' ',-1) AS word
    FROM (SELECT 1 AS i UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 
        UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 
        UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
        UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20) AS ints
    CROSS JOIN search AS s
    WHERE ints.i BETWEEN 1 and (SELECT 1 + LENGTH(s.phrase) - LENGTH(REPLACE(s.phrase,' ','')))) AS words
GROUP BY word
ORDER BY word_count DESC;

If you want to eliminate common words, you'll need to do this:

CREATE TABLE common_words (word VARCHAR(255) NOT NULL PRIMARY KEY);
INSERT INTO common_words (word) VALUES ('the'), ('a'), ('in'), ('of'), ('to'), ('and'), ('for'), ('is'), ('we'), ('are'), ('that'), ('have'), ('been'), ('etc');

-- Then your query will be altered as follows:

SELECT words.word, COUNT(1) AS word_count, COUNT(c.word) AS common FROM
    (SELECT *,
        SUBSTRING_INDEX(SUBSTRING_INDEX(s.phrase,' ',ints.i),' ',-1) AS word
    FROM (SELECT 1 AS i UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 
        UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 
        UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
        UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20) AS ints
    CROSS JOIN search AS s
    WHERE ints.i BETWEEN 1 and (SELECT 1 + LENGTH(s.phrase) - LENGTH(REPLACE(s.phrase,' ','')))) AS words
LEFT JOIN common_words AS c ON words.word = c.word
GROUP BY words.word
HAVING common = 0
ORDER BY word_count DESC

Note that these may be performance hogs if you have lots of searches... you may eventually want to create a second table with just search words, which will perform exponentially faster in word queries.

Hi

As earlier mentioned, you should create another table for search words..

e.g. "Joomla Web Design" .... should be split into 1. Joomla 2. Web 3. Design..and the resultsfound can be incremented each time...

What should I use to split up a phrase like that and separate each one as it's own word?
And thanks for all the great ideas guys.

EDIT!!!!!

I decided to use this:
$dArray = explode(" ", $var);

This way it will place each word into the array. But how can I then use this to set each word into the database? I'm assuming I use foreach, but i'm not too sure how to set it up. I'll keep working at it until someone is able to respond. Thanks!

ANOTHER EDIT!

Alright! I have figured it out

PHP Code:

    $word = explode(" ", $var);
    $num = 0;
    foreach($word as $key=>$value){
    
        $wordexist = mysql_query("SELECT word FROM searchedwords WHERE word='$word[$num]'");
        $wordcount = mysql_num_rows($wordexist);
        if($wordcount!=0){
        //UPDATE
            $wordget = mysql_query("SELECT * FROM searchedwords WHERE word='$word[$num]'");
            $wordrow = mysql_fetch_assoc($wordget);
            $todayword = $wordrow['today'];
            $totalword = $wordrow['total'];
            $newtoday = $todayword+1;
            $newtotal = $totalword+1;
        
            $updateword = "UPDATE `searchedwords` SET `today`='$newtoday', `total`='$newtotal' WHERE `word`='$word[$num]'";
            mysql_query($updateword);
            $num++;
        }
        else{
            $addone = 1;
            $wordinsert = mysql_query("INSERT INTO searchedwords VALUES ('','$word[$num]','$addone','$addone')");
            $num++;
        }
        
    } 


Did you bother trying the query that I spent half an hour writing for you before deciding to do it another way?

Sure did And thanks for all the time you spent on it. I was working on getting it to work forever and decided to go another way.

Thanks