Why am I not able to get an array from this query using PHP / MySQL and latitude / longitude?

Question

I have two tables but for the purposes of this question, I will only use one. I am using the latitude and longitude for a city to find airports within 50 miles. As far as I know, it's working but I can't seem to put this into an array with mysql_fetch_array... It's something stupid or something small... it always seems to be one or the other. After trying several things, this is where I am at right now with the code:

<?php 
  require('dbconnect.php');
  //airports Table Columns
    //iata_code
    //airport_name
    //airport_name_clean
    //city_id
    //airport_lat
    //airport_long

$cityLat = "25.788969"; //Miami
$cityLong = "-80.226439"; //Miami
$distance = "10"; //miles?

$airportQuery = mysql_query("select airport_name, 
   ( 3959 * acos( cos( radians($cityLat) ) 
          * cos( radians( locations.lat ) ) 
          * cos( radians( locations.lng ) - radians($cityLong) ) 
          + sin( radians($cityLat) ) 
          * sin( radians( locations.lat ) ) ) ) AS distance 
from airports 
and locations.lat between X1 and X2 
and locations.Long between y1 and y2
having distance < $distance ORDER BY distance;
");

while($airports = mysql_fetch_array($airportQuery))
    {
        echo $airports['airport_name'] . "<br />";
    }

?>

As always, any help will be greatly appreciated. Thank you so much for your help!

So what are you seeing from your echo? Have you checked your query return for errors? — Alan Moore, Mar 08 '12 at 15:06
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in (...file path...) on line 27 — NotJay, Mar 08 '12 at 15:08
I did try to put the query in the php myadmin sql to test and I got an error so there must be an issue with the query?!?! — NotJay, Mar 08 '12 at 15:13
MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and locations.lat between X1 and X2 and locations.Long between y1 and y2 having' at line 8 — NotJay, Mar 08 '12 at 15:14
Thanks Alan... That didn't work but I think you're right about using WHERE instead of AS. — NotJay, Mar 08 '12 at 15:18
If I am not mistaken, `having` clause works with `GROUP BY` clause only... — Fahim Parkar, Mar 08 '12 at 15:31

score 0 · Accepted Answer · answered Mar 08 '12 at 15:08

Your query looks suspicious, I think you have an and when you should have a where:

$airportQuery = mysql_query("select airport_name, 
   ( 3959 * acos( cos( radians($cityLat) ) 
          * cos( radians( locations.lat ) ) 
          * cos( radians( locations.lng ) - radians($cityLong) ) 
          + sin( radians($cityLat) ) 
          * sin( radians( locations.lat ) ) ) ) AS distance 
from airports 
WHERE locations.lat between X1 and X2 
and locations.Long between y1 and y2
having distance < $distance ORDER BY distance;
");

You should check the return value for errors, that might be informative as well.

Why am I not able to get an array from this query using PHP / MySQL and latitude / longitude?

1 Answers1