PhotoPost Community - View Single Post

CavySpirit · September 23rd, 2006, 01:53 PM

Well, I don't know the internal workings of mySQL, but when the zipData file has 800,000 records and when showcat.php has to access that file searching for a valid entry on every product, the performance went to hell. I don't know if making zipData indexed or not or some other technique would help, but I assumed that mySQL must have to do some kind of opening of the whole file or something when it is queried.

Soooo, my version now only checks that file when a zip/postal is created the first time in uploadproduct.php or changed in editproduct.php or changed on a search in showcat.php.

I now store the lat and long in the product file. And I also require that non-US/Canadian folks create an entry in the Googlemap application (thanks to vBulletin) before creating an ad in the classifieds. That way I can get their lat and long from the table that stores it by userid.

I've also added a flip on the search entry that allows the user to switch the location based search from entering a zip/postal code to entering a lat/long. And now my distance calculation is available to anyone and everyone in the world. And I've also changed the distance calculation in the application itself. I think I found what looks like a better one, and it allows me to pass an "M" or "K" and if it's K, I get back the distance in Kilometers instead of Miles (one simple multiplication in the function). So, on the showcat search info, I've also added an option to switch between miles and kilometers--and I store the preferences as a cookie.

I've been forced to use up valueable extra fields for 'city,' 'state/province,' and 'country.' Originally, I also used zip for non-US zip codes which I have to go back change now. In my opinion, location fields and contact info should absolutely be in the product file. Things like area code should be searchable and sortable. Same with city, state, country, lat & long for distance. A contact name. There are just basics about classified listings that should be there without having to devote so much time to hacking the system to get them in.

I've had to rely heavily on vBulletin userprofile fields to come up with the missing info and that wasn't easy.

I love this script, but I have spent a mountain of time trying to get it up to snuff on handling classified listings that are location based around the world, not just selling simple things over the internet. I'm almost there. I would be happy to send you back my files to take a look at what I ended up doing so that perhaps you can think about adding those features back into to future releases.

September 23rd, 2006, 01:53 PM	#7 (permalink)
CavySpirit Member Verified Customer Join Date: Sep 2002 Posts: 217	Well, I don't know the internal workings of mySQL, but when the zipData file has 800,000 records and when showcat.php has to access that file searching for a valid entry on every product, the performance went to hell. I don't know if making zipData indexed or not or some other technique would help, but I assumed that mySQL must have to do some kind of opening of the whole file or something when it is queried. Soooo, my version now only checks that file when a zip/postal is created the first time in uploadproduct.php or changed in editproduct.php or changed on a search in showcat.php. I now store the lat and long in the product file. And I also require that non-US/Canadian folks create an entry in the Googlemap application (thanks to vBulletin) before creating an ad in the classifieds. That way I can get their lat and long from the table that stores it by userid. I've also added a flip on the search entry that allows the user to switch the location based search from entering a zip/postal code to entering a lat/long. And now my distance calculation is available to anyone and everyone in the world. And I've also changed the distance calculation in the application itself. I think I found what looks like a better one, and it allows me to pass an "M" or "K" and if it's K, I get back the distance in Kilometers instead of Miles (one simple multiplication in the function). So, on the showcat search info, I've also added an option to switch between miles and kilometers--and I store the preferences as a cookie. I've been forced to use up valueable extra fields for 'city,' 'state/province,' and 'country.' Originally, I also used zip for non-US zip codes which I have to go back change now. In my opinion, location fields and contact info should absolutely be in the product file. Things like area code should be searchable and sortable. Same with city, state, country, lat & long for distance. A contact name. There are just basics about classified listings that should be there without having to devote so much time to hacking the system to get them in. I've had to rely heavily on vBulletin userprofile fields to come up with the missing info and that wasn't easy. I love this script, but I have spent a mountain of time trying to get it up to snuff on handling classified listings that are location based around the world, not just selling simple things over the internet. I'm almost there. I would be happy to send you back my files to take a look at what I ended up doing so that perhaps you can think about adding those features back into to future releases.