Geek Answers Handbook

Find all records in the database located at a certain distance from the set of lat and long points

I have seen all the examples and this is what I got so far.

my table is simple:

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

here is 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 (): the supplied argument is not a valid MySQL result resource

+4
source share
2 answers

Firstly, "long" is a reserved keyword in MySQL. You will need to wrap it in reverse measures:

 SELECT `long`,lat FROM schools

A complete list of reserved keywords can be found here: Reserved words

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

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

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

This should give you the error code and the error message generated by MySQL. The query looks fine, except for the problem with the keyword, but it will definitely tell you.

+3
source

Honestly, I recommend that you just take all the lats / longs for all schools and scroll through the Haversine function through your PHP code.

+1
source

More articles:


All Articles