MSQL Query

by 13 replies
15
Im using the following query which returns a correct result, however it only returns one result when I know for sure there is more than one in the radius. Any advice would be appreciated.

SELECT DISTINCT zip, ( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance FROM zips HAVING distance < 40
#programming #msql #query
  • try removing the keyword DISTINCT and see if that helps.
    --Jack
  • Well Im sure that there are more distinct rows, but I did notice this, when I take the MSQL resource and change it to an array and print_r it then it shows one result, if I change it to an assoc array and iterate through and extract it shows all the results. So I got it working now but would still like to know why it only shows all the results with an assoc array and extracting.
    • [1] reply
    • Have you tried using " GROUP BY zip " instead of DISTINCT ?
      • [1] reply
  • Of course mysql_fetch_assoc is pulling in all the rows, based on what you posted you have it in a while loop and the mysql_fetch_array isn't in a loop.
    • [1] reply
    • Yes I didnt post correctly, sorry about that, I was on the tail end of a 19 hour session and half asleep.
      • [1] reply
  • Essentially GROUP BY lets you use aggregate operators (MAX, SUM, GROUP_CONCAT, ..., or a HAVING clause). DISTINCT just removes duplicates.
Join the discussion

Next Topics on Trending Feed

  • 15

    MSQL Query

    Im using the following query which returns a correct result, however it only returns one result when I know for sure there is more than one in the radius. Any advice would be appreciated. SELECT DISTINCT zip, ( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance FROM zips HAVING distance < 40