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
