extract most common words in row of database titles

Posted on 2008-11-03
Medium Priority
Last Modified: 2013-12-13
I'm looking for an efficient solution to finding the most common words in a column of a database that comprises of text strings.

The solution would most likely be a combination of a mysql query and probably need some PHP to do some looping on results...

E.g. I have a table with 200,000 rows with a column for "titles" - these are names/titles of photos
I need a way to find the most common words in these 200,000 strings. This script need only run daily via a cron so it has room to be a little inefficient.
Question by:mixart
  • 2

Expert Comment

by:Sander Stad
ID: 22874099
You can use the str_word_count() function in PHP. At this website there are several examples how to search for repeating words: http://us3.php.net/manual/en/function.str-word-count.php.
What this function does it return an array with the words inside it. When you have the query you can loop the array to see if the word is already in your database.
The table with the words could look like this:
Word                        Count
You loop through the array and than increment the count in the table if it already exists and if it doesn't exist add itto the table.
Hope this helps you out a bit

Author Comment

ID: 22874917
This helps a little but I'm not quite there with figuring out the PHP code to do this.

Any chance you could help - if I had an array of strings like this:

$strings = ['The dog and cat run fast','A cat at a bird fast','The fast bird and cat jump','a dog run a cat','The cat fast run dog cat','A horse swim fast in a dog tree'];

Open in new window


Accepted Solution

Sander Stad earned 1500 total points
ID: 22885605
I thought it over and you'd also like to empty the table when the new job starts because you don't want the results from last time to get in the way of the new results.
It would look something like this:

$link = mysql_connect({SERVER}, {USERNAME}, {PASSWORD});
/* Delete all the old entries */
$query = "DELETE FROM wordstatictics";
$results = mysql_query($query, $link);
/*Get all the titles from the database */
$query = "SELECT titles FROM {TABLENAME}";
$results = mysql_query($query, $link);
/* Loop through the results */
while($result = mysql_fetch_array($results)){
  /*Get all the words inside an array */
  $words = array();
  $words = str_word_count($result['title'], 1));
  /* Call the function updateWordStatistics
function updateWordStatistics($wordsArray = array()){
  /*Loop through every word, check if it already exists in the db
    If it doesn exist the count value is incremented by 1. If it 
    doesn't exists it is inserted in the table */
  foreach($wordsArray as $word){
    $query = "SELECT * FROM wordstatictics WHERE word = $word";
    $results = mysql_query($query, $link);
    if(mysql_num_rows($results) == 0){
      $query = "INSERT INTO wordstatistics(word, count) VALUES(\"$word\",1)";
      $results = mysql_query($query, $link);
      $query = "UPDATE wordstatistics SET count = count + 1 WHERE word = $word";
      $results = mysql_query($query, $link);

Open in new window


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month16 days, 9 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question