CODEWITHSUNDEEP
phpcodeigniteractiverecordquery-builderlogical-grouping
keerthi
keerthi

Reputation: 767

Codeigniter query to correctly encapsulate AND and OR conditions in a WHERE clause

I want a CodeIgniter select query from a table with three conditions.

  1. wrk_fld_exc = 140
  2. wrk_cs_sts = 'Open'
  3. wrk_dlvrd_sts = 'Delivered' OR wrk_cl_sts = 'Success'

The third condition is an AND condition contains an OR condition. First and second is an AND condition.

Upvotes: 11

Views: 117827

Answers (7)

mickmackusa
mickmackusa

Reputation: 48070

You must encapsulate the subset of OR conditions with group_start() and group_end(). or_where() and get_where() are the most succinct methods to leverage in this case and they both happily receive an associative array of conditions.

return $this->db
    ->start_group()
    ->or_where([
        'wrk_dlvrd_sts' => 'Delivered',
        'wrk_cl_sts' => 'Success',
    ])
    ->group_end()
    ->get_where(
        'wrk_table',
        ['wrk_fld_exc' => 140, 'wrk_cs_sts' => 'Open']
    )
    ->result();

Rendered SQL will resemble (spacing and quoting may vary)

SELECT *
FROM wrk_table
WHERE (
    `wrk_dlvrd_sts` = 'Delivered'
    OR `wrk_cl_sts` = 'Success'
)
AND `wrk_fld_exc` = 140
AND `wrk_cs_sts` = 'Open'

See also: Build a CodeIgniter with a combination of AND and OR conditions in a WHERE clause

Upvotes: 0

sukalogika
sukalogika

Reputation: 599

I want to add some info for Codeigniter 3.1.11:

There is a Query grouping in Query Builder Class

So Ben Answer will be like this:

public function some_function()
    {
        $this->db->where("wrk_fld_exc",140);
        $this->db->where("wrk_cs_sts","Open");
        $this->db->group_start();
        $this->db->where("wrk_dlvrd_sts","Delivered");
        $this->db->or_where("wrk_cl_sts","Success");
        $this->db->group_end();
        return $this->db->get("some_table");
    }

After check with:

        echo $this->db->last_query();

It will produce SQL:

SELECT * FROM `some_table` WHERE `wrk_fld_exc` = 140 AND `wrk_cs_sts` = 'Open' AND ( `wrk_dlvrd_sts` = 'Delivered' OR `wrk_cl_sts` = 'Success' )

Further Read: Codeigniter Manual Book

Upvotes: 3

iCodeCrew
iCodeCrew

Reputation: 115

codeigniter uses its own syntax for OR claus in query

$this->db->or_where('wrk_cl_sts','Success');

to use AND in where clause use $this->db->where(''); twice

Upvotes: 7

NomanJaved
NomanJaved

Reputation: 1390

    $this->db->select("*");
    $this->db->from("table_name");
    if($condition1 != ''){
        $this->db->where('wrk_fld_exc', 140);
    }
    if($condition2 != ''){
        $this->db->where('wrk_cs_sts ', open);
    }
    //You can limit the results
    $this->db->limit(5);
    $q = $this->db->get();
    return $q->result();

This is the basic structure of the query you can implement in this way in codeigniter. You can add conditions if you require.

Upvotes: -1

Shadow
Shadow

Reputation: 35

 $this->db->where('wrk_fld_exc',140);

 $this->db->where('wrk_cs_sts','open');

 $where = '(wrk_dlvrd_sts="open" or wrk_cl_sts = "Success")';

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

Upvotes: 0

Cristiana Chavez
Cristiana Chavez

Reputation: 11549

Like this 

 $this->db->where('wrk_fld_exc',140);
 $this->db->where('wrk_cs_sts','open');
 $this->db->where('wrk_dlvrd_sts ','Delivered');
 $this->db->or_where('wrk_cl_sts','Success');

Upvotes: 5

Ben
Ben

Reputation: 4462

You can code it like this:

       $this->db->where('wrk_fld_exc',140);
       $this->db->where('wrk_cs_sts','open');
       $where = '(wrk_dlvrd_sts="open" or wrk_cl_sts = "Success")';
       $this->db->where($where);

Upvotes: 34

Related Questions