Locating users within a radius of another users zip code

Question

I have a site and am allowing users to search for other users within a specified radius of their own zip code. I first have to locate the latitude and longitude of the user and use that data to calculate and find other users. I've prepared the following query based on the 2 required tables but am getting errors that another alias is needed. If I hard code the values then parts of the query work find when I split them out, but with multiple joins and subqueries I've been unsuccessful. Looking for any guidance or help. Query and table structures included below.

Edited query and data set based on feedback. No longer receiving errors but am not getting any data returned for the query.

SQL Statement

SELECT u.username, u.zip, m.longitude,
  m.latitude,
  m.zip from users u, zipcodes m  
LEFT JOIN (SELECT  s.zip,
  s.username from users s) t
 on m.zip =  t.zip
  and t.username = 'tester55'
  INNER join ( SELECT zip, city, state, ( 3959 * acos( cos( radians(@m.latitude) ) * cos( radians( latitude ) ) 
    * cos( radians( longitude ) - radians(@m.longitude) ) + sin( radians(@m.latitude) ) * sin(radians(latitude)) ) ) AS distance 
FROM zipcodes
HAVING distance < 5000
ORDER BY distance) as RelevantCodes
  ON (t.zip=RelevantCodes.zip)

User Table

    username     zip
-----------------------
    tester55     60608
    Tester1      60608
    iosuser1     73120
    Tester3      60608

Zip Code Table

latitude     longitude     zip
--------------------------------
41.8515      -87.6694      60608
35.5835      -97.5638      73120
36.3169      -94.1545      72758

Answer 1

I don't have the time right now to work out a correct query, but I'll show what is wrong with the existing query at a first glance.

1) Regarding your actual question: The error message reads ERROR 1248 (42000): Every derived table must have its own alias and already tells what is wrong. The subquery in the following part

LEFT JOIN (select s.zip,
  s.username,
  m.longitude,
  m.latitude,
  m.zip
from users s) on u.zip =  s.zip

needs its own alias, so that should eventually be

LEFT JOIN (select s.zip,
  s.username,
  m.longitude,
  m.latitude,
  m.zip
from users s) t on u.zip =  t.zip

(note the t after the closing parenthesis and the replacement of s.zip by t.zip). But even if you do that, you will run into problem 2 (see next section).

2) The subquery

(select s.zip,
        s.username,
        m.longitude,
        m.latitude,
        m.zip
        from users s)

will throw another error because the table m is not known within that subquery.

If you are able to work it out correctly, I suggest you edit your question and let us know the result. If not, please post a comment or edit your question, describing what other problems you encounter.

EDIT

I now have worked out a solution for you:

SELECT zipcodes.zip,
       (3959 * acos(cos(radians(zipcodes.latitude)) *
                    cos(radians(center.latitude)) *
                    cos(radians(zipcodes.longitude ) -
                        radians(center.longitude)) +
                    sin(radians(zipcodes.latitude)) *
                    sin(radians(center.latitude)))) AS distance FROM
  ( SELECT users.username,
           users.zip,
           zipcodes.latitude,
           zipcodes.longitude FROM
    (users JOIN zipcodes ON users.zip = zipcodes.zip)
    WHERE (username='Tester1')
  ) center, zipcodes
HAVING (distance < 5000)
ORDER BY distance;

This selects all ZIP codes which are within the desired distance, ordered by distance.

As you may notice, I have simplified the code. Please note also that I have tested this based on your old data (the data you have shown before your edit), but using Tester1 instead of tester55.

You indeed need the HAVING instead of WHERE when filtering, so my initial statement regarding that was misleading. The reason is that MySQL does not allow computed columns (in this case: distance) in WHERE clauses, but in HAVING clauses. This is non-standard behavior; usually, you have to solve this problem another way. I won't elaborate on that because it is an additional possibly worrying thing which is not closely related to your original question.

EDIT 2

Finally, here is the version which gives the username in addition:

SELECT zipcodes.zip,
       users.username,
       (3959 * acos(cos(radians(zipcodes.latitude)) *
                    cos(radians(center.latitude)) *
                    cos(radians(zipcodes.longitude ) -
                        radians(center.longitude)) +
                    sin(radians(zipcodes.latitude)) *
                    sin(radians(center.latitude)))) AS distance FROM
  ( ( SELECT users.username,
             users.zip,
             zipcodes.latitude,
             zipcodes.longitude FROM
      (users JOIN zipcodes ON users.zip = zipcodes.zip)
      WHERE (username='Tester1')
    ) center, zipcodes) INNER JOIN users ON zipcodes.zip = users.zip
WHERE (users.username <> 'Tester1')
HAVING (distance < 5000)
ORDER BY distance;

Please note that you need the third from last line to filter out the user you are calculating the distances for. If you don't do that, the result will include a row with the user itself (in this case: Tester1) and a distance of (nearly) 0.

EDIT 3

The following is a version as requested by the comments below (desired seeking radius stored as additional field seekingdistance in table users):

SELECT zipcodes.zip,
       users.username,
       center.seekingdistance,
       (3959 * acos(cos(radians(zipcodes.latitude)) *
                    cos(radians(center.latitude)) *
                    cos(radians(zipcodes.longitude ) -
                        radians(center.longitude)) +
                    sin(radians(zipcodes.latitude)) *
                    sin(radians(center.latitude)))) AS distance FROM
  ( ( SELECT users.username,
             users.zip,
             users.seekingdistance,
             zipcodes.latitude,
             zipcodes.longitude FROM
      (users JOIN zipcodes ON users.zip = zipcodes.zip)
      WHERE (username='Tester1')
    ) center, zipcodes) INNER JOIN users ON zipcodes.zip = users.zip
WHERE (users.username <> 'Tester1')
HAVING (distance < center.seekingdistance)
ORDER BY distance;