CODEWITHSUNDEEP
phpcodeigniteractiverecordwhere-clausequery-builder
tarnfeld
tarnfeld

Reputation: 26556

WHERE clause with two conditions separated by OR using CodeIgniter's query building methods

I am using the following code to select from a MySQL database in a CodeIgniter webapp:

$query = $this->db
    ->get_where('mytable', array('id' => 10));

This works great, but I want to write the following MySQL statement using CI query builder methods.

SELECT *
FROM `mytable`
WHERE `id`='10' OR `field`='value'

Upvotes: 28

Views: 148826

Answers (7)

mickmackusa
mickmackusa

Reputation: 47864

You cannot fully enjoy CodeIgniter's query builder methods and implement the OR condition with get_where() alone because it implicitly joins conditions with AND.

or_where() does conveniently receive an array of conditions and joins each condition with OR.

public function getWhereOr(int $id, string $field): array
{
    return $this->db
        ->or_where(['id' => $id, 'field' => $field])
        ->get('mytable')
        ->result();
}

Rendered SQL:

SELECT *
FROM mytable
WHERE `id` = 10 OR `field` = 'value'

Other implementations of or_where() can be found here:

Upvotes: 0

LOKENDRA
LOKENDRA

Reputation: 391

What worked for me :

  $where = '';
   /* $this->db->like('ust.title',$query_data['search'])
        ->or_like('usr.f_name',$query_data['search'])
        ->or_like('usr.l_name',$query_data['search']);*/
        $where .= "(ust.title like '%".$query_data['search']."%'";
        $where .= " or usr.f_name like '%".$query_data['search']."%'";
        $where .= "or usr.l_name like '%".$query_data['search']."%')";
        $this->db->where($where);



$datas = $this->db->join(TBL_USERS.' AS usr','ust.user_id=usr.id')
            ->where_in('ust.id', $blog_list) 
            ->select('ust.*,usr.f_name as f_name,usr.email as email,usr.avatar as avatar, usr.sex as sex')
            ->get_where(TBL_GURU_BLOG.' AS ust',[
                'ust.deleted_at'     =>  NULL,
                'ust.status'     =>  1,
            ]);

I have to do this to create a query like this :

SELECT `ust`.*, `usr`.`f_name` as `f_name`, `usr`.`email` as `email`, `usr`.`avatar` as `avatar`, `usr`.`sex` as `sex` FROM `blog` AS `ust` JOIN `users` AS `usr` ON `ust`.`user_id`=`usr`.`id` WHERE (`ust`.`title` LIKE '%mer%' ESCAPE '!' OR  `usr`.`f_name` LIKE '%lok%' ESCAPE '!' OR  `usr`.`l_name` LIKE '%mer%' ESCAPE '!') AND `ust`.`id` IN('36', '37', '38') AND `ust`.`deleted_at` IS NULL AND `ust`.`status` = 1 ;

Upvotes: 0

Raham
Raham

Reputation: 4951

Active record method or_where is to be used:

$this->db->select("*")
->from("table_name")
->where("first", $first)
->or_where("second", $second);

Upvotes: 7

Pullat Junaid
Pullat Junaid

Reputation: 3384

$where = "name='Joe' AND status='boss' OR status='active'";

$this->db->where($where);

Though I am 3/4 of a month late, you still execute the following after your where clauses are defined... $this->db->get("tbl_name");

Upvotes: 3

Wassim Sboui
Wassim Sboui

Reputation: 1820

You can use this :

$this->db->select('*');
$this->db->from('mytable');
$this->db->where(name,'Joe');
$bind = array('boss', 'active');
$this->db->where_in('status', $bind);

Upvotes: 12

Rookwood
Rookwood

Reputation: 683

You can use or_where() for that - example from the CI docs:

$this->db->where('name !=', $name);

$this->db->or_where('id >', $id); 

// Produces: WHERE name != 'Joe' OR id > 50

Upvotes: 33

Dylan
Dylan

Reputation: 859

$where = "name='Joe' AND status='boss' OR status='active'";

$this->db->where($where);

Upvotes: 49

Related Questions