AND (POW((69.1*(Longitude-"-93.261385")*cos(37.148949/57.3)),"2")+POW((69.1*(Latitude-"37.148949")),"2"))<(1*1) ORDER BY POW((69.1*(Longitude-"-93.261385")*cos(37.148949/57.3)),"2")+POW((69.1*(Latitude-"37.148949")),"2") ASC Limit 0,300
This query is returning results that are over 1 mile away, when it should not be.
For a zip code script the distance needs to be calculated outside of the query. The query usually returns the data related to listings within a circumference to the users zip code. There are an infinite number of points within the circle. The distance to each is something that can be calculated in PHP after the points are returned from the database. What I have done is, after getting all the points in the circle returned from the query, during the while loop of assigning the fetch_array to my own array, I calculate distance and add that to my array.
Here's a snippet from a class I had written to do this. If you want, I can post the whole class or you can PM me if you'd like. Hope it helps...
PS, To sort this you can use whichever array sorting functions best suits your array to sort by the 'distance' field.
Thanks Everah, I appreciate the help. I am using the query to avoid pulling records outside of a certain distance to make it more efficient. Once the results are returned I use a function to calculate the distance.
I didn't test it before the clause because I hadn't written it yet. It's a dynamic query that is rather large, (over 1000 lines or code to create it), and there are some rogue OR statements in it that I need to resolve. So sometimes it works and sometimes it doesn't, but I know how to fix it so I am good to go on this one.