0

I'm doing an android application. It has a server back end too. I need to select latitude and longitude from database which comes in a bounding square or circle. I am using the below code.. But it is not working. Please help me..

<?php

$lat = $_GET['lat'];
$lon = $_GET['lon'];
$rad = 10;

$R = 6371;

$maxLat = $lat + rad2deg($rad/$R);
$minLat = $lat - rad2deg($rad/$R);
$maxLon = $lon + rad2deg($rad/$R/cos(deg2rad($lat)));
$minLon = $lon - rad2deg($rad/$R/cos(deg2rad($lat)));


$lat = deg2rad($lat);
$lon = deg2rad($lon);

$con=mysql_connect("localhost","root","");
mysql_select_db("circle",$con);


$sql="Select name,acos(sin($lat)*sin(radians(`lat`))+cos($lat)*cos(radians(`lat`))*cos(radians(`long`)-$lon))*$R As D From circle Where acos(sin($lat)*sin(radians(`lat`))+cos($lat)*cos(radians(`lat`))*cos(radians(`long`)-$lon))*$R < $rad";

$rslt= mysql_query($sql);
$row=mysql_num_rows($rslt);
$arr=mysql_fetch_assoc($rslt);
print_r($arr);

?>

Thanks

Yury
  • 20,618
  • 7
  • 58
  • 86
youv
  • 63
  • 1
  • 1
  • 7
  • 2
    If you tell us exactly how the code is not working we'll be able to help you much better. Many of us won't even look at the code unless you tell us what errors you have. It would also help if you formatted the code for readability. – High Performance Mark Feb 26 '12 at 11:11
  • Welcome to Stack Overflow! You are not doing any error checking in your query, so it's no wonder you're not getting any useful hints on what goes wrong. How to properly check errors is outlined in the [manual on `mysql_query()`](http://php.net/mysql_query) or in this [reference question.](http://stackoverflow.com/questions/6198104/reference-what-is-a-perfect-code-sample-using-the-mysql-extension) – Pekka Feb 26 '12 at 11:13
  • This is the error am getting: Invalid query: 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 'long > – youv Feb 26 '12 at 11:34

2 Answers2

1

long is a reserved word on mySQL, try use backtick:

$sql = "select name from circle where lat > $minLat and lat < $maxLat and `long` > $minLon and `long` < $maxLon";
Paulo Rodrigues
  • 5,273
  • 7
  • 35
  • 58
1

Since long is a reserved word, you can't use it directly as a column name. (Are you sure your column isn't name lon instead?)

If the column is named long you can escape it using backticks:

$sql = "select name from circle where lat > $minLat and lat < $maxLat and `long` > $minLon and `long` < $maxLon";

That way MySQL knows you're using a column name and it doesn't try to interpret the text as something else.

Update

The new query from the OP with sorting by distance. (I did rename D to distance.)

$sql="SELECT name, acos(sin($lat)*sin(radians(`lat`))+cos($lat)*cos(radians(`lat`))*cos(radians(`long`)-$lon))*$R AS `distance`
    FROM circle
    WHERE `distance` < $rad
    ORDER BY `distance` ASC";

Please note that this query will be slow because it cannot use indexes to limit the number of rows nor for sorting. It will have to calculate the distance for every row before it can determine whether or not the row should be in the result set. That won't really matter if your table contains 5 rows, but it will matter if there are 500,000 rows.

Arjan
  • 9,784
  • 1
  • 31
  • 41
  • Hello may i ask one ore question please?Am getting the results from database but i need to sort it..minimum value should come first..How can i do that? – youv Feb 26 '12 at 12:01
  • You can sort the output by adding a `ORDER BY` clause to the query. You just need to define which how the sorting should be done (which columns should be used for sorting). – Arjan Feb 26 '12 at 12:04
  • see,when using the new query am getting the result like Array([name]=>AAA [D]=>1.333)Array([name]=>BBB [D]=>1.222) How can i sort the esult with this distance?please help – youv Feb 26 '12 at 12:19