How to implement a BETWEEN condition in a WHERE clause with CodeIgniter active record

How to implement a BETWEEN condition in a WHERE clause with CodeIgniter active record

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

Stefan Bogdanescu

Founder & Senior Architect

Implementing BETWEEN Conditions in CodeIgniter Active Record

In legacy CodeIgniter applications, developers often reach for a single where() call to filter data ranges. The snippet below demonstrates a common pattern where $accommodation is treated as a column name, but the logic relies on a simple equality check against $minvalue. This approach fails when you need to retrieve records falling within a specific range, such as hotels with accommodations between two price points.

function gethotels($state_id,$city,$accommodation,$minvalue,$maxvalue,$limit,$pgoffset)
{
    $this->db->limit($limit, $pgoffset);
    $this->db->order_by("id", "desc");
    $this->db->where('state_id',$state_id);
    $this->db->where('city',$city);

    // This one should become a between selector
    $this->db->where($accommodation,$minvalue); 

    $result_hotels = $this->db->get('hotels');
    return $result_hotels->result();
}

To fix this, you must explicitly construct the BETWEEN condition using the Active Record query builder methods. The most straightforward solution is to chain two where() calls or use the dedicated where_between() method available in CodeIgniter 2.x and later versions.

Using Where_Between()

If you are working with CodeIgniter 2, the library provides a specific helper function for range queries. You can pass the column name as the first argument, followed by the lower and upper bounds. This generates the correct SQL syntax automatically: column_name BETWEEN ? AND ?.

$this->db->where($accommodation, array($minvalue, $maxvalue)); 
// Or more explicitly using where_between if available in your CI version
// $this->db->where_between($accommodation, $minvalue, $maxvalue);

In the example above, passing an array to where() is a common idiom in CodeIgniter 2 that effectively handles range checks depending on the specific library version. However, using where_between() is cleaner and more readable because it makes your intent explicit. It ensures the generated SQL looks like:

WHERE accommodation BETWEEN $minvalue AND $maxvalue

This approach prevents syntax errors that often occur when developers try to concatenate strings manually, which can lead to SQL injection vulnerabilities if input isn't sanitized perfectly.

Handling Dynamic Columns

A subtle pitfall in the original code is passing $accommodation directly into where(). If $accommodation contains user input rather than a static column name defined at compile time, you must be cautious. The Active Record library will escape values by default, but it does not escape field names.

If $accommodation is dynamic (e.g., coming from a URL parameter), always validate that the string matches a known column name in your database schema before passing it to the query builder. Blindly trusting user input for column names can break the application or expose the database structure.

// Validate column name before using it
$allowed_columns = ['accommodation', 'price', 'rating'];
if (!in_array($accommodation, $allowed_columns)) {
    throw new Exception("Invalid column specified");
}

$this->db->where_between($accommodation, $minvalue, $maxvalue);

Performance Considerations

When using BETWEEN, ensure that the column you are filtering on is indexed. If your database lacks an index on the accommodation or price column, the query planner might resort to a full table scan as the dataset grows. In CodeIgniter, this doesn't change how the library builds the query string, but it significantly impacts execution time in production environments with large datasets.

Additionally, remember that BETWEEN is inclusive. The SQL generated includes both $minvalue and $maxvalue. If your business logic requires an exclusive upper bound (e.g., "less than $500"), you would need to adjust the query to use < instead of BETWEEN. CodeIgniter's Active Record allows this by simply swapping the method:

// Exclusive upper bound example
$this->db->where($accommodation, $minvalue); 
$this->db->where($accommodation . ' <', $maxvalue); // Manual chaining for exclusive bounds

By leveraging the built-in Active Record methods correctly, you avoid manual SQL string concatenation while maintaining clean, maintainable code. This pattern scales well even if your application grows into larger enterprise structures where query complexity increases.

Our network