iBeSavvvy, there are a couple things that are not quite right with your code.
$distance will not work correctly here. The variable is not a string, so PHP will try to calculate it and store a number into the variable. To make the variable do what you want (and also use ‘lat’ and ‘lng’ as field names) you need to make $distance a string, like so:
$center_lat = $this->db->escape($center_lat);
$center_lng = $this->db->escape($center_lng);
$distance = "(3959*acos(cos(radians($center_lat))*cos(radians(`lat`))*cos(radians(`lng`)-radians($center_lng) )+sin(radians($center_lat))*sin(radians(`lat`))))";
“->where(‘distance’, $distance < $this->db->escape($radius))” will also not not work quite right. PHP will try to calculate “$distance < $this->db->escape($radius)”, which will return true or false. That would make the statement “WHERE distance = 1” or “WHERE distance = 0”. Instead you want to do something like this:
$radius = $this->db->escape($radius);
$sub->where("$distance < $radius");
To actually execute this query, you need to call “$this->db->get()”, and to get results, you need to use “$this->db->result()”. Please have a look at the CodeIgniter user guide.
Also, using a subquery here in the first place is not correct. Your current code will generate a query like this (which I don’t think is what you want, or would even work):
SELECT *, (SELECT * FROM admins WHERE (3959*acos(cos(radians(...))) < 12) AS distance
FROM admins
WHERE active = 1
You can re-create your original query using just CodeIgniter’s Active Records (no subqueries).
$center_lat = $this->db->escape($center_lat);
$center_lng = $this->db->escape($center_lng);
$this->db->select('id, address, dispensary, username, lat, lng, delivery, credit, legal');
$this->db->select("(3959*acos(cos(radians($center_lat))*cos(radians(lat))*cos(radians(lng)-radians($center_lng))+sin(radians($center_lat))*sin(radians(lat)))) AS distance", false);
$this->db->from('admins')->where('active', 1);
$this->db->having('distance <', $radius);
$this->db->order_by('distance');
$this->db->limit(20);
$query = $this->db->get();
if($query->num_rows() > 0){
return $query->result();
}
return FALSE;
