convert the mysql query to codeigniter 3 model

Question

Convert the MySQL query to Codeigniter Query

    $query = "(SELECT content, title, 'msg' as type FROM messages WHERE content LIKE '%" . 
               $keyword . "%' OR title LIKE '%" . $keyword ."%') 
               UNION
               (SELECT content, title, 'topic' as type FROM topics WHERE content LIKE '%" . 
               $keyword . "%' OR title LIKE '%" . $keyword ."%') 
               UNION
               (SELECT content, title, 'comment' as type FROM comments WHERE content LIKE '%" . 
               $keyword . "%' OR title LIKE '%" . $keyword ."%')";

    mysql_query($query);

I have tried to convert it in Codeigniter

              $this->db->like("content", $keyword);

              $this->db->or_like('title',$keyword,'after'); 
              $this->db->or_like('msg',$keyword,'after'); 
             ->from('message')
              $this->db->like("content", $keyword);
              $this->db->or_like('title',$keyword,'after'); 
              $this->db->like("msg", $keyword);
             ->from('topics')
              $this->db->or_like('content',$keyword,'after'); 
              $this->db->or_like('title',$keyword,'after'); 
              $this->db->or_like('msg',$keyword,'after'); 
              ->from('comment')

The top one is in MySQL and bottom which I try to convert is in Codeigniter I m trying to search the keyword from selected columns from three tables. How I can convert the MySQL to Codeigniter. I'm trying to search the keyword from selected columns from three tables.

How I can convert the MySQL to Codeigniter

@DanishAli thank you.....but i have a question for u ....i have a single search box where from three table of 10 columns.in search box when i type any letter it should give me a suggestion from all three table of 10 colums rows data in search in codeigniter using ajax ....if u have any links or solution do provide me a link .....thanks in advance — , Mar 29 '19 at 05:40
Yes, I can give you a suggestion. Try to use JQuery autocomplete plugin or make Ajax wtih `onkeyup()` function — Danish Ali, Mar 29 '19 at 05:51
https://jqueryui.com/autocomplete/ And https://stackoverflow.com/questions/5637013/how-to-bring-ajax-search-onkeyup-with-jquery — Danish Ali, Mar 29 '19 at 05:52
Possible duplicate of [Write union query in codeigniter style](https://stackoverflow.com/questions/14978532/write-union-query-in-codeigniter-style) — Nick, Mar 30 '19 at 05:13

Danish Ali · Answer 1 · 2019-03-29T05:33:15.583

Try this

$this->db->select('content, title, msg as type');
$this->db->from('message');
$this->db->like("content", $keyword);
$this->db->or_like('title',$keyword,'after'); 
$this->db->or_like('msg',$keyword,'after'); 
$query1 = $this->db->get_compiled_select();


$this->db->select('content, title, msg as type');
$this->db->from('topics');
$this->db->like("content", $keyword);
$this->db->or_like('title',$keyword,'after'); 
$this->db->like("msg", $keyword);
$query2 = $this->db->get_compiled_select();

$this->db->select('content, title, msg as type');
$this->db->from('comment');
$this->db->or_like('content',$keyword,'after'); 
$this->db->or_like('title',$keyword,'after'); 
$this->db->or_like('msg',$keyword,'after'); 
$query3 = $this->db->get_compiled_select();

$result = $this->db->query($query1." UNION ".$query2." UNION ".$query3);
return $result->result();

Note:- If you intend to use this make sure that your both the table column are same sequence and name.

convert the mysql query to codeigniter 3 model

1 Answers1