City and State MySQL Database For You

Hey,

There are many uses for a database of all US cities. I have here a database with every city, what state it's in, and that state's abbreviation. There are some 6,000 rows in it. You will need MySQL and PHP. Read below for more.

STEP 1
--------------------
Create a database called "city_state" in phpMyAdmin or whatever. Then, also in phpMyAdmin, run this MySQL query.

-- phpMyAdmin SQL Dump
-- version 2.9.2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Apr 11, 2007 at 10:14 PM
-- Server version: 5.0.27
-- PHP Version: 5.2.1
-- 
-- Database: `city_state`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `cities`
-- 

CREATE TABLE `cities` (
  `id` int(11) NOT NULL auto_increment,
  `state_id` int(11) NOT NULL,
  `city` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5998 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `states`
-- 

CREATE TABLE `states` (
  `id` int(11) NOT NULL auto_increment,
  `state` varchar(255) NOT NULL,
  `abb` varchar(2) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=52 ;

Code (markup):

STEP 2
--------------------
Download attached zip file. Upload all the files to the server with your MySQL database on it (I'm using my localhost).

STEP 4
--------------------
Open up cities/pop.php and at the top, set $dbhost, $dbuser, and $dbpass to be whatever your values are. Most likely $dbhost will be localhost.

STEP 5
--------------------
Run pop.php. Most likely this is http://www.yourdomain.com/cities/pop.php. Be patient, it will take a while as it needs to add around 6000 rows to the database.

Caution: this WILL slow down your server while it populates the database.




So there you have it. You now have a MySQL database with every city name in the US. There are a lot of uses for this, and it's all up to your imagination. The beauty is that you can manipulate the data in almost any way imaginable using queries and some clever PHP.

If you have any questions, just ask.

 

This is great man. It's just what I need. Now I can do so much more with marketing.

I can actually target my users better.

Thanks! Rep!

 

Absolutely awsome! Thanks!

 

Thanks for this - appreciate you writing it up and sharing...Green for you!

 

UPDATE!

I forgot to add state abbreviations. If you've already downloaded it, just go into phpMyAdmin and run the following on the city_state database:

ALTER TABLE `states` CHANGE `abb` `abb` VARCHAR( 2 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL 

Code (markup):

Then download the attached file, edit it to put in host/user/pass, and run it.

Otherwise, I updated the first post and it will work fine.

 

I get an invalid attachment message when I try to download cities.zip

 

You probably downloaded it right when I was updating the first post. Try again.

 

Looks like that was the problem. It download fine now. Thanks!

 

Thanks, even better now!

 

fantastic post.
Exactly what i was looking for.
Reps.

 

If you want a tool I created for this, get it here (I'm not posting it at DP, because WickedFire users are a lot more into this stuff):

http://www.wickedfire.com/traffic-s...us-city-state-database-keyword-generator.html

 

This is a great resource. I would like to point out I had trouble running the script on my server because the filenames for the states are lowercase (except "District of Columbia.txt") but the script looks for them with first letter capitalized.

You can fix it by changing the filenames (e.g. "alabama.txt" to "Alabama.txt") or changing the line in pop.php:
$fn = 'list/' . $state . '.txt';
to:
$fn = 'list/' . strtolower($state) . '.txt';

Not that if you do this you do need to change "District of Columbia.txt" to "district of columbia.txt"

 

Yeah thats true, I figured everyone figured that out so I didn't reupload lol...

But here is a more advanced version:

http://www.wickedfire.com/traffic-s...url-city-state-keyword-generator-adwords.html