Hi Wendell,
What you've proposed is not a bad solution. There is some initial work to set up the stuff for the existing agents, but if you define a limit to the range that the agent works in, then you can take the result stuff it into a comma delimited string and place that into the agent_zip_codes field...then the realtime time query is a simple IN (zip1, zip2, zipN) statement and should be faster
Bastien
From: "Wendell Frohwein" <wendell@xxxxxxxxxxxx> To: "'graeme'" <graeme_foster@xxxxxxxxxxxxxxxx> CC: <php-db@xxxxxxxxxxxxx> Subject: RE: Slow Query Date: Thu, 13 Jan 2005 00:30:34 -0800
Thank you Graeme.
But unfortunately, there are a 700 + agents. This number keeps growing every day as well. I had a vision of another idea. I don't know how much time it will save. In the table that stores all the agents zipcodes that they cover, I added longitude and latitude to the table.
So when my zip code radius function kicks in, it will find agents with there respective zip code.
Before it would take the property zip, query the zipcodes table with over 42,000 zip codes, then put those in an array, then search for agents from there.
The way I see it, I killed one step in the process. But at the time of me writing this letter I am still trying to get it to work. If I do, I will post up some numbers to see how much faster it is.
But I am still looking for a better way to do it.
Thanks once again to everyone.
-Wendell Frohwein
-----Original Message----- From: graeme [mailto:graeme_foster@xxxxxxxxxxxxxxxx] Sent: Thursday, January 13, 2005 12:02 AM To: Wendell Frohwein Cc: php-db@xxxxxxxxxxxxx Subject: Re: Slow Query
How many agents do you have?
If the number of agents is small then you could set up a temp table which has a link to the agent, the client, and a calculation of the distance between the two. Then do your search on this temp table. Once you have the answer you want delete the records for this client. (that should allow for more than one client entering data at the same time, although you may want to properly think that scenario through)
graeme.
Wendell Frohwein wrote:
>First of all I would like to thank anyone who lends a hand in this >matter. Here is what im working with. Redhat 9, PHP 5.0.2, >Apache/2.0.50, MySQL 4.1.8-standard Binary Installation for >pc-linux-i686, OpenSSL 0.9.7d, gcc version 3.2.2. PHP compiled with >mysql, and mysqli capability. > >This is how the script works: > >The client submits a property for an appraisal. >Based on the appraisal type and property zip code, It searches the >database for agents / appraisers with the zip code in question (property >zip code) in there coverage area (zip codes they are willing to do >appraisal work for). So when it finds a match, It adds that order to >there potential order list. It then waits for the to accept or decline >the order. If accepted the order becomes there's and alerts all the >other agents / appraisers that this order has already been accepted. The >do the work, send it back job is done. > >This works fine right now rather it be a single order placed by a >client, or a spreadsheet imported to the system with 2000 orders or >more. So now you're saying if it works, why are you here asking for >help? Well instead of searching agents with the exact zip code match, I >would like to search a radius of zip codes. The first search would be a >2 mile radius, the next search (incase the first did not return any >result) would be 5 mile radius. > >I purchased some software (php / mysql sql files) that contains every >zipcode in the united states along with a longitude and latitude for >each zip code. So the zip code script provided with this software allows >you to 1) enter a zip code and miles in radius, it will then spit out >all the zip codes in that radius into an array. 2) you can give it 2 zip >codes and it will tell you the distance in miles between the 2 (give or >take). > >So I rewrote my script to do the follow: >The client submits a property for an appraisal. >Based on the appraisal type and property zip code, It does a radius >search of 2 miles from the property zip. If no matches are found, it >does a 5 mile radius search. For every zip code returned by each radius >search, It searches the database for agents / appraisers with the zip >code in question (zip codes from radius search) in there coverage. > >This is the most stressful part I am assuming. This works fine for 1 - >10 orders. But when I import 30 or more, the script pretty much hangs as >well as mysql. I have to kill mysql with signal 9, start mysql it up >again. Then all is back to normal. I would paste the code in here but it >is really long and complicated. > >I was hoping on the based on the operation of the script, someone would >suggest a better and faster way to search zip code radius, while >matching the results to agents within the system. > >If I am asking for way to much time from someone, I apologize. If >someone is really interested in helping me sort this out, I could send >you code samples to see the process. > > > >-Wendell Frohwein > > >
-- Experience is a good teacher, but she sends in terrific bills.
Minna Antrim
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
