如何使用codeigniter php找到搜索功能的最匹配关键字

Question

here is my table and I need to make a search function using codeigniter that will display the a list of items that contains my keywords order by most number of matched keywords and latest date added.

Ex. the two submitted keyword 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 for expected output it will display:

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

Currently here are my current code and i'm not sure with these on how to make this search works

$match = array('yes','test');
$array = array('m.title' => $match, 'm.msg' => $match);
$this->db->select('m.*');
$this->db->from('messages m');
$this->db->like($array);
$this->db->order_by('m.date', 'DESC');
$query = $this->db->get();

Is there any php codeigniter code for this?

Thanks!

Answer 1

You will need to create a ORDER BY clause using search terms.

So the Full code

EDITED

$match = array('yes','test');
//$array = array('m.title' => $match, 'm.msg' => $match);//you do not need this
$orderbyString = "";
$likestr = "";
//first prepare an ORDER BY statement and like statement
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");
//$this->db->order_by($orderbyString, 'DESC');//removed this line

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

$results = $query->result_array();//contains the desired result