PHP: Combination of having and where not working in mysql query

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!

*"If I query on the table with the below:"* - Did you close off each statement? this being a technical sidenote for future (newbie) visitors who may think that that is valid syntax in php. — Funk Forty Niner, Mar 09 '17 at 19:02
On the sidenote, i hope that this will never go into the code that is executed from the internet. It is the very definition of the SQL injection. — v010dya, Mar 09 '17 at 19:04
See http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php and http://stackoverflow.com/documentation/php/5828/pdo/2685/preventing-sql-injection-with-parameterized-queries#t=201703091904411986445 for more information — WOUNDEDStevenJones, Mar 09 '17 at 19:05
Thanks, closeoff in which way do you mean? No, this is still at test stage - later I´ll amend all my php code so that they´re injection proof. — Corey Hart, Mar 09 '17 at 19:20
@CoreyHart start from the beginning with the right foot, listen to the advice a newbie can give you. Your code should be ok from the beginning — Lelio Faieta, Mar 09 '17 at 20:56

ScaisEdge · Accepted Answer · 2017-03-09T20:42:50.407

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");

edited Mar 09 '17 at 20:42

answered Mar 09 '17 at 19:03

ScaisEdge

131,976
10
91
107

Thanks, I removed all single quotes from numeric values, but the result is the same. My suspicion is that this is related to addressing the latitude and longitude fields with "Messages.", since I saw other people dealing with this same task, who were encouraged to use JOIN, but I´m not sure if I really need to do that. – Corey Hart Mar 09 '17 at 19:29
join or where condition do the same result .. so this i not the problem .. instead check for real data .. ... try get the real query builded by php an try to exec it in a sql IDE ....(could be also that $search = '' is not good) – ScaisEdge Mar 09 '17 at 19:33
What I mean is that I got it saying "messages.latitude" - never worked with adressing a column before this way. The search works fine as it is.. if I remove the HAVING, GROUP BY and ".............. AS distance" parts, things work fine - this is how it used to be, but now I need part of the query to take into consideration the coordinates, which is what I´m struggling with. – Corey Hart Mar 09 '17 at 19:43
I'm sorry but .. I realized now that it seems that you are using group by and having no one aggregate function .. (like sum. min.) .. because you are using group by? .. i have update the answer with code using where instead of having – ScaisEdge Mar 09 '17 at 19:47
Allright thanks, still no luck but at least I have a better overview without the HAVING part - not sure why that´s used in all examples I found about this! – Corey Hart Mar 09 '17 at 19:58
Hmm, is it not doing some twice here, querying on the whole longi/lati-formula? – Corey Hart Mar 09 '17 at 20:03
I don't understand you last comment please explain better (in a simple way) .. what do you mean – ScaisEdge Mar 09 '17 at 20:05
You have ( 6371 * acos ( cos ( radians($latitude) ) * cos( radians( Messages.latitude ) ) * cos( radians( Messages.longitude ) - radians($longitude) ) + sin ( radians($latitude) ) * sin( radians( Messages.latitude ) ) ) appearing twice. Is that intentional? – Corey Hart Mar 09 '17 at 20:08
Yes ... .. the use of an alias in a where condition is not allowed in sql ... and you have not aggreagtion function so you don't should use group by and having ..-- having filter the result ... where filter the selected rows .. – ScaisEdge Mar 09 '17 at 20:10
Ok,so I rewrote it to something that I got working:your inspiration and help got me there! $query=("SELECT subject,username,message,timestamp FROM Messages WHERE username ='pb' AND ((subject LIKE '%$search%') OR (message LIKE '%$search%')) AND ( 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"); $sth = mysql_query($query); – Corey Hart Mar 09 '17 at 20:22
Nope, now it does the job as it should! Do you see any issues with this approach? – Corey Hart Mar 09 '17 at 20:39
The second answer i provide .. is the right one .. and is in the right way .. (i remove the first because don't take in right consideration the absence of aggregation function) so .. the answer provided have not particular issue .. is written in the right way .. – ScaisEdge Mar 09 '17 at 20:42
@CoreyHart well if my answer is right please mark it as accepted ...see how here http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – ScaisEdge Mar 09 '17 at 20:43
I will indeed, but just wondering if my ammended version of your answer, cutting out the double query on distance and the "distinct" part is somewhat slower, would cause some issues or..? – Corey Hart Mar 09 '17 at 20:55
Ah I think I got it.. you posted both answers in the same box? That´s why it´s double then :D – Corey Hart Mar 09 '17 at 20:57

PHP: Combination of having and where not working in mysql query

1 Answers1