SQL search query (using LIKE) giving priority for column

activerecord, codeigniter, database, mysql, php

Solution

Here is the SQL query:

SELECT id, title, body
  FROM posts
  WHERE title LIKE '%{KEY}%' OR body LIKE '%{KEY}%'
  ORDER BY CASE
    WHEN (title LIKE '%{KEY}%' AND body LIKE '%{KEY}%') THEN 1
    WHEN (title LIKE '%{KEY}%' AND body NOT LIKE '%{KEY}%') THEN 2
    ELSE 3
    END, title
LIMIT 0, 6;

SQLFiddle Demo

In this case, rows have the `{KEY}` in both of `title` and `body` come at first, Then the rows have the `{KEY}` just inside thier `title`, and finally those that have the `{KEY}` in `body`.

You can run this query in CodeIgniter by using:

`$query = $this->db->query('YOUR QUERY HERE');`

Here is an example:

$key = $this->db->escape_like_str($seached_text);

$sql = <<<SQL
SELECT id, title, body
  FROM posts
  WHERE accepted = '1'
    AND deleted = '0'
    AND (title LIKE '%$key%' OR body LIKE '%$key%')
  ORDER BY CASE
    WHEN (title LIKE '%$key%' AND body LIKE '%$key%') THEN 1
    WHEN (title LIKE '%$key%' AND body NOT LIKE '%$key%') THEN 2
    ELSE 3
    END, title
LIMIT 0, 6;
SQL;

$query = $this->db->query($sql);
$result = $query->result_array();

Problem

I'm using CodeIgniter and active record. I have `posts` table: ``` id | title | body | accepted | deleted ``` Once user is typing a word, `6` posts matching database will be suggested if it is inside `title` or `body` of those posts. What I want to do is to give priority to those posts who have that phrase inside their `title` over those posts who have that only in their `body`. how to do it? ``` $query = $this->db->select('id,title,body') ->from('posts') ->where(array('accepted'=>'1')) ->where(array('deleted'=>'0')) ->like('title', $seached_text) ->or_like('body', $seached_text) ->limit(6) ->order_by('title','asc'); ```

Original source