# select within 20 kilometers based on latitude/longitude

Viewed   65 times

i have a mysql table structured as per the example below:

``````POSTAL_CODE_ID|PostalCode|City|Province|ProvinceCode|CityType|Latitude|Longitude
7|A0N 2J0|Ramea|Newfoundland|NL|D|48.625599999999999|-58.9758
8|A0N 2K0|Francois|Newfoundland|NL|D|48.625599999999999|-58.9758
9|A0N 2L0|Grey River|Newfoundland|NL|D|48.625599999999999|-58.9758
``````

now what i am trying to do is create a query that will select results within selected kilometers of a searched location

so lets say they search for "grey river" and select "find all results within 20 kilometers"

it should obviously select "grey river", but it should also select all locations within 20 kilometers of grey river based on the latitudes and longitudes.

i really have no idea how to do this. i've read up on the haversine formula but have no idea how to apply this to a mysql SELECT.

any help would be much appreciated.

5
``````SELECT  *
FROM    mytable m
JOIN    mytable mn
WHERE   m.name = 'grey river'
``````

If your table is `MyISAM` you may want to store your points in a native geometry format and create a `SPATIAL` index on it:

``````ALTER TABLE mytable ADD position POINT;

UPDATE  mytable
SET     position = POINT(latitude, longitude);

ALTER TABLE mytable MODIFY position NOT NULL;

CREATE SPATIAL INDEX sx_mytable_position ON mytable (position);

SELECT  *
FROM    mytable m
JOIN    mytable mn
ON      MBRContains
(
LineString
(
Point
(
X(m.position) - 0.009 * 20,
Y(m.position) - 0.009 * 20 / COS(RADIANS(X(m.position)))
),
Point
(
X(m.position) + 0.009 * 20,
Y(m.position) + 0.009 * 20 / COS(RADIANS(X(m.position))
)
),
mn.position
)
WHERE   m.name = 'grey river'
``````
Sunday, September 18, 2022
3

If you want the third row, use `offset`/`limit`:

``````select *
from clients
order by id
offset 2
limit 1;
``````

Note that that `offset 0` gets the first record, so offset 2 would be the third record.

Sunday, December 25, 2022
3

As others have said and you confirmed, the problem is that you are using string literals to compare to a numeric column. To have it work, the query should look like

``````mysql_query("SELECT * FROM foo WHERE id =\$foo OR id = \$foo2");
``````

However, this solution has very very bad code smell!

First off, this is why `IN` exists: to be able to write

``````mysql_query("SELECT * FROM foo WHERE id IN (\$foo, \$foo2)");
``````

And second, are you injecting unescaped strings into your query? If you are, your code is vulnerable to sql injection! Escape and quote your variables to be safe, like this (in the general case):

``````\$query = sprintf("SELECT * FROM foo WHERE id IN ('%s', '%s')",
mysql_real_escape_string(\$foo),
mysql_real_escape_string(\$foo2));
mysql_query(\$query);
``````

or alternatively like this, since in this specific scenario you know we 're talking about integer values:

``````\$query = sprintf("SELECT * FROM foo WHERE id IN (%s, %s)",
intval(\$foo), intval(\$foo2));
mysql_query(\$query);
``````

Footnote: I am aware that when using `sprintf` like this, one could also handle integer values by just using `%d` instead if `%s` as the format specifier. However, I believe that proving you are correctly escaping variables should be possible by just looking at one place (the parameter list) instead of multiple places (did I use `intval` on the variable? or maybe I did not, but I 'm using `%d` in the format string so I 'm still OK?). It may sound counter-intuitive, but it's more robust in the face of modifications.

Friday, September 16, 2022
1

use the jquery `:selected` a little bit of documentation is here http://api.jquery.com/selected-selector/

That works in an option select menu

Edit

and a copy of the code for

``````\$('#source').change(function () {
if (\$('#source option:selected').text() == "France"){
\$('.cities').hide();
\$('#source2a').show();
} else if (\$('#source option:selected').text() == "Germany"){
\$('.cities').hide();
\$('#source2b').show();
} else if (\$('#source option:selected').text() == "India"){
\$('.cities').hide();
\$('#source2c').show();
} else {
\$('.cities').hide();
} });
``````
Sunday, December 18, 2022
3

try this:

``````<?php
\$location= \$_GET['location'];
\$language= \$_GET['language'];
\$name= \$_GET['name'];

if(\$location!=""){
\$where[] = " `location` = '".mysql_real_escape_string(\$location)."'";
}
if(\$language!=""){
\$where[] = " `language` = '".mysql_real_escape_string(\$language)."'";
}
if(\$name!=""){
\$where[] = " `name` = '".mysql_real_escape_string(\$name)."'";
}
\$where_clause = implode(' OR ', \$where);
//same code for the other 2 options

\$sql_json = "SELECT * FROM myTable WHERE \$where_clause ORDER BY id DESC";
?>
``````
Thursday, September 22, 2022
Not the answer you're looking for? Browse other questions tagged :

Share