Search code examples
phpmysqlsqlinstances

how to count the occurence of a word in multiple rows of a mysql db

i am looking for some help with counting the number of occurences in multiple rows of a mysql db.the purpose is for creting a search engine like google but only for my website

the scheme is to display the entry which has the most number of occurences of the word at the very top

for eg . the user searches for the word key.the records are searched and the data is shown in order of the number of occurences

 1.this is a key which is a test key and also the profile key (3 occurences)
 2.key give me a key (2 occurences)
 ....and so on...

is this the best approach?

SELECT (LENGTH(daya) - LENGTH(REPLACE(data, 'key', ''))) / LENGTH('key') AS `count` ORDER BY `count` DESC // data is the column name and key is the word

or is there a better one

Note:i dont want to use Like % %. also i want to show the number of times the word has occured like so the word key has occured 3 times.Could i use count()

Solution

  • You can add the count in ORDER BY clause.

    Try this way:

    SELECT *,(LENGTH(data) - LENGTH(REPLACE(data, 'key', ''))) / LENGTH('key') as cnt
FROM TableName
ORDER BY ((LENGTH(data) - LENGTH(REPLACE(data, 'key', ''))) / LENGTH('key')) DESC

    To get the records of 2 occurences, you can use HAVING clause:

    SELECT *,(LENGTH(data) - LENGTH(REPLACE(data, 'key', ''))) / LENGTH('key') as cnt
FROM TableName
HAVING cnt=2
ORDER BY ((LENGTH(data) - LENGTH(REPLACE(data, 'key', ''))) / LENGTH('key')) DESC