how to count the matched keywords using select in mysql codeigniter php?

Question

how to count the matched keywords using select in mysql codeigniter php? here is my table

Ex. assuming that the search keywords are yes, test

messages table

id | title     | msg               |date
---+-----------+-------------------+-------------+--      
1  |  test1    |  yes              | 2016-06-01 // 2 match keywords
2  |  yes1     |  no               | 2016-06-02 // 1 match keywords   
3  |  test2    |  no               | 2016-06-03 // 1 match keywords
4  |  yes2     |  yes yes yes      | 2016-06-04 // 4 match keywords
5  |  yesyes3  |  yes yes yes yes  | 2016-06-05 // 6 match keywords

now need to display it with count_match column

id | title     | msg               |date        |count_match    
---+-----------+-------------------+------------+-------------------    
1  |  test1    |  yes              | 2016-06-01 | 2
2  |  yes1     |  no               | 2016-06-02 | 1  
3  |  test2    |  no               | 2016-06-03 | 1
4  |  yes2     |  yes yes yes      | 2016-06-04 | 4
5  |  yesyes3  |  yes yes yes yes  | 2016-06-05 | 6

and for array it should displayed look like this

array (
    [0] => array (
        [id] => 5
        [title] => yesyes3
        [msg] => yes yes yes yes
        [date] => 2016-06-05
        [match] => 6
    )
    [1] => array (
        [id] => 4
        [title] => yes2
        [msg] => yes yes yes
        [date] => 2016-06-04
        [match] => 4
    )
    [2] => array (
        [id] => 1
        [title] => test1
        [msg] => yes
        [date] => 2016-06-01
        [match] => 2
    )
    [3] => array (
        [id] => 3
        [title] => test2
        [msg] => no
        [date] => 2016-06-03
        [match] => 1
    )
    [4] => array (
        [id] => 2
        [title] => yes1
        [msg] => no
        [date] => 2016-06-02
        [match] => 1
    )
)

and currently my code in fetching for this is this

        $match = array('test','yes');
        $orderbyString1 = "";
        $orderbyString = "";
        $likestr = "";
        foreach($match AS $value)
        {
            $orderbyString .= "IF(m.title LIKE '%".$value."%' OR m.msg LIKE '%".$value."%',1,0)+";
            $likestr .= "m.title LIKE '%".$value."%' OR m.msg LIKE '%".$value."%' OR ";
        }
        $orderbyString = substr($orderbyString, 0, -1);
        $likestr = substr($likestr, 0, -4);
        $this->db->select('m.*, ('.$orderbyString.') as count_match');
        $this->db->from('messages m');
        $this->db->where($likestr." ORDER BY ".$orderbyString." DESC");

        $query = $this->db->get();

        $results = $query->result_array();
        print_r($results);exit;

Is there any way for the select to display the correct count_match using php codeigniter code? because currently it displays 1 and 2 under count_match.

Thanks!

it easier may be done by PHP - loop array and add result of substr_count() — splash58, Jul 02 '16 at 08:32
You can also use [regex](http://stackoverflow.com/a/22944075/5527985) eg if you need caseless matching or word boundaries. See [demo at eval.in](https://eval.in/599468) — bobble bubble, Jul 02 '16 at 09:46
@bobblebubble thanks how about if the msg = testyestest and the title = yestest? the match_count should be 5 — howardtyler, Jul 02 '16 at 09:57
got it thanks to @splosh58 too I'll just do it in php though — howardtyler, Jul 02 '16 at 10:04
@bobblebubble just post your answer below for me to mark it as the solution for this problem. thanks! — howardtyler, Jul 02 '16 at 10:05

score 1 · Accepted Answer · edited May 23 '17 at 10:28

On php-side there are many options to count the keywords in your array. If you need additional functionality such as caseless matching or word boundaries how about using regex.

An idea with preg_match_all

Returns the number of full pattern matches (which might be zero), or FALSE if an error occurred.

$pattern = '~(?:yes|test)~i';

foreach($arr AS $k => $v)
  $arr[$k]['match'] = preg_match_all($pattern, $v['title']." ".$v['msg']);

The pattern is simply an alternation of the two keywords using a non-capturing group. After the closing pattern delimiter ~ used the i flag for caseless matching. Regex101 is a nice place to test pattern.

Here is a demo at eval.in

If input is generic, use preg_quote to escape certain characters from it's special regex meaning.

how to count the matched keywords using select in mysql codeigniter php?

1 Answers1

Linked