PHP：具有和不在mysql查询中工作的组合

Question

I have a table named Messages where one entry contains the same as the following:

longtitude = 4.867478333333334
latitude = 52.31819833333334
username = 'pb'  

If I query on the table with the below:

$longitude=4.867478333333334
$latitude=52.31819833333334
$username='pb'
$search=''
$visibledistance=2

using the below mysql query:

$query=("SELECT 
  subject,username,message,timestamp,(
    6371 * acos (
        cos ( radians('$latitude') )
      * cos( radians( Messages.latitude ) )
      * cos( radians( Messages.longitude ) - radians('$longitude') )
      + sin ( radians('$latitude') )
      * sin( radians( Messages.latitude ) )
    )
  ) AS distance FROM Messages WHERE username ='pb'  AND ((subject LIKE        '%$search%') OR (message LIKE '%$search%'))  GROUP BY subject  

HAVING distance < '$visibledistance' 
ORDER BY timestamp");
$sth = mysql_query($query);

I get no results. If I remove HAVING distance < '$visibledistance' I get all entries matching the remaining criterias, so there´s nothing wrong with anything but the distance part of it. Could someone help me to understand how I can do this right?

Thanks!

Answer 1

if you have numeric value you should avoid the single quotes around the vars

and you should use concat for like

(for having if you use '$visibledistance' you are eval HAVING distance < '4' (you are eval you distance respect to a string)

Looking to your code
you are not using an aggregation function so you don't need having you could use where (but noy alias) for fliter and distcint instead of group by

  $query=("SELECT distinct
    subject,username,message,timestamp,(
      6371 * acos (
          cos ( radians($latitude) )
        * cos( radians( Messages.latitude ) )
        * cos( radians( Messages.longitude ) - radians($longitude) )
        + sin ( radians($latitude) )
        * sin( radians( Messages.latitude ) )
      )
    ) AS distance FROM Messages WHERE username ='pb'  AND ((subject LIKE concat('%', '$search', '%') 
            OR (message LIKE concat('%','$search','%'))    

  WHERE (
      6371 * acos (
          cos ( radians($latitude) )
        * cos( radians( Messages.latitude ) )
        * cos( radians( Messages.longitude ) - radians($longitude) )
        + sin ( radians($latitude) )
        * sin( radians( Messages.latitude ) )
      )
    ) < $visibledistance 
  ORDER BY timestamp");