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%'
error 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
The query itself works when done directly in sql :
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%’
LIMIT 0 , 30
But ci seems to be adding single quotes ariound the points in the long/lat values why?
Ci code:
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");