Question 40656 in PHP Question posted in
Official manual can be found here.

Codeigniter WHERE clause issue using NULL – PHP

Im trying to return records WHERE an individuals phone number is either empty as in ” or NULL. Please check this code out

$this->db->select('i.id, i.name, i.phone, b.date')
                          ->from('bookings AS b')
                          ->join('individuals AS i', 'b.individual_id = i.id')
                          ->where('b.status', 'confirmed')
                          ->where('b.date >=', '2022-09-12')
                          ->where('b.date <=', '2023-01-15')
                          ->where('i.phone = "" OR i.phone IS NULL')
                          ->order_by('b.date', 'ASC')
                          ->get();

The result is it brings back NULL values for Phone but the date ranges in the where clause get overridden and the value of that field is also NULL. Think im missing something just not sure what.

It brings back this

object(stdClass)[129]
      public 'id' => string '52393' (length=5)
      public 'name' => string 'Mrs Janet dooley' (length=17)
      public 'phone' => null
      public 'date' => null
  1 => 
    object(stdClass)[198]
      public 'id' => string '32277' (length=5)
      public 'name' => string 'Ms Rita molongi' (length=16)
      public 'phone' => null
      public 'date' => null
Tags:

2

Answers


  1. 0

    ->where(‘i.phone = "" OR i.phone IS NULL’)

    Kindly remove the condition (i.phone = "" OR) from your query and verify the outcome. There is no need to include an empty condition in the query.

    Please let me know if you are unable to obtain the result and I will assist you in resolving the issue.

    Also you can use ->where(‘i.phone’,NULL);

    Login or Signup to reply.
  2. 0

    Your ‘OR’ is breaking the query as CI would just print it out with no breaks between the ANDs and ORs. Do a echo $this->db->last_query(); to confirm. The answer is to parenthesis it:

    where('(i.phone = "" OR i.phone IS NULL)')

    You can also do a group_start but that depends on your CI version.

    Grouping WHERE clauses in Codeigniter

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search