0

I try to do a MySQL-query with a list of locations near me (ordered with the nearest first). I had no idea how to do that. I found this interesting post: http://funkjedi.com/technology/308-search-using-geolocation-data-in-mysql/

Everything seams clear to me. but the query doen't work. I made the following SQL-query:

SELECT *, ($distance_formula) AS distance FROM restaurants 
    WHERE (geolatitude BETWEEN $lat_b1 AND $lat_b2) 
    AND (geolongitude BETWEEN $lng_b1 AND $lng_b2) 
    HAVING distance < $radius ORDER BY distance ASC

The error I get is the following:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column '$distance_formula' in 'field list'

The point is, the $distance_formula is made in the script and is not a field list, that's right. But how should I do this that MySQL knows that this is to execute and it's not a field?

If you have any other help or other solution (from the green) I would be very happy. At the end, I need a solution for cakephp.

Thanks a lot

Ivo

chrki
  • 6,143
  • 6
  • 35
  • 55
FishWave
  • 308
  • 2
  • 16
  • It looks like the `$distance_formula` variable isn't evaluated and shows up as "$distance_formula" instead of the SQL formula (6371 * ACOS...) in your query. Can you post the bit where you query your database? – chrki Feb 16 '13 at 14:09
  • Huh, I query my database like this: return $this->query($sql); That's the way it works in cakephp with other queries. I think the problem is not like I query my database. – FishWave Feb 16 '13 at 15:21
  • just remove the $ sign before distance_formula and run it – Vineet1982 Feb 16 '13 at 15:26
  • Ha! Thank you! I solved the problem! I did it with sprint_f() to insert the php-variables into the SQL string. I don't know why this should be better, but it works like that! – FishWave Feb 16 '13 at 15:54
  • more info here: http://stackoverflow.com/questions/20865747/geolocation-mysql-query – miralong Dec 05 '14 at 16:48

1 Answers1

0

I think there must be a mistake of windows and unix format of files or the some times the example has some writing issues and thus request you to change the command little bit and write the command as:

 $sql = "SELECT *, (" . $distance_formula . ") AS distance FROM listings WHERE (latitude BETWEEN " . $lat_b1 . " AND " . $lat_b2 . ") AND ( longitude BETWEEN " . $lng_b1 . " AND . " $lng_b2 . ") HAVING distance < " . $radius . "ORDER BY distance ASC";
Vineet1982
  • 7,730
  • 4
  • 32
  • 67