如何使用mysql codeigniter php中的select来计算匹配的关键字？

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!

Answer 1

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.