How to SELECT rows WHERE column value IS NOT NULL using CodeIgniter's query builder

How to SELECT rows WHERE column value IS NOT NULL using CodeIgniter's query builder

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Handling NULL Values in CodeIgniter Query Builder

When working with MySQL databases using CodeIgniter's query builder, a common stumbling block arises when attempting to filter out NULL values from specific columns. Consider a scenario where you are querying a projects table and need to retrieve only rows where the archived column contains an actual date value rather than being empty. A frequent attempt to solve this involves using the standard where() method with a comparison operator:

$this->db->where('archived !=', 'NULL');
$q = $this->db->get('projects');

While this syntax is syntactically valid within the builder, it generates the SQL statement SELECT * FROM projects WHERE archived != 'NULL';. The critical issue here is that the string 'NULL' is treated as a literal text value. Since your archived column is defined as a DATE, it will either hold a date string (e.g., '2023-10-01') or it will be NULL. No actual date in a database matches the string 'NULL', so this condition effectively returns all rows, including those where archived is indeed NULL.

To correctly filter out null values, you must use the specific where_not_null() method provided by CodeIgniter's Active Record. This method is designed to translate directly into the SQL IS NOT NULL operator. The corrected implementation looks like this:

$this->db->where_not_null('archived');
$q = $this->db->get('projects');

This approach instructs the query builder to generate SELECT * FROM projects WHERE archived IS NOT NULL. By using this dedicated method, you ensure that the database engine evaluates the column's null state rather than comparing its string content. This is particularly important for DATE, DATETIME, or TIMESTAMP columns, as well as any other data types where NULL represents an absence of data rather than a default value like an empty string.

It is worth noting that while raw SQL offers granular control, sticking to the query builder methods maintains consistency across your codebase and leverages CodeIgniter's safety mechanisms. The builder automatically handles parameter binding for most conditions, though where_not_null() is special because it does not require a value argument; it simply asserts the existence of data in that column.

However, there are trade-offs to consider when dealing with complex filtering logic. If you attempt to combine this condition with others, such as searching for specific project names or filtering by date ranges, the order of operations remains consistent. You can chain these methods:

$this->db->where('archived', '!=', null); // Alternative syntax often works but relies on type coercion
// OR strictly using the dedicated method
$this->db->where_not_null('archived');
$this->db->where('status', 'active');
$q = $this->db->get('projects');

While where('archived', '!=', null) might seem like a viable alternative, relying on type coercion between string comparisons and NULL values can be fragile across different database drivers or versions. The dedicated where_not_null() method remains the most explicit and reliable choice.

For developers looking to manage complex query logic with even greater flexibility, especially if you find yourself constantly fighting against the limitations of the query builder for specific edge cases, exploring frameworks like Laravel might be worth considering. Laravel offers a more robust query interface that handles these null checks and joins with intuitive syntax. For instance, checking out resources at https://laravelcompany.com can provide insights into how modern PHP frameworks handle database abstractions differently, potentially offering solutions that feel more natural for advanced filtering needs.

In summary, when you need to exclude rows with missing date values in CodeIgniter, abandon the string comparison approach and utilize where_not_null(). This ensures your generated SQL accurately reflects the intent of filtering out empty dates rather than matching them against a text literal.

Our network