how to solve mysql synatx error

my php version : 5.2.5
mysql :       5.0.51a

i have problem in below query
print query

SELECT *,(((acos(sin((*3.14159265359/180)) * sin((lat*3.14159265359/180)) + cos((*3.14159265359/180)) * cos((lat*3.14159265359/180)) * cos((( - lon)*3.14159265359/180))))*180/3.14159265359)*60*1.423) as distance FROM locations HAVING distance <= 150 AND ( categories LIKE '%Category 1%' OR categories LIKE '%Category 2%' OR categories LIKE '%Category 3%' ) AND (expiration_date >= NOW() || expiration_date = '0000-00-00 00:00:00') ORDER BY distance ASC LIMIT 0,25

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 '*3.14159265359/180)) * sin((lat*3.14159265359/180)) + cos((*3.14159265359/180)) ' at line 1
technortusAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
it should get filled here:

            $lat    = trim($lat_lon->lat);
            $lon    = trim($lat_lon->lon);

obviously, it does not.

you have to find out, why.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
SELECT locations.*,(((acos(sin((*3.14159265359/180)) * sin((lat*3.14159265359/180)) + cos((*3.14159265359/180)) * cos((lat*3.14159265359/180)) * cos((( - lon)*3.14159265359/180))))*180/3.14159265359)*60*1.423) as distance 
FROM locations 
WHERE distance <= 150 
  AND ( categories LIKE '%Category 1%' OR categories LIKE '%Category 2%' OR categories LIKE '%Category 3%' ) 
  AND (expiration_date >= NOW() OR expiration_date = '0000-00-00 00:00:00') 
ORDER BY distance ASC LIMIT 0,25

Open in new window

0
 
technortusAuthor Commented:
its give folwling error
Error
 
SQL query: Documentation
 
SELECT locations . * , (
(
(
acos( sin( (
* 3.14159265359 /180
) ) * sin( (
lat * 3.14159265359 /180
) ) + cos( (
* 3.14159265359 /180
) ) * cos( (
lat * 3.14159265359 /180
) ) * cos( (
(
- lon
) * 3.14159265359 /180 ) )
)
) *180 / 3.14159265359
) *60 * 1.423
) AS distance
FROM locations
WHERE distance <=150
AND (
categories LIKE '%Category 1%'
OR categories LIKE '%Category 2%'
OR categories LIKE '%Category 3%'
)
AND (
expiration_date >= NOW( )
OR expiration_date = '0000-00-00 00:00:00'
)
ORDER BY distance ASC
LIMIT 0 , 25
 
MySQL said: Documentation
#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 '*3.14159265359/180)) * sin((lat*3.14159265359/180)) + cos((*3.14159265359/180)) ' at line 1 

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
could you try to put the entire expression into 1 line?
0
 
technortusAuthor Commented:
I found out that the actual query is as below..

$query="SELECT *,(((acos(sin(($lat*$pi/180)) * sin((lat*$pi/180)) + cos(($lat*$pi/180)) *  cos((lat*$pi/180)) * cos((($lon - lon)*$pi/180))))*180/$pi)*60*1.423) as distance FROM locations HAVING distance <= $radius AND $category_search AND (expiration_date >= NOW() || expiration_date = '0000-00-00 00:00:00') ORDER BY distance ASC $results_limit";

and looking at the error, we can conculde that its not getting the value for $lat and hence giving the error.

But I am unable to find out why it is not getting the value for $lat. i am attaching the file, could you please let me with it.

btw, you may see the implementation at http://www.meyok.com/goog1/index.php
phpGoogleStoreLocator-functions.txt
0
 
Hugh FraserConsultantCommented:
In the query at
SELECT *,(((acos(sin((*3.14159265359/180))

what's being multiplied by 3.14? It looks like this is a prepared query that's got a blank for a parameter.
0
 
technortusAuthor Commented:
yes, it get the value from $lat variable. I have posted the details in my above post.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.