1

I'm trying to run a SQL query to search for a term and a users location. I need help to search my database for locations near a user with the description of 'X'. The users location (lat, long) is drawn when they click 'find' on a form for 'X'.

Individually each query works fine. I can find all with a specific %search_term% using:

 SELECT * FROM  `table_name` WHERE  `column_name` LIKE '%search_term%'

and also find all the locations from the distant of the user using:

  SELECT *, (
  3959 * acos (
  cos ( radians($lat) )
  * cos( radians( lat ) )
  * cos( radians( lng ) - radians($long) )
  + sin ( radians($lat) )
  * sin( radians( lat ) )
  )
  ) AS distance
  FROM locations
  HAVING distance < 50
  ORDER BY distance
  LIMIT 0 , 20;

but together I receive an error #1064.

This is the query:

  SELECT * FROM  `table_name` WHERE  `column_name` LIKE '%search_term%' AND 
  *, (
  3959 * acos (
  cos ( radians($lat) )
  * cos( radians( lat ) )
  * cos( radians( lng ) - radians($long) )
  + sin ( radians($lat) )
  * sin( radians( lat ) )
  )
  ) AS distance
  FROM locations
  HAVING distance < 50
  ORDER BY distance
  LIMIT 0 , 20;
  • LIMIT 0? Doesn't that mean return nothing? – alphabetasoup Jul 10 '15 at 6:52
  • @alpha-beta-soup, I used the initial SELECT statement from Google here: link The full code is 'LIMIT 0 , 20;' The error only listed 'LIMIT 0 ,' – Avrahm Kleinholz Jul 10 '15 at 13:16
  • I think that's shorthand for LIMIT 0 OFFSET 20 (i.e. returning no rows, starting from row 20). I don't really understand the syntax or why that would be useful; have you tried just with LIMIT 10 or something? – alphabetasoup Jul 10 '15 at 20:44
  • @alpha-beta-soup, i edited my question to try to be more specific. The LIMIT 0 , 20 isn't a problem when I separate the SQL query. – Avrahm Kleinholz Jul 10 '15 at 21:50
1

Error 1064 means syntax error. You are putting "AS distance" at wrong place. Try this:

SELECT *, (3959 * acos(cos (radians($lat))*cos(radians(lat))*cos(radians(lng) - radians($long))+sin (radians($lat))*sin( radians( lat ) )  )  ) AS distance FROM  `table_name` WHERE `column_name` LIKE '%search_term%' HAVING distance < 50 ORDER BY distance  LIMIT 0 , 20;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.