php - SQL search query (using LIKE) giving priority for column -

i'm using codeigniter , active record. have posts table:

id | title | body | accepted | deleted 

once user typing word, 6 posts matching database suggested if inside title or body of posts.

what want give priority posts have phrase inside title on posts have in body. how 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');  

here sql query:

select id, title, body   posts   title '%{key}%' or body '%{key}%'   order case     when (title '%{key}%' , body '%{key}%') 1     when (title '%{key}%' , body not '%{key}%') 2     else 3     end, title limit 0, 6; 

sqlfiddle demo

in case, rows have {key} in both of title , body come @ first, rows have {key} inside thier title, , have {key} in body.

you can run query in codeigniter using:

$query = $this->db->query('your query here');

here example:

$key = $this->db->escape_like_str($seached_text);  $sql = <<<sql select id, title, body   posts   accepted = '1'     , deleted = '0'     , (title '%$key%' or body '%$key%')   order case     when (title '%$key%' , body '%$key%') 1     when (title '%$key%' , body not '%$key%') 2     else 3     end, title limit 0, 6; sql;  $query = $this->db->query($sql); $result = $query->result_array();