Jump to content

ORDER BY help


pocobueno1388

Recommended Posts

I successfully found some code that will display all users within a certain radius of a specific zip code. Now I'm trying to order the users it returns sorted by distance from the same zip code.

 

Here is the query that gets me the list of zipcodes that are within a certain radius of another zip code -

SELECT distinct(zipcode) FROM zipcodes  WHERE (3958*3.1415926*sqrt((latitude-'.$lat.')*(latitude-'.$lat.') + cos(latitude/57.29578)*cos('.$lat.'/57.29578)*(longitude-'.$lon.')*(longitude-'.$lon.'))/180) <= '.$radius

 

Now here is my query that selects the users that have matching zip codes from the ones the previous query returns -

SELECT name FROM users WHERE zip IN (".implode(",", $zips).")"

 

So again, I'm trying to order the last query by distance from a specified zip. I have the long/lat of all the zipcodes if that's needed.

 

I feel like I need something like

ORDER BY zip ASC STARTING WITH [this zip code]

 

I'm not quite sure how to translate that to the query.

 

Thanks for the help!

 

 

 

Link to comment
Share on other sites

Try this and see how it works for you.

$query = "SELECT distinct(zipcode), 
(3958*3.1415926*sqrt((latitude-($lat))*(latitude-($lat)) + cos(latitude/57.29578)*cos(($lat)/57.29578)*(longitude-($lon))*(longitude-($lon)))/180) AS `distance` 
WHERE 
(3958*3.1415926*sqrt((latitude-($lat))*(latitude-($lat)) + cos(latitude/57.29578)*cos(($lat)/57.29578)*(longitude-($lon))*(longitude-($lon)))/180) 
<= $radius 
ORDER BY 
`distance` ASC";

Link to comment
Share on other sites

Thanks Pikachu2000, but that is only going to order the zip codes by distance, which I believe it's already doing. Either way, even if those come out in the correct order it still doesn't make my second query come out in the correct order.

 

Can you see a way to combine the first and second query so I can select the users information as well? I know there is a way to combine it, but it's just too much for me to wrap my head around!

Link to comment
Share on other sites

It wasn't clear whether you had the ORDERing taken care of or not, but if it turns out you need that, I've tested it against one of my databases, and it does work. As far as the rest of your request, I'm probably not the right one to try to answer it. I suspect it could be done with a JOIN, but that isn't one of my strong points.

Link to comment
Share on other sites

Fenway - I have a query that gets all the users in a specified radius of a given zip code. Now I'm just trying to order the users by distance from that zip code.

 

My first query just selects all the zip codes within that radius, then I am using a second query to select all the users with a matching zip code. I'm sure both of these queries can be combined and it will make it simpler to sort it, but I can't quite wrap my head around all of it.

Link to comment
Share on other sites

  • 2 weeks later...

fenway - I'm still not quite sure how/if that would work in my case. Yes, that would join the queries but wouldn't give me my desired results. I'm really not even sure if the queries can be combined and get the results I want.

 

I need to first get all the zipcodes that are in the radius, THEN get all the users with those zipcodes and order them by distance from the currently logged in users zipcode.

 

So lets say I had the following zipcodes returned from the first query and the logged in users zipcode was 76017:

 

76015 76019 76008 76028

 

I would want them ordered like:

76015, 76019, 76008, 76028

 

Because those are ordered from closest to furthest from the users zipcode.

 

Does this make sense?

Link to comment
Share on other sites

  • 2 weeks later...

I was finally able to figure it out. What I did was find the equation for getting the distance in miles, then just sorted them by distance.

 

Here is the query for anyone else who needs help with this:

 

SELECT 
  (((acos(sin((".$latitude."*pi()/180)) * sin((z.latitude*pi()/180))+cos((".$latitude."*pi()/180)) * cos((z.latitude*pi()/180)) *
  cos(((".$longitude."- z.longitude)*pi()/180))))*180/pi())*60*1.1515) as distance, u.name
FROM 
`zipcodes` z
RIGHT JOIN
users u
ON
u.zip = z.zipcode
HAVING 
distance <= ".$distance." 
ORDER BY 
distance

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.