Using Codeigniter active record for "within distance" query

I'm trying to create the following query in CI using ActiveRecord

SELECT *, 
   ( 3959 * acos( cos( radians($lat) ) 
   * cos( radians( lat ) ) 
   * cos( radians( lng ) - radians($lng) ) 
   + sin( radians($lat) ) 
   * sin( radians( lat ) ) ) ) AS distance 
FROM locations 
HAVING distance <= $miles 
ORDER BY distance 
LIMIT 0, 20

I tried

$where = "( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance FROM locations";        
$this->db->where($where);                           
$this->db->having('distance <= ' . $miles);                     
$this->db->order_by('distance');                    
$this->db->limit(20, 0);

The problem (I think) is that I'm already telling from which table I'm getting data via $query = $this->db->get('locations'); at the end of my model. So I'm getting the following error:

A Database Error Occurred Error Number: 1064

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 'AS distance FROM user_profiles HAVING distance <= 100 ORDER BY distance LIMI' at line 5

SELECT * FROM (locations) WHERE country = 'US' AND tags = 'technology' AND ( 3959 * acos( cos( radians(25.9331488) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-80.1625463) ) + sin( radians(25.9331488) ) * sin( radians( lat ) ) ) ) AS distance FROM locations HAVINGdistance<= 100 ORDER BYdistance` LIMIT 20

Filename: C:\wamp\www\mysite\system\database\DB_driver.php

Line Number: 330

Some things to note.. I'm using a fe开发者_如何学Pythonw where() functions in my model. The distance query should coexist with other clauses.

You're setting the distance calculation as a WHERE in your SQL. You want it in your SELECT. Didn't test, but try:

$this->db->select("*, ( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance");                         
$this->db->having('distance <= ' . $miles);                     
$this->db->order_by('distance');                    
$this->db->limit(20, 0);

$miles= 20; $this->db->select("*, ( 3959 * acos( cos( radians($shop_lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($shop_long) ) + sin( radians($shop_lat) ) * sin( radians( lat ) ) ) ) AS distance");
$this->db->from('shop'); $this->db->having('distance <= ' . $miles);
$this->db->order_by('distance');
$this->db->limit(20, 0); $query = $this->db->get(); $result = $query->result(); return $result; }