dongqixuan3112 2016-07-02 08:25
浏览 44
已采纳

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

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!

  • 写回答

1条回答 默认 最新

  • du1068 2016-07-02 11:01
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
  • ¥15 ubuntu系统下挂载磁盘上执行./提示权限不够
  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误