EllisLab text mark
Advanced Search
     
Codeigniter database error using between clause
Posted: 14 June 2012 09:13 AM
Joined: 2010-10-27
114 posts

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

SELECT `code`, `latitude`, `longitudeFROM (`postcodes`) WHERE `latitude` `BETWEEN 55`.`8616 AND 56`.`1508` AND `longitude` `BETWEEN '-3`.`9507' AND '-3`.`6131'` AND `codeLIKE '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($zip02);
  
  
//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"); 


 

 

 
Posted: 14 June 2012 09:34 AM   [ # 1 ]   [ Rating: 0 ]
Joined: 2012-06-13
9 posts

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

Revert to 2.1.0 is the best solution until 2.1.2 comes out

 
Posted: 14 June 2012 09:37 AM   [ # 2 ]   [ Rating: 0 ]
Joined: 2010-10-27
114 posts

Ah FFS been struggling with this for hours as well lol

 
Posted: 14 June 2012 10:38 AM   [ # 3 ]   [ Rating: 0 ]
Joined: 2012-06-13
9 posts

fyi https://github.com/EllisLab/CodeIgniter/issues/1469 bug is fixed but its been released again as CI 2.1.1

In my tests Datamapper continues to works well.