1

let's say I have this table:

id | title           | description      | state_name
-------------------------------------------------------
01 | dairy products  | has milk in it   | east java
02 | school supplies | for teachers     | west java
03 | school books    | books for school | central java

I want to build a query that sort the result by number of matches from a string in multiple fields.

the rules are:

  1. order by most number of matching words
  2. order by the position of matched words (found on nth character in field)

for example, the keyword java school book should result in the order id 03, 02 because id 03 has 3 matching words and id 02 only has 2 matching words.

this answer suggested using LOCATE function from mysql but I don't know how to use that function in codeigniter 3.

I tried this query:

$search = $this->input->get('q', true);
$search = $this->db->escape($search);
$this->db->select('id, title, description, state_name');
$this->db->from('mytable');
$this->db->like('title', $search);
$this->db->like('description', $search);
$this->db->like('state_name', $search);
$this->db->order_by("LOCATE({$search}), title");
$this->db->order_by("LOCATE({$search}), description");
$this->db->order_by("LOCATE({$search}), state_name");

when I tried to search for the keyword 'test', it gives the error:

Incorrect parameter count in the call to native function 'LOCATE'

...ORDER BY LOCATE('test'), `mytable`.`title`, LOCATE('test'), `mytable`.`description`, LOCATE('test'), `mytable`.`tipsntrick`

can anyone help me?

Community
  • 1
  • 1
dapidmini
  • 1,490
  • 2
  • 23
  • 46
  • LOCATE will return the position of one string in another. Your "parameter count" error is because you need to pass two parameters to LOCATE and you're using one. That's besides the point though, because you want to order by the *number of matches* not the *position of a match in the string*. – moopet Dec 20 '16 at 11:16
  • 1
    Your closing bracket belongs after the column name, so use `LOCATE({$search}, title)` to fix your syntax error. On the other hand, I don't think this will give you the result you are hoping for. It will only work for a single-word-keyword, and will order only according to the position in the first column, using the 2nd and 3rd column only as a decider. Also you don't include your first rule anywhere. If you could ignore rule 2, a [fulltext search](http://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html) might be worth a look (or even in combination with your locate-ordering) – Solarflare Dec 20 '16 at 11:21
  • @Solarflare thanks for clearing that up. I didn't realise the second parameter was outside the bracket.. it seems I'm too tired right now. I'll be sure to check out your solution later. thanks again. – dapidmini Dec 20 '16 at 11:57

0 Answers0