(5 votes, average: 4.80 out of 5)
Loading ...Just recently I needed a mysql database table that contained records for city, state, zip, latitude, and longitude coordinates. I needed a complete list of locations since one of my projects involved an ajax location auto populating script. While browsing on the web to find a free solution, I finally found a complete list of US zip codes from 2006 that contained everything that I wanted. I downloaded the files and realized it was written in Perl by R. Don Henning. I rewrote it in PHP for developers looking for a PHP solution.
Simply Download the files and place the directory wherever you would like. Make sure to edit the zip_insert.php file and change the database connection with yours.
Version 3 Updates:
Replaced file_get_contents with file so we return an array of each line. Replaced for loop with foreach loop. Removed the split function that is deprecated as of PHP 5.3.
Version 2 Updates:
There was an issue with leading zeros not being inserted into the database. I have added zerofill and changed INT(5). This is fixed now!
Loading ...
Jesse Price is a web applications developer currently located in Sarasota, Florida. He enjoys the daily challenges of creating usable web applications that consists of custom content management, customer relationship management, and dynamically driven database systems.
Thx Jesse, ok script.. but mysql add , update, delete how, pls help new script.
Thx.
Jesse
Thank you so much for sharing this. I tested it on a web server and it worked like a charm. Just a note. Becuase the zip code is set up as an integer it deletes all the prefix zeros of the zip code. So zipcode 00871 will only show up as 871 in the zipcode column. I am thinking about setting the zipcode field as char or varchar and change the table creation as needed, modify the primary key and load the data. I have not done that yet. If I get that working, I will share my change here.
Dal
Please use explode() and file() instead of split() and file_get_contents().
Sorry for not using explode and using split… Also file vs file_get_contents. I wrote this in my early days of php3 programming where the explode function did not exist. Also file returns an array and file_get_contents returns string so I figured that using file_get_contents then using a string manipulating function would suffice… I should update the code since split is now deprecated as of php 5.3…
Thanks great job! I’m currently working on Flex application and I needed this..
Hi Jesse,
Nice work. Keep it up.
If you are not addicted to PHP (Joking) then you can use this MySQL solution given below (Not joking) to upload data given in zipcode database.
—————————————————————————–
CREATE TABLE geoInfo(zipcode INT PRIMARY KEY,latitude VARCHAR(15),longitude VARCHAR(15),stateCode VARCHAR(10),city VARCHAR(20),county VARCHAR(20));
LOAD DATA LOCAL INFILE ‘/home/sanket/Desktop/zipcodes.txt’ INTO TABLE geoInfo FIELDS TERMINATED BY ‘||’ LINES TERMINATED BY ‘\n’;
—————————————————————————–
Thanks man really just need this for the database very helpful mate
Cheers
Have a look at http://worldcitiesdatabase.info
They offer a great database of world cities for less than five bucks. Unlike the free databases containing millions of entries, this one only has about 70 thousand entries, which makes it ideal for city selector scripts. Otherwise, it takes forever to populate the selectors.