Unable to fetch correct row in codeigniter by using multiple join

Question

I want to multiple like condition in bracket. Here is my CI Active Record code:-

$this->db->select("ev.`created_date`,u.first_name,u.last_name,ev.`expert_id` , ev.`posted_by` ,
        ev.`segment_id` , ev.`expert_title` , ev.`expert_discription` , ev.`status` , s.segment", FALSE);
    $this->db->from($this->db->dbprefix . 'expert_view as ev');
    $this->db->join($this->db->dbprefix . 'segment as s', 's.segment_id=ev.segment_id');
    $this->db->join($this->db->dbprefix . 'users as u', 'u.id=ev.posted_by');
    if ($this->session->userdata('group_id') != 1) {
        $this->db->where('ev.posted_by', $this->session->userdata('userid'));
    }
    if ($sSearch) {
        $this->db->like('u.first_name', $sSearch);
        $this->db->or_like('u.last_name', $sSearch);
        $this->db->or_like('ev.created_date', $sSearch);
        $this->db->or_like('ev.posted_by', $sSearch);
        $this->db->or_like('ev.`expert_title`', $sSearch);
        $this->db->or_like('s.`segment`', $sSearch);
    }
echo $this->db->last_query();

Output come from above code:

SELECT ev.`created_date`, u.first_name, u.last_name, ev.`expert_id`, ev.`posted_by`, ev.`segment_id`
, ev.`expert_title`, ev.`expert_discription`, ev.`status`, s.segment
FROM (`adv_expert_view` as ev)
JOIN `adv_segment` as s ON `s`.`segment_id`=`ev`.`segment_id`
JOIN `adv_users` as u ON `u`.`id`=`ev`.`posted_by`
WHERE `ev`.`posted_by` =  '14'
AND  `u`.`first_name`  LIKE '%a%'
OR  `u`.`last_name`  LIKE '%a%'
OR  `ev`.`created_date`  LIKE '%a%'
OR  `ev`.`expert_title`  LIKE '%a%'
OR  `s`.`segment`  LIKE '%a%'

Expected query:

 SELECT ev.`created_date`, u.first_name, u.last_name, ev.`expert_id`, ev.`posted_by`, ev.`segment_id`
, ev.`expert_title`, ev.`expert_discription`, ev.`status`, s.segment
FROM (`adv_expert_view` as ev)
JOIN `adv_segment` as s ON `s`.`segment_id`=`ev`.`segment_id`
JOIN `adv_users` as u ON `u`.`id`=`ev`.`posted_by`
WHERE `ev`.`posted_by` =  '14'
AND  (`u`.`first_name`  LIKE '%a%'
OR  `u`.`last_name`  LIKE '%a%'
OR  `ev`.`created_date`  LIKE '%a%'
OR  `ev`.`expert_title`  LIKE '%a%'
OR  `s`.`segment`  LIKE '%a%')

Saty · Accepted Answer · 2015-09-16 06:04:59Z

2

IF you are using CI 3 You can use ->group_start() and ->group_end() for adding bracket. For more detail read Query grouping

$this->db->group_start()
$this->db->like('u.first_name', $sSearch);
        $this->db->or_like('u.last_name', $sSearch);
        $this->db->or_like('ev.created_date', $sSearch);
        $this->db->or_like('ev.posted_by', $sSearch);
        $this->db->or_like('ev.`expert_title`', $sSearch);
        $this->db->or_like('s.`segment`', $sSearch);

$this->db->group_end()

FOR CI 2 You can create your condition as

$this->db->where("(`u`.`first_name` LIKE '%a%' OR `u`.`last_name` LIKE '%a%' OR `ev`.`created_date`  LIKE '%a%' OR  `ev`.`expert_title`  LIKE '%a%' OR `s`.`segment`  LIKE '%a%')");

edited Sep 16, 2015 at 6:04

answered Sep 16, 2015 at 5:49

Saty

22.5k7 gold badges35 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

vineet Over a year ago

i'm using CI 2.1 and it show error Call to undefined method CI_DB_mysql_driver::group_start()

Saty Over a year ago

@chotesah this function only for CI 3 not for CI 2.

vineet Over a year ago

then what is the solution? @Saty

vineet Over a year ago

Thanks @Saty for quick reply as answer and comments.

chridam Over a year ago

Hi @clsah if this or any answer has solved your question please consider accepting it by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this.

user5210690 · Accepted Answer · 2015-09-16 06:04:39Z

Do like this:-

$query = "SELECT ev.`created_date`, u.first_name, u.last_name, ev.`expert_id`, ev.`posted_by`, ev.`segment_id`
, ev.`expert_title`, ev.`expert_discription`, ev.`status`, s.segment
FROM (`adv_expert_view` as ev)
JOIN `adv_segment` as s ON `s`.`segment_id`=`ev`.`segment_id`
JOIN `adv_users` as u ON `u`.`id`=`ev`.`posted_by`";

        $query.= " WHERE";

        if ($this->session->userdata('group_id') != 1) {
            $query.= " `ev`.`posted_by` =" . $this->session->userdata('userid') . " AND";
        }

        if ($sSearch) {
            $query.= " (`u`.`first_name`  LIKE '%$sSearch%'
                OR  `u`.`last_name`  LIKE '%$sSearch%'
                OR  `ev`.`created_date`  LIKE '%$sSearch%'
                OR  `ev`.`expert_title`  LIKE '%$sSearch%'
                OR  `s`.`segment`  LIKE '%$sSearch%')";
        }

Ashok Maharjan · Accepted Answer · 2015-09-16 05:47:05Z

0

Try like this

$this->db->where("(`u`.`first_name`LIKE '%a%'OR `u`.`last_name` LIKE '%a%' OR `ev`.`created_date`  LIKE '%a%' OR  `ev`.`expert_title`  LIKE '%a%' OR `s`.`segment`  LIKE '%a%')");

answered Sep 16, 2015 at 5:47

Ashok Maharjan

1631 silver badge7 bronze badges

Collectives™ on Stack Overflow

Unable to fetch correct row in codeigniter by using multiple join

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related