如何仅在经度和纬度在给定距离内时才显示查询结果

Question

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 1

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, this query will not be able to leverage any indexes on the fields in the where clause with your typical B-tree index. That is because you have a wildcard at the beginning of the LIKE definition for the search values and typical b-tree indexes only support prefix match use cases. 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 things, special field and index types optimized for working with spatial data.