phpcodeigniterwhere-clausebetweenmysql-error-1054

How to use CodeIgniter's where() to find rows where a value is BETWEEN two column values?

My model:

public function version()
{
    $this->load->database();
        
    $this->db->select('location_id');  
    $this->db->from('geo_blocks');  
    $this->db->where("2057793231 BETWEEN `ip_start` AND `ip_end`"); 
    echo $query = $this->db->get()->row(); 
}

The error I'm getting:

A Database Error Occurred

Error Number: 1054

Unknown column ' ip_start <= 2057793231 AND ip_end >= 2057793231' in 'where clause'

SELECT `location_id` FROM (`geo_blocks`) WHERE ` ip_start <= 2057793231 AND ip_end >= 2057793231

Line Number: 93

But the query works fine in PHPMyAdmin:

SELECT * FROM `geo_blocks` WHERE 2057793231 BETWEEN `ip_start` AND `ip_end`

I also tried different queries which produce the same output in PHPMyAdmin, but not in CodeIgniter:

$this->db->where("ip_start <= 2057793231 AND ip_end >= 2057793231");

What am I doing wrong?

Solution

  • You're using the where()-function wrong. The entire point with the where function is for the system to determine where the column is, and what value to escape in order to prevent mysql injections. In this case the first parameter needs to be the columns and the second one the value:

    $this->db->where('ip_start <=', 2057793231);
$this->db->where('ip_end >=', 2057793231);

    Note that you can call the where() function more then once. It simply adds an AND between the 2 conditions.