如何计数匹配的关键字使用select在mysql codeigniter php？ [英] how to count the matched keywords using select in mysql codeigniter php?

问题描述

如何使用select在mysql codeigniter php中计数匹配的关键字？这里是我的表

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

现在需要使用count_match列显示

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
    )
)

目前我的代码

        $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;

有什么方法让select使用php codeigniter代码显示正确的count_match？因为目前它在count_match下显示1和2.

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.

谢谢！

推荐答案

p>在php端有很多选项来计算数组中的关键字。如果您需要其他功能，例如无框匹配或字边界，如何使用 regex 。

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.

使用 preg_match_all

返回完全模式匹配的数量（可能为零），如果发生错误则返回FALSE。

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']);

模式只是一个替换两个关键字，使用非捕获组。结束模式定界符 〜使用 i flag 无框匹配。 Regex101 是测试模式的好地方。

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.

这是eval.in的演示

如果输入是通用的，请使用 preg_quote 从某些字符中转义特殊的正则表达式意义。

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

这篇关于如何计数匹配的关键字使用select在mysql codeigniter php？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！