title: mysql best match algorithm in codeigniter
body: <p>let's say I have this table:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I want to build a query that sort the result by number of matches from a string in multiple fields. </p>
  
  <p>the rules are:</p>
  
  <ol>
  <li>order by most number of matching words</li>
  <li>order by the position of matched words (found on nth character in field)</li>
  </ol>
  
  <p>for example, the keyword <code>java school book</code> should result in the order id 03, 02 because id 03 has 3 matching words and id 02 only has 2 matching words.</p>
  
  <p><a href="https://stackoverflow.com/a/18726122/1235167">this answer</a> suggested using <code>LOCATE function</code> from mysql but I don't know how to use that function in codeigniter 3.</p>
  
  <p>I tried this query:</p>
  
  <pre><code>$search = $this-&gt;input-&gt;get('q', true);
  $search = $this-&gt;db-&gt;escape($search);
  $this-&gt;db-&gt;select('id, title, description, state_name');
  $this-&gt;db-&gt;from('mytable');
  $this-&gt;db-&gt;like('title', $search);
  $this-&gt;db-&gt;like('description', $search);
  $this-&gt;db-&gt;like('state_name', $search);
  $this-&gt;db-&gt;order_by("LOCATE({$search}), title");
  $this-&gt;db-&gt;order_by("LOCATE({$search}), description");
  $this-&gt;db-&gt;order_by("LOCATE({$search}), state_name");
  </code></pre>
  
  <p>when I tried to search for the keyword 'test', it gives the error:</p>
  
  <pre><code>Incorrect parameter count in the call to native function 'LOCATE'
  
  ...ORDER BY LOCATE('test'), `mytable`.`title`, LOCATE('test'), `mytable`.`description`, LOCATE('test'), `mytable`.`tipsntrick`
  </code></pre>
  
  <p>can anyone help me?</p>