December 4, 2017 at 4:38 pm
Hi,
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(center.latitude)) *
cos(radians(ZIPCodes.longitude ) -
radians(center.longitude)) +
sin(radians(ZIPCodes.latitude)) *
sin(radians(center.latitude)))) AS distance FROM
( ( SELECT usersearch_answers.username,
usersearch_answers.zipcode,
ZIPCodes.latitude,
ZIPCodes.longitude FROM
(usersearch_answers JOIN ZIPCodes ON usersearch_answers.zipcode = ZIPCodes.zipcode)
WHERE (ZIPCodes.zipcode='33133')
) center, ZIPCodes) INNER JOIN usersearch_answers ON ZIPCodes.zipcode = usersearch_answers.zipcode
WHERE (usersearch_answers.username <> '$userLoggedIn')
HAVING (distance < 5000)
ORDER BY distance");
December 4, 2017 at 4:47 pm
I guess the first thing I would do is write a query that generates a list of zip codes based on the lat/long of the target zip code and distance. Then this can be used to identify the users based on zip codes.
December 4, 2017 at 5:49 pm
Thanks. I figured it out. How do you close this topic?
December 5, 2017 at 8:36 am
First, you post how you fixed your problem to help others that may come along and see this thread with a similar problem.
Second, you really don't "close" the thread
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply