Ricky T Ricky T - 3 months ago 8
SQL Question

How to Display query results only if longitude and latitude are in a given distance

How do I combine the two $sql querys into one $sql query. I want to enter some name, address, city or state and have it display only if it is within a specified longitude and latitude range. I have tried many ways to combine these $sql into one, but just can't figure out the logic. Help please.

$sql = "SELECT *, ( 3959 * acos( cos( radians(47.64585) ) * cos( radians( lat ) ) * cos( radians( longitude ) - radians(-117.159999) )
+ sin( radians(47.64585) ) * sin( radians( lat ) ) ) ) AS distance FROM table HAVING distance > 100";


The $sql above and the $sql below both work just fine when ran separately. I only included the $sql code above rather than all the connections and stuff that goes with it. I can supply the whole connection and display table code if needed. I will also be using longitude, latitude and distance variables for my current location. I just hard coded them to make my examples more readable hopefully.

$name = "text entered from search form";

$db= new pdo("mysql:host=localhost;dbname=$dbname",$username,$password);
$sql="SELECT * FROM table WHERE
companyname LIKE '%" . $name . "%'
OR
address LIKE '%" . $name . "%'
OR
city LIKE '%" . $name ."%'
OR
state LIKE '%" . $name ."%'";

$result = $db->query($sql);
$numrows=$result->fetch(PDO::FETCH_ASSOC);

if ($result != false) {
while($row=$result->fetch(PDO::FETCH_ASSOC)){
$client_id=$row['client_id'];
$companyname =$row['companyname'];
$address=$row['address'];
$city=$row['city'];
$state=$row['state'];

echo $client_id, $companyname, $address, $city, $state;

}
}
$result = null;

Answer

Just add the where clause from your second query to the first.

SELECT *,
    ( 3959 * acos( cos ( radians(47.64585) )
        * cos( radians( lat ) ) 
        * cos( radians( longitude ) - radians(-117.159999) ) 
        + sin( radians(47.64585) )
        * sin( radians( lat ) )
        )
    ) AS distance 
FROM table
WHERE     
    companyname LIKE '%" . $name . "%' 
    OR address LIKE '%" . $name . "%'
    OR city LIKE '%" . $name ."%'
    OR state LIKE '%" . $name ."%'
HAVING distance > 100

A warning though, at query will not be able to leverage any indexes on the fields in the where clause with you typical B-tree index. That is because you have wildcard at the beginning of the LIKE definition for the search values and typical b-tree index only supports prefix matche uses case. You might want to consider natural language search for this use case.

You also may need to consider using MySQL spatial extension if you are going to be making significant use of geospatial functionality. This will allow, amongst other thing special field and index types optimized for working with spatial data.

Comments