0

I've looked and looked and tried and tried with no success. I have a query that I use to display users within a certain distance range. It works great, but it returns all users from my users table, and I want it to only return users where the value in account_type is equal to '1'. So basically different kinds of account types share my users table and on this page I only want one type of user to be display. I've tried all sorts of things, including joining the same table which I know makes no sense and it didn't work anyway. Basically, I would like to know where in this query I can add a 'WHERE' clause to check the column named 'account_type'.

Here's my functional query:

SELECT `user_id`, ( 3959 * acos( cos( radians('".$lat."') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('".$lng."') ) + sin( radians('".$lat."') ) * sin( radians( lat ) ) ) )  AS distance FROM users   HAVING distance <= '".$dist."'  ORDER BY distance

Since I only want to return users from that table that have account_type = 1, I tried doing many different variations of the following, with no success:

SELECT `user_id`, `account_type`, ( 3959 * acos( cos( radians('".$lat."') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('".$lng."') ) + sin( radians('".$lat."') ) * sin( radians( lat ) ) ) )  AS distance FROM users   ***WHERE `account_type` = '1'*** HAVING distance <= '".$dist."'  ORDER BY distance

SELECT `user_id`, `account_type`, ( 3959 * acos( cos( radians('".$lat."') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('".$lng."') ) + sin( radians('".$lat."') ) * sin( radians( lat ) ) ) )  AS distance FROM users   HAVING distance <= '".$dist."'  ***WHERE `account_type` = '1'*** ORDER BY distance

any many others though I won't pollute this topic any further. Can someone please tell me what I'm doing wrong? Thank you

Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
user1000665
  • 41
  • 4
  • Simply try using `WHERE` only like this `"SELECT ......... FROM users WHERE account_type = '1' AND distance <= '".$dist."' ORDER BY distance"` – rhavendc Apr 11 '16 at 05:51
  • Thanks. So I tried what you said, SELECT `user_id`, `account_type`, ( 3959 * acos( cos( radians('".$lat."') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('".$lng."') ) + sin( radians('".$lat."') ) * sin( radians( lat ) ) ) ) AS distance FROM users WHERE account_type = '1' AND distance <= '".$dist."' ORDER BY distance and get mysql error – user1000665 Apr 11 '16 at 05:55
  • Is your query `"SELECT ......... FROM users WHERE account_type = '1' HAVING distance <= '".$dist."' ORDER BY distance"` works? It should work. It's correct to put `WHERE` clause before `HAVING`, and it's okay to use `HAVING` without the `GROUP BY`. **Maybe there is/are no users with account_type of 1 and less than the given distance in your table** – rhavendc Apr 11 '16 at 06:10
  • There are a whole bunch, and i'm going out 100 miles and there are bunch within 1 mile even. Thanks though. – user1000665 Apr 11 '16 at 06:15
  • Your question is not nearly as bad as the one quoted here, but you might benefit from this: https://ericlippert.com/2014/03/05/how-to-debug-small-programs/ ... could you try breaking the problem into smaller parts? – alexanderbird Apr 11 '16 at 06:32
  • Since you know how to get users of a certain account type, and how to get users that match your distance logic, I would try combining the two queries in the format: `SELECT whatever FROM users WHERE id IN(SELECT id FROM users WHERE --account stuff--) OR id IN(SELECT id FROM users HAVING --distance stuff--)`. If that works you know it's not a logic problem, it's a syntax problem – alexanderbird Apr 11 '16 at 06:34
  • Recommend that you use WHERE instead of HAVING as its used to filter after GROUP BY. ..... WHERE account_type=1 AND distance <= '".$dist."' ORDER BY distance . – blokeish Apr 11 '16 at 07:58

1 Answers1

0

@rhavendc was correct. I'm a moron. The account that I created to test this I used some far away location and though there are more than 20 test accounts on my site, there were none within 100miles of the crazy location my test account was using as far as those matching account_type='1'

So once I realized that it took 2 seconds to just use this query to get the proper result

$qry="SELECT user_id, ( 3959 * acos( cos( radians('".$lat."') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('".$lng."') ) + sin( radians('".$lat."') ) * sin( radians( lat ) ) ) ) AS distance FROM users WHERE account_type = '1' HAVING distance <= '".$dist."' ORDER BY distance";

Thanks for everyone's input. I apologize for my stupidity.

user1000665
  • 41
  • 4