I am getting the following below error when running the query using the yii framework. I am not find myself to resolve this issue can you please help to recover and resolve it where I am doing wrong in to that.
CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 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 '), 2) + POW(69.1 * (- items.longitude) * COS(items.latitude / 57.3), 2)),2) as d' at line 1. The SQL statement executed was: SELECT items.*,round(SQRT( POW(69.1 * (items.latitude - ), 2) + POW(69.1 * (- items.longitude) * COS(items.latitude / 57.3), 2)),2) as distance,u.id as userId,u.first_name,u.last_name FROM
10yoyo_shared_item
items
JOIN10yoyo_user
u
ON items.user_id=u.id WHERE items.home_page_display_status="Y" AND items.status="active" AND items.category_id = "62" AND u.status="active" HAVING distance <=10000 ORDER BYshared_time
DESC LIMIT 4
The statement executed was:
SELECT items.*,round(SQRT( POW(69.1 * (items.latitude - ), 2) + POW(69.1 * (- items.longitude) * COS(items.latitude / 57.3), 2)),2) as distance,u.id as userId,u.first_name,u.last_name
FROM `10yoyo_shared_item` `items`
JOIN `10yoyo_user` `u` ON items.user_id=u.id
WHERE items.home_page_display_status="Y" AND items.status="active" AND items.category_id = "62" AND u.status="active" HAVING distance <=10000
ORDER BY `shared_time` DESC LIMIT 4
Thanks, Sorabh
Finally i resolved my error by replacing the sqrt function with the following codes as below:
DEGREES(ACOS(COS(RADIANS(items.latitude))
* COS(RADIANS("'.$latitude.'"))
* COS(RADIANS(items.longitude - "'.$longitude.'"))
+ SIN(RADIANS(items.latitude))
* SIN(RADIANS("'.$latitude.'")))) AS distance
Thanks and Regards, Sorabh