Tom Ray [Lists] wrote:

Hey all-

I've run into a small bump in some code I'm writing for a membership database. The first thing that needs to be done is an index of all states in the US with members. The fun part is that the state information is stored as the abbreviation in the database (ie MI, WI) and the HTML page needs to display the full name of the State. I want to display the information in a four column table that kicks out as many rows as needed. Now in order to get the full name of the state, I'm running the state result through a switch statement. Actaully, it's working rather well expect for this little issue:

Row 8: South Dakota Tennessee Texas Virgina
Row 9: West Virgina Wisconsin Texas Virgina

I need to kill that extra Texas and Virgina.....here's how I'm doing this:

<table border=1>
<?
$query=mysql_query("SELECT DISTINCT state FROM members WHERE country='US' ORDER BY state ASC"); while($q=mysql_fetch_array($query)) {
<snip>

Hi Tom

Seeing as you are already using mysql, why not create a lookup table with the following fields:

state_abbreviation, state_full_name

And your query to :

SELECT DISTINCT b.state_full_name FROM members a, state_lookup b WHERE a.country='US' AND b,state_abbreviation = a.state ORDER BY b.state_full_name ASC

This shifts the data processing work to MySQL (which is what it is designed for) - you can also use the state lookup table for user input validation if needed.

--
Rory McKinley
Nebula Solutions
+27 21 555 3227 - office
+27 21 551 0676 - fax
+27 82 857 2391 - mobile
www.nebula.co.za
====================

This e-mail is intended only for the person to whom it is addressed and
may contain confidential information which may be legally privileged.
Nebula Solutions accepts no liability for any loss, expense or damage
arising from this e-mail and/or any attachments.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Reply via email to