I have two tables:
1) ZIPCodes- ZIPCodes table contains all the zipcodes of the USA with latitude and longitude.
2) usersearch_answers- usersearch_answers table has a zipcode column with the zipcodes where users live.
I want to have two variables: zipcode that the user can specify, and a distance in miles that a user can also specify.
The results should return anyone withni the radus of the zipcode that the user specified. Now, the query below works as expected only when there's atleast one person in the users table with the zipcode that was specified. Nothing returns if the zipcode that entered is not contained in the table, even if there are nearby zipcodes. Can someone please pinpoint for me in this code where the problem is? What do I need to do in order to get the rest of the zipcodes working? Any help is greatly appreciated. Thanks in advance.
$data_query = mysqli_query($conn, "SELECT ZIPCodes.zipcode, usersearch_answers.username,
(3959 * acos(cos(radians(ZIPCodes.latitude)) *
cos(radians(ZIPCodes.longitude ) -
sin(radians(center.latitude)))) AS distance FROM
( ( SELECT usersearch_answers.username,
(usersearch_answers JOIN ZIPCodes ON usersearch_answers.zipcode = ZIPCodes.zipcode)
) center, ZIPCodes) INNER JOIN usersearch_answers ON ZIPCodes.zipcode = usersearch_answers.zipcode
WHERE (usersearch_answers.username <> '$userLoggedIn')
HAVING (distance < 5000)
ORDER BY distance");