How would you handle state names?

PupChow

I would like to consult you guys first before I start doing this, to see if there are more efficient ways.

I am working on a database with lion dance troupes, part of the site will list all the states with troupes in the state.

Maryland (3)
Virginia (1)
California (10)
.
.
.

Right now the States are entered via a form as abbreviations like MD, VA, CA... etc. I need to somehow convert those into full state name.

Create an overview listing that shows all the states with tr

I am thinking about creating a new table in my database that has records of the states and their abbreviation. Am I going in the right direction? Or should I just do a bunch of Case statement for the state listing in PHP and forget the database in MySQL?

bpat1434

You are headed in the right direction using a foreign table. The case-statements might slow you down, but the mySQL won't. If you set up your tables correctly, you can join them on the states abbreviations, and select the state-name, rather than abbreviation so when it's displayed, it's displayed as state-name. But it wouldn't be that hard:

SELECT count(a.troups), b.statename 
FROM troups_listing a
INNER JOIN state_listings b
ON a.state_abbreviation=b.state_abbreviation
GROUP BY a.state_abbreviation
ORDER BY b.statename ASC

And that should give you what you want, or close, or whatever....

But yes, using a separate table (not database) for the state name listings is perfectly fine, and actually a preferred method. Mainly for extensibility for later when you decide to expand, you can add states/countries, and not have to worry about editing all your rows in your database. It's now just a minor change to a secondary table.

PupChow

Gotcha, glad I am finally thinking along the right path for once! Now, the fun job to create all those records for 50 states and 80 some countries... let me consult Google and see if any body on the web has done the hard part for me already! =)

Thanks!!!