Query MySQL database and display using variable from response

Question

I got this far by reading here on Stack Overflow. I have a table full of delivery hubs. Table structure is:

name - name of hub
zip - zip code of hub
lng - longitude of hub
lat - latitude of hub
radius - delivery radius

I am currently using this to get the closest delivery hub:

$dist1 = '20';

$pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare("SELECT name, 
       lat, 
       lng, 
       zip, 
       radius, 
       3956 * 2 * Asin(Sqrt(Power(Sin(( :lat - lat ) * Pi() / 180 / 2), 2) + 
                                            Cos(:lat * Pi() / 180) * Cos( 
                                            lat * Pi() / 180) * 
                                            Power(Sin(( :lng - lng ) * Pi() 
                                                           / 
                            180 / 2), 2))) AS dist 
FROM   hubs 
WHERE  lng BETWEEN ( :lng - :dist1 / Cos(Radians(:lat)) * 69 ) AND ( :lng + 
                          :dist1 / Cos(Radians(:lat 
                                      )) * 69 ) 
       AND lat BETWEEN ( :lat - ( :dist1 / 69 ) ) AND ( :lat + ( :dist1 / 69 ) ) 
HAVING dist < :dist1 
ORDER  BY dist 
LIMIT  100 ");

$stmt->execute([
  'lng' => $lng,
  'lat' => $lat,
  'dist1' => $dist1
]); 

while ($row = $stmt->fetch()) {

  if ($row['radius'] >= $row['dist']){

    echo $row['name'];
    echo " - ";
    echo $row['zip'];
    echo " - ";
    echo $row['dist'];
    echo "<br>";

  }

}

I would optimally like to eliminate the $dist1 variable from the query and instead only display the closest delivery hubs that deliver in that radius set by the hub, using only MySQL, not PHP (as I am using now).

How can I do this?

mysql.rjweb.org/doc.php/latlng This should help you with your task. I did something from scratch as well but switched to other solutions quickly. Why invent the wheel new if there are a ton of APIs and Packages out there which do the trick? Even the Geo-Based search. If you have the chance to use this I highly recommend this to you, just pick something what fits your needs, maybe change a little bit your data structure if needed and off you go. Let me know if you want some more info on that. I use name/zip/city as search query to find locations from my database nearby — floGalen
– floGalen, Commented Sep 23, 2020 at 9:21
this question is more suitable on this site: codereview.stackexchange.com — Argee
– Argee, Commented Sep 23, 2020 at 10:41

tony · Accepted Answer · 2020-09-24 08:51:53Z

1

Easy, change your MySQL to this:

SELECT name, 
       lat, 
       lng, 
       zip, 
       radius, 
       3956 * 2 * Asin(Sqrt(Power(Sin(( :lat - lat ) * Pi() / 180 / 2), 2) + 
                                            Cos(:lat * Pi() / 180) * Cos( 
                                            lat * Pi() / 180) * 
                                            Power(Sin(( :lng - lng ) * Pi() 
                                                           / 
                            180 / 2), 2))) AS dist 
FROM   hubs 
WHERE  lng BETWEEN ( :lng - radius / Cos(Radians(:lat)) * 69 ) AND ( :lng + 
                          radius / Cos(Radians(:lat 
                                      )) * 69 ) 
       AND lat BETWEEN ( :lat - ( radius / 69 ) ) AND ( :lat + ( radius / 69 ) ) 
HAVING dist < radius 
ORDER  BY dist 
LIMIT  1

answered Sep 24, 2020 at 8:51

tony

6346 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query MySQL database and display using variable from response

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related