count search keyword via mysql query for relevance in search function

Question

I need to count the repeats of search keyword in searched fields.

For example, if i have table wp_posts like this

   ID       post _content     post_title 
-----------------------------------------------
     1      page page page     page
     2      page test          page
     3      page foo           test

I want result like if any one search for page

ID  total count
1    4
1    2
2    1

Answer 1

try this

SELECT  id ,
                FLOOR(( (length(REPLACE(post_content, '', ''))
                        - length(REPLACE(REPLACE(post_content, ' ', ''),
                                         'page', ''))) ) / length('page'))
                + FLOOR(( (length(REPLACE(post_title, ' ', ''))
                          - length(REPLACE(REPLACE(post_title, '
 ', ''), 'page', ''))) ) / length('page')) "count of 'page'"
        FROM    page_search ;