CodeIgniter2's where() method is rendering a misquoted string when given a single parameter including BETWEEN [duplicate]

Question

I am getting a database syntax error from codeigniter and its generating this sql query.

SELECT `code`, `latitude`, `longitude` FROM (`postcodes`) WHERE `latitude` `BETWEEN 55`.`8616 AND 56`.`1508` AND `longitude` `BETWEEN '-3`.`9507' AND '-3`.`6131'` AND `code` LIKE 'FK%'

I think I can see the problem its adding '' where they should not be in the longitude and lqatitudes.

The code thats generating this query is:

 function get_zips_in_range($zip, $range, $sort=1, $include_base=true)
{
    //get base postcode details
    $details = $this->get_zip_point($zip);

    if( ! $details)
    {
        return false;
    }

    //get the first 2 letters of the postcode
    $str = substr($zip, 0, 2);

    //find max - min lat / long for radius and zero point and query
    //only zips in that range.
    $lat_range = $range/69.172;
    $lon_range = abs($range/(cos($details->latitude) * 69.172));
    $min_lat = number_format($details->latitude - $lat_range, "4", ".", "");
    $max_lat = number_format($details->latitude + $lat_range, "4", ".", "");
    $min_lon = number_format($details->longitude - $lon_range, "4", ".", "");
    $max_lon = number_format($details->longitude + $lon_range, "4", ".", "");

    //build the sql query
    $this->CI->db->select("code, latitude, longitude");

    if( !$include_base)
    {
        $this->CI->db->where("code <>", $zip);
    }

    $this->CI->db->where("latitude BETWEEN '$min_lat' AND '$max_lat'");
    $this->CI->db->where("longitude BETWEEN '$min_lon' AND '$max_lon'");
    $this->CI->db->like('code', $str, 'after');    
    $result = $this->CI->db->get("postcodes");

    if($result->num_rows() < 1)
    {
        $this->set_last_error("SQL error in get_zips_in_range");
        return false;
    }

it seems to be adding single quotes around the points in the lon/lats like so — user794846
– user794846, Commented Jun 14, 2012 at 12:29

user794846 · Accepted Answer · 2012-06-14 13:49:31Z

3

There is a bug in CI 2.1.1 https://github.com/EllisLab/CodeIgniter/issues/1469

Reverted to 2.1.0 is the best solution until 2.1.2 comes out

answered Jun 14, 2012 at 13:49

user794846

1,9315 gold badges32 silver badges78 bronze badges

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

Comments

Dirk de Man · Accepted Answer · 2012-06-15 11:14:33Z

1

CodeIgniter automatically adds backticks to make safer queries. If you want to disable this, you can add the parameter FALSE as follows:

$this->CI->db->where("latitude BETWEEN '$min_lat' AND '$max_lat'", FALSE);

answered Jun 15, 2012 at 11:14

Dirk de Man

6553 silver badges9 bronze badges

Comments

KuKu · Accepted Answer · 2012-06-15 11:32:08Z

Well, check out following thing,

$this->CI->db->where("latitude BETWEEN $min_lat AND $max_lat");
$this->CI->db->where("longitude BETWEEN $min_lon AND $max_lon");

apart from syntax, it will be rendered as where latitude BETWEEN $min_lat AND $max_lat AND longitude BETWEEN $min_lon AND $max_lon

Now you will ask what is the problem..

The problem is in query the way we are making it

It can be done as follows

$this->CI->db->where("(latitude BETWEEN $min_lat AND $max_lat)");
$this->CI->db->where("(longitude BETWEEN $min_lon AND $max_lon)");

So it will be rendered as where (latitude BETWEEN $min_lat AND $max_lat) AND (longitude BETWEEN $min_lon AND $max_lon) and hopefully this will solve the problem.

Even there is no need to quotes.

jcho360 · Accepted Answer · 2012-06-14 12:34:53Z

0

$this->CI->db->where("latitude BETWEEN '$min_lat' AND '$max_lat'");
$this->CI->db->where("longitude BETWEEN '$min_lon' AND '$max_lon'")

remove the simple '

$this->CI->db->where("latitude BETWEEN $min_lat AND $max_lat");
$this->CI->db->where("longitude BETWEEN $min_lon AND $max_lon")

edited Jun 14, 2012 at 12:34

answered Jun 14, 2012 at 12:20

jcho360

3,7491 gold badge18 silver badges25 bronze badges

8 Comments

user794846 Over a year ago

Just tried it the error remains: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BETWEEN 55.8616 AND 56.1508 AND longitude BETWEEN -3.9507 AND -3.61' at line 3 SELECT code, latitude, longitude` FROM (postcodes) WHERE latitude BETWEEN 55.8616 AND 56.1508 AND longitude BETWEEN -3.9507 AND -3.6131 AND code LIKE 'FK%'

jcho360 Over a year ago

can you print the complete query to see how it looks like?

user794846 Over a year ago

SELECT code, latitude, longitude FROM (postcodes) WHERE latitude BETWEEN 55.8616 AND 56.1508 AND longitude BETWEEN -3.9507 AND -3.6131 AND code LIKE 'FK%'

jcho360 Over a year ago

your query looks ok, can you put the tables structure and some data in sqlfiddle.com to make test?

user794846 Over a year ago

The query is ok as it works a direct sql query the problem is that ci is putting single quotes around the point in the numbers but its not showing that when I paste the query in here instead of 56.1508 the ci query is 56"."1508.

|

Collectives™ on Stack Overflow

CodeIgniter2's where() method is rendering a misquoted string when given a single parameter including BETWEEN [duplicate]

4 Answers 4

Comments

Comments

Comments

8 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

8 Comments

Linked

Related