Codeigniter WHERE clause issue using NULL

Question

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

Just sticking the `OR` operator into a `where` call is probably the issue. You have now effectively created `b.date >= '2022-09-12' AND b.date <= '2023-01-15' AND i.phone = "" OR i.phone IS NULL`. You need to use an `or_where` call for these two `i.phone` conditions first, and then join the result of that to the rest of your query using a `where` call again. — CBroe, Feb 06 '23 at 12:19

score 0 · Answer 1 · answered Feb 06 '23 at 13:54

->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);

score 0 · Answer 2 · answered Feb 06 '23 at 23:43

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

Codeigniter WHERE clause issue using NULL

2 Answers2