Welcome Guest, Not a member yet? Register   Sign In
Query method doens't work!
#1

[eluser]Unknown[/eluser]
I'm have a serious problem with query() method usage,
trying to do this query:

Code:
$arr['query'] = "SELECT motoboy.*, veiculo.*, avaliacao.*, (((acos(sin(({$lat} * pi()/180))
* sin((motoboy.latitude * pi()/180))+cos(({$lat} * pi()/180)) * cos((motoboy.latitude * pi()/180))
* cos((({$lng} - motoboy.longitude)*pi()/180))))*180/pi())*60*1.1515*1.609344)
AS distance FROM motoboy JOIN veiculo ON  veiculo.id_motoboy = motoboy.id
JOIN avaliacao ON avaliacao.id_motoboy = motoboy.id WHERE motoboy.status = '1'
HAVING distance <= {$this->distance} ORDER BY distance"

So, this query has a join with another 2 tables
"veiculo" and "validacao", and a simply math to
calculate geo location based on latitude, longitude.
This query just ignore join clause and where, but
this works fine into phpMyAdmin.
Someone can give me some help? :down:
Thanks!
#2

[eluser]SmokeyJoe[/eluser]
generally this looks fine to me.
Maybe you can try it this way to receive an error if it doesn't work

Code:
$this->db->select(a.*, b.*, c.*, (((acos(sin(({$lat} * pi()/180))
* sin((a.latitude * pi()/180))+cos(({$lat} * pi()/180)) * cos((a.latitude * pi()/180))
* cos((({$lng} - a.longitude)*pi()/180))))*180/pi())*60*1.1515*1.609344)
AS distance);
$this->db->from('motoboy AS a');
$this->db->join('veiculo AS b','b.motoboy_id = a.id');
$this->db->join('avaliacao AS c','c.motoboy_id = a.id');
$this->db->where('a.status',1);
$this->db->having('distance <=',$this->distance);
$this->db->order_by('distance','ASC');
$query = $this->db->get();
#3

[eluser]Unknown[/eluser]
Thanks for the help, i'm following your
structure and make some changes to
works inside my select method

Code:
$arr['select'] = "motoboy.*, veiculo.*,(((acos(sin(({$lat} * pi()/180))
    * sin((motoboy.latitude * pi()/180)) + cos(({$lat} * pi()/180))
    * cos((motoboy.latitude * pi()/180)) * cos((({$lng} - motoboy.longitude)*pi()/180))))
    *180/pi())*60*1.1515*1.609344) AS distance";
$arr['join'] = array('veiculo', 'motoboy.id = veiculo.id_motoboy', 'left outer');
$arr['where'] = "motoboy.status = '0'";
$arr['having'] = "distance <= {$this->distance}";
$arr['order_by'] = "distance ASC";
$rows = $this->mtb->select_motoboy($arr);




Theme © iAndrew 2016 - Forum software by © MyBB