DISTINCT not working

[rmelino]

Hello,

 

Hoping someone can help...

 

I am pulling records from a db of cities and state abbrevs.  My db has many duplicate city names but i want to echo out only the distinct ones.  My query is based on a radius around a city and i think this might be what is tripping up the DISTINCT mysql query.  Here is what i have:

	$teachradius = 50;
$add_under = array(" " => "_");
$query = sprintf("SELECT DISTINCT city, state_abbrev, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM cities HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20",
  mysql_real_escape_string($lat_i),
  mysql_real_escape_string($lng_i),
  mysql_real_escape_string($lat_i),
  mysql_real_escape_string($teachradius));
$result = mysql_query($query);
while ($row = @mysql_fetch_assoc($result)){
echo '<li><a href="/' . strtolower($row['state_abbrev']) . '/' . strtolower(strtr($row['city'],$add_under)) . '.html">' . ucwords($row['city']) . ', ' . $row['state_abbrev'] . ' Dogs</a></li>',"\n";
}

 

Can you see what I might be doing incorrectly in my query so that i can echo out distinct city names / state abbrevs only?

 

Thanks in advance...

 

[jcbones]

Have you tried to EXPLAIN the SELECT statement?  Sometimes DISTINCT doesn't like to be run with an ORDER BY clause.  EXPLAIN should tell if that is a problem.