Find all records in database that are within a certain distance of a set of lat and long points

I've seen all the examples and here's what I got so far.

my table is simple:

schools (table name) - School_ID - lat - long - county - extrainfo

here's my code:

<?php

 $con = mysql_connect("xxx","xxx","xxx");



 if (!$con) {

            die('Could not connect: ' . mysql_error());

 } else {}



 mysql_select_db("xxx", $con);

 $latitude = "36.265541";

 $longitude = "-119.207153";

 $distance = "1"; //miles



 $qry = "SELECT *, (3958.75 * ACOS(SIN(" . $latitude . " / 57.2958)*SIN(lat / 57.2958)+COS(" . $latitude . " / 57.2958)*COS(lat / 57.2958)*COS(long / 57.2958 - " . $longitude . " / 57.2958))) as distance FROM schools WHERE (3958.75 * ACOS(SIN(" . $latitude . " / 57.2958)*SIN(lat / 57.2958)+COS(" . $latitude . " / 57.2958)*COS(lat / 57.2958)*COS(long / 57.2958 - " . $longitude . " / 57.2958))) <= " . $distance;

 $results = mysql_query($qry);
 if (mysql_num_rows($results) > 0) {
            while($row = mysql_fetch_assoc($results)) {
                            print_r($row);
            }
 } else {}

 mysql_close($con);

 ?>

but I get this error when I try to run it:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

From stackoverflow

• To be honest, I recommend you just grab all the lat/long for all the schools and loop through running a Haversine function through your PHP code.

  Calvin : Assuming that this query is being used to find schools near a given location, I don't think the distances being searched are going to be large enough to require taking the curvature of the earth into account. And the Earth isn't a perfect sphere in any case, so if you wanted a precise measurement, you'd have to use Vincenty's formulas.

  jottos : you could optimize this suggestion by creating an enclosing bounding box about the circle defined by the distance == radius, create a SQL query result with fewer results. then do a haversine distance on this set

  TheSoftwareJedi : no, YOU are PHP code.

  BobbyShaftoe : @Calvin, I'm not sure what you are trying to tell me exactly. I'm well aware that the Earth isn't a perfect sphere but using Haversine is pretty good and arguably the most common method in applications where absolute precision isn't required. @jottos, perhaps, but just getting them all and doing Haversine will get you a bit closer than what he has now. :)

• First, 'long' is a reserved keyword in MySQL. You'll need to enclose it in backticks like so:

  SELECT `long`,lat FROM schools
  

  Full list of reserved keywords can be found here: Reserved Words

  If you have access to a tool like phpMyAdmin, I recommend running your query testing there.

  Otherwise, try executing this in your code after running mysql_query():

  print(mysql_errno().' '.mysql_error());
  

  That should give you the error code and error message generated by MySQL. The query looks ok other than the keyword issue, but this will tell you definitively.

• This is so perfect. I've been looking for this script for weeks and this one did just the trick!! Thanks so much.