select within 20 kilometers based on latitude/longitude

Question 1

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.

Question 2

SELECT  *
FROM    mytable m
JOIN    mytable mn
ON      ACOS(COS(RADIANS(m.latitude)) * COS(RADIANS(mn.latitude)) * COS(RADIANS(mn.longitude) - RADIANS(m.longitude)) + SIN(RADIANS(m.latitude)) * SIN(radians(mn.latitude))) <= 20 / 6371.0
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
                )
        AND ACOS(COS(RADIANS(m.latitude)) * COS(RADIANS(mn.latitude)) * COS(RADIANS(mn.longitude) - RADIANS(m.longitude)) + SIN(RADIANS(m.latitude)) * SIN(radians(mn.latitude))) <= 20 / 6371.0
WHERE   m.name = 'grey river'

Question 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.

Question 4

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.

Question 5

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

That works in an option select menu

I am updating your Jfiddle now if you can give me a little more info about what you want done.

Edit

Here is an updated jfiddle with your answer. http://jsfiddle.net/stAAm/7/

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();
        } });

Question 6

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";
?>

select within 20 kilometers based on latitude/longitude

Answers

php,mysql,select