Help selecting the most often entererd word

jasonyoung

Hello. I have a database of questions, similar to a FAQ DB. What I would like to do is provide a "statistics" page where I display the top 5 most entered words.

Like this:

Q1: What color is the sky?
Q2: Is the sky blue?
Q3: Why is the sky blue?
Q4: What makes the sky appear blue?

In the examples above I entered the word "sky" 4 times. Obviously I'm not interested in how many times words like "what", "why" and "is" are entered, so I'd need to make a filtered list. I thought I'd maintain a separate file with a list of words I do NOT want to be factored into the calculations.

So, here's my question: How the hell do I do this? Nothing I've tried scratches the surface, so I must be going about this all wrong.

All help is greatly appreciated!!!

banzaimonkey

How you'd do this would depend on the size of the database.

If you have a very large database then you'd want to compile (by way of an automated algorithm) several lists, one being a list of most common words (which would not be included in the search results) and another including common uncommon words (i.e. words like "sky" and "blue" that show up relatively frequently).

If you have a small database, you can make a list of included or discluded words manually.

As for how to get the words in the string, you'd first want to trim() the punctuation, normalize the capitalization using strtolower(), explode() the string, and then process or compare the words to your lists.

In terms of comparing, you'll probably want to process the exploded array against your disclude list to remove any unwanted terms, and then add the remaining words to your database (either add if they don't exist or increment the count if they do). To do this, you can probably use a simple foreach nested structure to iterate through your exploded array, and then iterate through your discluded words list (or vice versa). I think either way, your efficiency ends up being (count(discluded)*count(exploded)).

There are obviously going to be some more and less efficient ways of doing the comparison as well. If you have a smallish database, having your include / disclude lists sorted in order of most frequent to less frequent will help you keep things running quickly. If you have a very large database, you'd probably want to create multiple indexes that break down every letter to decrease search time. I'm sure there are some methods of string comparison that are significantly faster than others. Maybe someone else can give you more info.

jasonyoung

Thanks a bunch. This got me started, but the results are not always consistant. For example, I set up a test page with a static question to enter ($string). When I execute the page, only portions of $string get inserted.

$string = "If an SLA provides 2 analog ports how many analog ports will an IP SLA provide?";
$charlist = array("`","~","!","@","#","$","%","^","&","*","(",")","_","=","+","\\","|","]","}","[","{","'","\"",";",":","/","?",".",">","<");
$singleChar = array(" a "," b "," c "," d "," e "," f "," g "," h "," i "," j "," k "," l "," m "," n "," o "," p "," q "," r "," s "," t "," u "," v "," w "," x "," y "," z ");
$charlistStripped = str_replace($charlist, " ", $string);
$singleCharStripped = str_replace($singleChar, " ", $charlistStripped);
$singleCharStrippedLower = strtolower($singleCharStripped);
$trimmed = trim($singleCharStrippedLower," ");
$exploded = (explode(' ', $trimmed));



$a = $exploded;
	foreach ($a as $v) {
		$sqlQuizFrequencyCount = "SELECT frequency FROM quizFrequency WHERE word LIKE '$v'";
		$sqlQuizFrequencyCountResult = mysql_query($sqlQuizFrequencyCount) or die(mysql_error());
		while ($row = mysql_fetch_array($sqlQuizFrequencyCountResult))
		{ 
			$frequencyFound = $row['frequency'];
		}

if (is_null($frequencyFound)) {
	$sqlQuizFrequencyInsert = "INSERT INTO quizFrequency (word) VALUES ('$v')";
} else {
	$frequencyFound ++;
	$sqlQuizFrequencyInsert = "UPDATE quizFrequency SET frequency = $frequencyFound WHERE word = '$v'";
}
	$sqlQuizFrequencyInsertResult = mysql_query($sqlQuizFrequencyInsert) or die(mysql_error());
}

	echo "Done !!";

When I execute this page I get:

uid | word | frequency
1 if 1
2 an 2
3 sla 2
4 provides 1
5 2 1
6 analog 2
7 ports 2
8 how 1
9 many 1

But my string was If an SLA provides 2 analog ports how many analog ports will an IP SLA provide? It's missing the words will, ip and provide. Any ideas? I know my code is less than efficient, so any rewriting you can offer will be appreciated

Thanks

Installer

I think the problem might be that some of the words aren't in the table when the script starts. Go through the script and figure out what will happen when such a word is encountered. Anyway, I think this will work:

$string = "If an SLA provides 2 analog ports how many analog ports will an IP SLA provide?"; 
$ignore_list = array('what', 'if');  // From database, datafile, or other. 
$charlist = array("`","~","!","@","#",'$',"%","^","&","*","(",")","_","=","+","\\","|","]","}","[","{","'",'"',";",":","/","?",".",">","<"); 
$string = str_replace($charlist, " ", $string);
$string = trim(strtolower($string), " "); 
$word_array = explode(' ', $string); 

foreach ($word_array as $word) { 
    if (((strlen($word) < 2) && !ctype_digit($word)) || in_array($word, $ignore_list) {
        continue;
    }
    $sqlQuizFrequencyCount = "SELECT frequency FROM quizFrequency WHERE word LIKE '" . $word . "'"; 
    $sqlQuizFrequencyCountResult = mysql_query($sqlQuizFrequencyCount) or die(mysql_error()); 
    if (mysql_num_rows($sqlQuizFrequencyCountResult) != 0) {
        $sqlQuizFrequencyInsert = "UPDATE quizFrequency SET frequency = frequency + 1 WHERE word = '" . $word . "'";
    } else {
        $sqlQuizFrequencyInsert = "INSERT INTO quizFrequency (word) VALUES ('" . $word . "')";
    }
    $sqlQuizFrequencyInsertResult = mysql_query($sqlQuizFrequencyInsert) or die(mysql_error());
}

echo "Done !!";

Of course, there are other checks that need to be added. For instance, this code will add "George's" as "george", "didn't" as "didn", and "$2.65" as "2" and "65". There will be many more problem strings that need to be filtered out, I'm sure.

bradgrafelman

Instead of doing a SELECT and then UPDATE/INSERT accordingly, you could just make the word column UNIQUE and then do a simply INSERT ... ON DUPLICATE KEY UPDATE ... type of query.

Otherwise, you probably have a race condition going on.

jasonyoung

Thanks everybody. Can't use the ON DUPLICATE stuff because my DB is 4.0.24 and I have zero control of that.

bradgrafelman

jasonyoung wrote:
Can't use the ON DUPLICATE stuff because my DB is 4.0.24 and I have zero control of that.

Sure ya do, find a decent host! :p

jasonyoung

Yeah, I guess. But in the mean time, you guys got me pointed in the right direction. Now the only thing I need to do is filter out some words. I think it's easiest if I maintain a mini "dictionary" of words I want omitted. Figured I could do a str_replace and insert whitespace which will get stripped in another cleaning pass. Is it as easy as creating a text file and calling it with an include statement? Cuz I tried that but it just echoes. Any ideas?

HalfaBee

I would just have a file with words on seperate lines.
You can then read it with file() and you have an array of words.
Something like this.

 $list = preg_replace( "#^(.*)\n#",' $1 ',file( 'words.txt' ) );
$text = str_ireplace( $list , ' ' , $text );

banzaimonkey

Hm... I'd suggest a CSV (comma-separated values) file in conjunction with fgetcsv(). If you have complex strings (with punctuation, etc.) this could get tricky to maintain, but if you just have words it should be very easy to implement.

The other option I'd recommend is to create a table in your database to check for word-matching. That's a bit more tedious / difficult to edit manually, but also probably more efficient if you have a large or complex list.

HalfaBee

Why would having the list in a DB be more efficient than a flat file?

two lines of code and the words were removed (could have been one line)
This involved reading one file, probably quicker than creating the connection to the DB.

The flat file is easy to maintain, just add new words or delete lines not required.

MYSQL is efficient for doing searches etc, but just for containing a list of words it is not the way to go IMO.

banzaimonkey

HalfaBee wrote:
Why would having the list in a DB be more efficient than a flat file?

I suggested a database might be more efficient if you have a large or complex list.

The database can hold metadata in a nicely organized fashion, and will be more efficient than a flat file once the flat file gets to be a certain size and has a high volume of traffic or usage. Also, manipulating and organizing the data in a database via algorithms is much easier than manipulating the data in a flat file.

HalfaBee

A list of words doesn't need organising, if you add a word to a list of useless words it is unlikely you want to remove it or modify it.

Umm, words don't tend to work via algorithims.

A flat file is always quicker than a DB call if the call is looking for all the rows.

banzaimonkey

HalfaBee wrote:
A list of words doesn't need organising, if you add a word to a list of useless words it is unlikely you want to remove it or modify it.

A flat file is always quicker than a DB call if the call is looking for all the rows.

If you want to iterate through a list most efficiently, you want the most commonly "hit" words at the top and the rest in descending order. You won't know which words occur most frequently unless you tag them with metadata to keep track. In doing so, you can avoid having to iterate through the entire list because chances are, the word you're looking for is near the top or not there at all. Once you find it, you break the routine that's checking against the list.

Alternatively, if you want to sort and search alphabetically, it's going to be easier to do that with a database because you can start 80% of the way down the list at S-words if you want to. Unless you're going to keep track externally of file pointers with a flat file system, I don't know how you'd implement that.

Umm, words don't tend to work via algorithims.

Google doesn't compile their search indexes manually. They use algorithms. There's no reason you can't use algorithms to organize words.

Again, like I said, it's only going to matter if you have a huge list of words. Google's algorithms would be pointless for a small-scale job. The reason they have to use algorithms is because the volume of sites in their index and the volume of search traffic is way too high to monitor by hand. They'll pay special attention to Google Bombs occasionally, but they just tweak the algorithms; they don't go through and manually remove them.

jasonyoung

So now when I do the str_replace I get a wierd result.

Lets say my "to be deleted" dictionary file contains "be"
Then say I post the word "between"
After scrubbing the POST MySQL only gets passed "tween"

So what gives? I tried looking for a different replace function but they all get really technical and I think my trouble may be solved with a simple tweak.

HalfaBee

you need to replace the word with a leading and trailing space.
you probably need to add a space before and after newlines and one at the begining of the paragraph.

If you copied my code above, the preg_replace did have a leading/trailing space.