rondog Posted July 27, 2008 Share Posted July 27, 2008 I have a client who wants to have their stores searchable my zip code. I have a database with 20,000+ stores already that include zip code. I dont even know where to begin to start this? I was looking on google and just found ones you can buy and they are a bit expensive. Any ideas on where to start with this? Thanks. Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/ Share on other sites More sharing options...
Attila Posted July 27, 2008 Share Posted July 27, 2008 Here you go this should do it for you. It is a form that will search the database and display the results of the zip code. <? $top_form = "<form method=\"post\" action=\"{$_SERVER['PHP_SELF']}\"> <table border=\"2\" cellpadding=\"2\" cellspacing=\"2\" bordercolor=\"#333333\" bgcolor=\"#CCCCCC\"> <tr align=\"center\" valign=\"middle\"> <td colspan=\"1\">"; $bottom_form = "</td> </tr> <tr> <td>Enter the your zip code:/td> <td><input type=\"text\" value=\"{$_POST['zip']}\" name=\"zip\" /></td> </tr> <tr align=\"center\" valign=\"middle\"> <td colspan=\"2\"> <input type=\"hidden\" name=\"op\" value=\"ds\"> <input type=\"submit\" name=\"Submit\" value=\"Search\"></td> </tr> </table> </form>"; if ($_POST['op'] != "ds") { echo "$top_form"."$bottom_form"; } else { echo "$top_form"."$bottom_form"; $zipcode = $_POST['zip']; $sql = "SELECT * FROM the tabel WHERE colum name LIKE '%$zip%' ORDER BY colum name ASC"; //this is a like search you might have to do an exaxt search database_connect() $result = mysql_query($sql) or die(mysql_error()); while($row = mysql_fetch_array($result)) { //add formating in here to echo out the data } ?> Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-601031 Share on other sites More sharing options...
rondog Posted July 29, 2008 Author Share Posted July 29, 2008 well the query you provided wont really work since zip codes are all 5 digits (US anyway) and the % wildcard will return nothing unless its an exact match to a zip code in the DB. If I searched 92026 and nothing in the DB had a zip of 92026, then I would get nothing. If I was to search like '920%' then I would get some pretty decent results back. I am not sure how well that would work with other areas, but that might be a cheap solution...just knock off the users two trailing characters. Even increase/decrease the third numeral and just concatenate those results. Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-602263 Share on other sites More sharing options...
Zane Posted July 29, 2008 Share Posted July 29, 2008 I can tell where to start for this but I won't do the whole thing for you. 1. Lookup a zipcode database, there easy to find. actually, here's a great one http://code.google.com/p/codearachnid/downloads/detail?name=zipcodes.rar&can=2&q= So you get the zipcodes SQL and you've got an entire table full of every US zipcode. 2. Then google a zipcode distance calculator. Depending on how good you are a programmer, choose one that suits you. you can find an OOP based one, with many features, or look for a single function out there. all in all, you just need the longitude and latitude of two zipcodes and you can do whatever you want if you find or compose the right code. 3. Well I guess you done. just implement it. enjoy Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-602294 Share on other sites More sharing options...
Third_Degree Posted July 29, 2008 Share Posted July 29, 2008 I can tell where to start for this but I won't do the whole thing for you. 1. Lookup a zipcode database, there easy to find. actually, here's a great one http://code.google.com/p/codearachnid/downloads/detail?name=zipcodes.rar&can=2&q= So you get the zipcodes SQL and you've got an entire table full of every US zipcode. 2. Then google a zipcode distance calculator. Depending on how good you are a programmer, choose one that suits you. you can find an OOP based one, with many features, or look for a single function out there. all in all, you just need the longitude and latitude of two zipcodes and you can do whatever you want if you find or compose the right code. 3. Well I guess you done. just implement it. enjoy I don't think he was really asking for that, he already has the zip codes and mentioned nothing about distance. Anyway just get all the zip codes out of the db and use php to compare the strings. Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-602335 Share on other sites More sharing options...
Zane Posted July 29, 2008 Share Posted July 29, 2008 I don't think he was really asking for that, he already has the zip codes and mentioned nothing about distance. I believe he does, because he says he has 20,000 stores already with valid zipcodes. his only problem is that that is only approximately 46.5% of all the zipcodes is the US. How many ZIP Codes are there in the United States? There are approximately 43,000 ZIP Codes in the United States. This number can fluctuate by a few thousand ZIP Codes annually, depending on the number of changes made So if he has a table will all of them, and another table with all the stores, each store with its own foreign key (it's zipcode) relating to the zipcode table, he can perform radius searches to find the closest store to the customer instead of the customer searching for his/her OWN zipcode or various ones by trial and error......which is what the op is trying to do.....which I am trying to persuade him not to do. Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-602374 Share on other sites More sharing options...
Third_Degree Posted July 29, 2008 Share Posted July 29, 2008 I guess I misunderstood the OP then Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-602376 Share on other sites More sharing options...
rondog Posted July 29, 2008 Author Share Posted July 29, 2008 Yes I actually did need this table of all the zip codes. Thanks zanus. Its taking me forever to import the data! If I do the whole file at once, my connection to the DB times out after about an hour. Right now I am doing it state by state and about half way done so far. As soon as thats done, Ill begin my google search for this distance calculator. I have actually seen a few examples already. They were using things such as SIN, COS and TAN. I didnt even know those existed in mysql. On a side note, I am noticing that some of these zip codes have no state and its set to null. How could a zip code have no state ??? Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-602936 Share on other sites More sharing options...
Zane Posted July 29, 2008 Share Posted July 29, 2008 I don't know....maybe puerto rico has no states and guam. Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-602959 Share on other sites More sharing options...
rondog Posted July 29, 2008 Author Share Posted July 29, 2008 Ok finally done importing. Alright I am looking at this post on the mysql forums: http://forums.mysql.com/read.php?23,3868,73506#msg-73506 I am pretty sure that will do what I need it to do, but I am so lost. Is their any easier resource that you know of? This zip radius search function is pretty much what I need: http://www.web4future.com/products.php?p=zip This is A LOT more complicated than I thought :'( Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-603023 Share on other sites More sharing options...
Zane Posted July 30, 2008 Share Posted July 30, 2008 http://www.phpro.org/tutorials/Geo-Targetting-With-PHP-And-MySQL.html The next set of posts begins the UK version of this topic Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-603226 Share on other sites More sharing options...
haku Posted January 30, 2009 Share Posted January 30, 2009 One of the specs for a real-estate site I'm doing a quote on, is finding a property within X km of a postal code. The user enters in the postal code, and then the database is searched for properties near that. I have to admit, I'm somewhat at a loss on where to begin with this - how does this work? The site is UK based, which I'm sure will play into that. Has anyone ever seen a database of UK postal addresses and their locations, or something along that line? Any ideas? Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-750318 Share on other sites More sharing options...
phparray Posted January 30, 2009 Share Posted January 30, 2009 Add a hundred bucks to your quote and buy this. http://www.milldo.com/en16_uk_post_codes_database.htm Problem solved. Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-750322 Share on other sites More sharing options...
haku Posted January 30, 2009 Share Posted January 30, 2009 Thanks mate, that's exactly what I need. I was kind of stressing about that part of the system, but it just got a lot easier with that! Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-750329 Share on other sites More sharing options...
redarrow Posted January 30, 2009 Share Posted January 30, 2009 cheek this out then there all free. http://www.freemaptools.com/download-uk-postcode-lat-lng.htm Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-750345 Share on other sites More sharing options...
redarrow Posted January 30, 2009 Share Posted January 30, 2009 another free one there all using mate. http://www.acmultimedia.co.uk/blog/article.php?blogID=59 Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-750350 Share on other sites More sharing options...
Zane Posted January 30, 2009 Share Posted January 30, 2009 I've merged this with another older post with good links as well I'm putting this in the Code Repository Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-750499 Share on other sites More sharing options...
haku Posted January 31, 2009 Share Posted January 31, 2009 This link, looks to the best link posted, as it was a complete repository that comes with the scripts necessary to do what I want. I'll just tack $100 on to the clients bill. Considering that it would cost him more than that at my hourly rate for me to write the scripts to interact with the other free (incomplete) databases that were linked, this is obviously the way to go. Link to comment https://forums.phpfreaks.com/topic/116872-finding-a-location-within-x-mileskm-of-an-postal-code/#findComment-751200 Share on other sites More sharing options...
Recommended Posts