Construct a CodeIgniter WHERE condition with the value BETWEEN two column values

Question

Here is my model

public function getDetails($ip)
{
    $this->db->select('country_code, country_name');
    $this->db->where("'2323' BETWEEN begin_ip_num AND end_ip_num");
    return $this->db->get('ip')->row();
}

I get this error:

Unknown column ''2323'' in 'where clause'

SELECT `country_code`, `country_name` FROM (`ip`) WHERE `2130706433` BETWEEN begin_ip_num AND end_ip_num

Where am I going wrong?

$this->db->where("'2323' BETWEEN begin_ip_num AND end_ip_num"); Is actually $this->db->where("$ip BETWEEN begin_ip_num AND end_ip_num"); — adaxa
– adaxa, Commented Jan 16, 2011 at 12:28
where you are wrong? everywhere...you are misunderstanding the between() function! hope the answer on your previous question will clear things up for you. — ifaour
– ifaour, Commented Jan 16, 2011 at 12:33

MyStream · Accepted Answer · 2011-10-17 05:18:24Z

3

2323 is evaluated as a column name, and begin_ip_num and end_ip_num as values. You must do the opposite, like this :

<?php
public function getDetails($ip)
    {
        $this->db->select('country_code, country_name');
        $this->db->where("begin_ip_num <= 2323 AND end_ip_num >= 2323");
        return $this->db->get('ip')->row();
    }

See MySQL Reference on 'BETWEEN' for how this should be used.

edited Oct 17, 2011 at 5:18

MyStream

2,5511 gold badge16 silver badges34 bronze badges

answered Jan 16, 2011 at 12:33

Xavier Barbosa

3,9471 gold badge22 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sarfraz · Accepted Answer · 2011-01-16 12:35:10Z

1

I think you meant:

$this->db->where("$ip BETWEEN begin_ip_num AND end_ip_num");

Because $ip is coming from your model function something you want to check against.

answered Jan 16, 2011 at 12:35

Sarfraz

384k81 gold badges561 silver badges613 bronze badges

Comments

mickmackusa · Accepted Answer · 2025-04-17 02:30:05Z

Upon testing with CodeIgniter 3.1.11, here is a battery of active record attempts and rendered queries assuming that the passed in $num is a string type value:

Quality	Syntax	SQL
⭐	->where( sprintf( '%s BETWEEN %s AND %s', $this->db->escape($num), $this->db->escape_identifiers('from_num'), $this->db->escape_identifiers('to_num') ) )	WHERE '2323' BETWEEN `from_num` AND `to_num`
✅	->where( [ $this->db->escape($num) . ' >=' => $this->db->escape_identifiers('from_num'), $this->db->escape($num) . ' <=' => $this->db->escape_identifiers('to_num'), ], null, false )	WHERE '2323' >= `from_num` AND '2323' <= `to_num`
❌	->where("$num BETWEEN", 'from_num AND to_num')	WHERE 2323 BETWEEN 'from_num AND to_num'
💀	->where_between($num, ['from_num', 'to_num'])	undefined method where_between()

It must be stated that if your value is a numeric (integer or float type) value, then it doesn't need any quoting. If your column names are not reserved keywords for your database dialect, then they do not need quoting. Assuming these conditions are true, you can entertain the following:

Quality

Syntax

SQL

✅

->where("from_num <= $num AND to_num >= $num")

WHERE `from_num` <= 2323 AND `to_num` >= 2323

⚠️

->where("$num BETWEEN from_num AND to_num")

WHERE 2323 BETWEEN `from_num` AND to_num

⚠️

->where("$num BETWEEN", 'from_num AND to_num', false)

WHERE 2323 BETWEEN from_num AND to_num

_{⭐ is my recommendation, ✅ means correct and fully quoted, ⚠️ means it will work but not fully quoted, ❌ means it is incorrect, 💀 means it will emit an error}

CodeIgniter BETWEEN-related answers of mine:

A similarly formatted table of CodeIgniter method calls can be found at:

How to update table with column set NULL in codeigniter

Collectives™ on Stack Overflow

Construct a CodeIgniter WHERE condition with the value BETWEEN two column values

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related