Page 2 of 2

Posted: Wed Dec 20, 2006 8:02 pm
by RobertGonzalez
WanamakerStudios wrote:If I store the distances in an array, how can I sort the final results by distance then?
Have you searched these forums for 'Zip Code Search' (all terms)? This has been done quite a few times before (I think I have posted several solutions to this before as well).

You shouldn't have to make temp tables. You can use the array sorting functions of PHP to handle your distance sorts. You should be able to execute the proximity matching with one query and get all the records you want in another. You can combine the two, but you will be looking at a fairly slow query (maybe 0.3 to 0.5 seconds). 9 seconds suggests your indexes are bad.

Posted: Wed Dec 20, 2006 8:36 pm
by Begby
Everah wrote:
WanamakerStudios wrote:If I store the distances in an array, how can I sort the final results by distance then?
Have you searched these forums for 'Zip Code Search' (all terms)? This has been done quite a few times before (I think I have posted several solutions to this before as well).

You shouldn't have to make temp tables. You can use the array sorting functions of PHP to handle your distance sorts. You should be able to execute the proximity matching with one query and get all the records you want in another. You can combine the two, but you will be looking at a fairly slow query (maybe 0.3 to 0.5 seconds). 9 seconds suggests your indexes are bad.
I believe the main issue was not the zip code part as that was pretty much tackled in his SQL already, but he was having trouble wrapping his head around joining all the tables together that he needs. In the follow up posts it appears he has used my suggestions to get this into one query and eliminated the temp tables. (perhaps you just skimmed those posts :wink: ) Now he is just having minor troubles getting unique records.

Posted: Thu Dec 21, 2006 7:15 am
by WanamakerStudios
And my lack of replies may have added to a bit of the problem as well :wink:

I have been able to combine everything into one query and added a DISTINCT request so that I don't return multiple results of the same vehicle. So I would say that this one has been solved! Thank you for all of the help!

Posted: Thu Dec 21, 2006 7:31 am
by Begby
But is it fast?

Posted: Thu Dec 21, 2006 7:38 am
by WanamakerStudios
Yes! Very much so! It turned out that the optimized coding needed to be coupled with a slightly different indexing method. I have another post (viewtopic.php?t=60968) where I need to know how to take multiple JOINs of the same table and combine them against multiple fields in another table. Having coded the way I have it now slows it down slightly ... but when I look at the true numbers ... it isn't too bad.