[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 522
  • Last Modified:

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
0
technortus
Asked:
technortus
  • 3
  • 3
1 Solution
 
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
could you try to put the entire expression into 1 line?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 
Guy Hengel [angelIII / a3]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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now