How to find the most match keyword for search function using 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!

Possible duplicate of [how to count the matched keywords using select in mysql codeigniter php?](http://stackoverflow.com/questions/38158110/how-to-count-the-matched-keywords-using-select-in-mysql-codeigniter-php) — A J, Jul 11 '16 at 10:38

A J · Accepted Answer · 2016-07-02T07:42:18.947

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

edited Jul 02 '16 at 07:42

answered Jul 02 '16 at 05:30

A J

3,970
14
38
53

hi @aj thanks but how about the match that will return? how will it be displayed in the array? – howardtyler Jul 02 '16 at 05:51
I get an error: Severity: Notice Message: Array to string conversion – howardtyler Jul 02 '16 at 06:03
alright so it would be `$match = 'yes test;` but how about the foreach? it must be in array – howardtyler Jul 02 '16 at 06:11
almost there but I get these error ` syntax to use near 'LIKE '%%' ESCAPE '!' ORDER BY IF(` – howardtyler Jul 02 '16 at 06:43
I think there is a problem with the order by using the `$orderbyString` – howardtyler Jul 02 '16 at 06:50
what is search term? I just used `$match = array('yes','test');` if you could have some jsfiddle like for me to see it would be nice – howardtyler Jul 02 '16 at 07:03
I checked the count match and it seems like it is not working too... `$this->db->select('m.*, ('.$orderbyString.') as count_match');` – howardtyler Jul 02 '16 at 07:10
can you use `echo $this->db->last_query();` after the `$query = $this->db->get();` and show me the result? – A J Jul 02 '16 at 07:16
SELECT `m`.*, (IF(m.title LIKE '%yes%' OR m.msg LIKE '%yes%', 1, 0)+IF(m.title LIKE '%test%' OR m.msg LIKE '%test%', 1, 0)) as count_match FROM `messages` `m` WHERE `m`.`title` LIKE '%yes%' OR `m`.`msg` LIKE '%yes%' OR `m`.`title` LIKE '%test%' OR `m`.`msg` LIKE '%test%' LIKE '%%' ESCAPE '!' ORDER BY IF(m.title LIKE '%yes%' OR m.msg LIKE '%yes%' DESC, 1 DESC, 0)+IF(m.title LIKE '%test%' OR m.msg LIKE '%test%' DESC, 1 DESC, 0) DESC – howardtyler Jul 02 '16 at 07:23
`You have an error in your SQL syntax; near 'LIKE '%%' ESCAPE '!' ORDER BY IF(m.title LIKE '%yes%' OR m.msg LIKE '%yes%' DESC' ` – howardtyler Jul 02 '16 at 07:24
still error : You have an error in your SQL syntax; near 'IF(m.title LIKE '%yes%' OR `m`.`msg` LIKE '%yes%',1,0)+IF(m.title LIKE '%test%' ' at line 3 SELECT `m`.*, (IF(m.title LIKE '%yes%' OR m.msg LIKE '%yes%', 1, 0)+IF(m.title LIKE '%test%' OR m.msg LIKE '%test%', 1, 0)) as count_match FROM `messages` `m` WHERE `m`.`title` LIKE '%yes%' OR `m`.`msg` LIKE '%yes%' OR `m`.`title` LIKE '%test%' OR `m`.`msg` LIKE '%test%' IF(m.title LIKE '%yes%' OR `m`.`msg` LIKE '%yes%',1,0)+IF(m.title LIKE '%test%' OR `m`.`msg` LIKE '%test%',1,0) DESC – howardtyler Jul 02 '16 at 07:40
tried your code it is displaying the array now but the values of count_match is wrong and the order that has the same count_match is not in DESC – howardtyler Jul 02 '16 at 07:49
`Array ( [0] => Array ( [id] => 1 [title] => test1 [msg] => yes [date] => 2016-06-01 00:00:00 [count_match] => 2 ) [1] => Array ( [id] => 2 [title] => yes1 [msg] => no [date] => 2016-06-02 00:00:00 [count_match] => 1 ) [2] => Array ( [id] => 3 [title] => test2 [msg] => no [date] => 2016-06-03 00:00:00 [count_match] => 1 ) [3] => Array ( [id] => 4 [title] => yes2 [msg] => yes yes yes [date] => 2016-06-04 00:00:00 [count_match] => 1 ) [4] => Array ( [id] => 5 [title] => yesyes3 [msg] => yes yes yes yes [date] => 2016-06-05 00:00:00 [count_match] => 1 ) ) ` – howardtyler Jul 02 '16 at 07:50
As your question mentions 'most match keyword', this code is perfectly working. Your search terms are 'yes' and 'test' and in the first result there are matching. The `Like` will only look for single occurrence and return true if it finds any. IF you want the number of occurrence, check this link http://stackoverflow.com/q/12344795/4932070 – A J Jul 02 '16 at 16:09

How to find the most match keyword for search function using codeigniter php

1 Answers1