CODEWITHSUNDEEP
phpmysqlsql
StudioTime
StudioTime

Reputation: 23999

Common word count across a table, not just a row

I know I can count common occurrences of words in a string but what's the best solution for mulitple strings? Or in this case, rows in a table?

Example table:

ID    Title
------------
1     red car
2     blue car
3     red bike
4     green car
5     red and green car

I want to return the top 3 common words and their count from column: title e.g.

Word    Count
--------------
car     4
red     3
green   2

I'm using mysql & PHP

Is there an efficient way to do this?

Upvotes: 1

Views: 126

Answers (2)

i'm PosSible
i'm PosSible

Reputation: 1393

use this to fetch a record :

SELECT group_concat(concat(`Title`) separator ' ')
FROM test

see demo

store it value into $text then use,

echo substr_count($text, 'car');
echo substr_count($text, 'red');

for more detail about substr_count() see demo

Upvotes: 0

Andresch Serj
Andresch Serj

Reputation: 37418

That can be done with MySQL using it's string manipulation methods. Using those, you can create a query getting you all "words" from your table rows. However, splitting a string in MySQL is not a trivial problem. Sadly MySQL has no split or explode method. Read about it here.

If you use that query as a subquery, you can use order by and the string length method of MySQL to get an ordered list of words and their occurance.

Your alternative is to iterate over all MySQL Results, filling a local PHP Array with the words you find and keep count. See in this example:

<?php
// connect to db and so fort
...
$wordCounterArray = array();
while(($row = mysqlfetch_assoc($mysqlHandle) !== false ) {
  $words = explode(' ',$row['Title']);
  foreach ($words as $word) {
    if(array_key_exists($word, $wordCounterArray ) {
      $wordCounterArray[$word]++;
    }
    else {
      $wordCounterArray[$word] = 1;
    }
  }
}

Now you still have to sort that array by its values, but i guess you will figure out how to do that yourself with google and the php online documentation right?

Upvotes: 2

Related Questions