Prev: Launching app from image href
Next: MS-SQL to Mysql
From: EugenT on 20 Apr 2008 10:53
Hello,
Can somebody tell me if I'm right with this syntax :

I have these values in DB :

AL,AK,AZ,AR,CA,CO,CT,DE,DC,FL,GA,HI,ID,IL,IN,IA,KS,KY,LA,ME,MD,MA,MI,MN,MS,MO,MT
,NE,NV,NH,NJ,NM,NY,NC,ND,OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VT,VA,WA,WV,WI,WY

and i want to put out the full name of state.

<cfquery name="getArtists" datasource="cfartgallery">
SELECT state
FROM Artists
</cfquery>

<cfoutput query="getArtists">
<cfif Trim(state) is "AL">Alabama</cfif>
<cfif Trim(state) is "AK">Alaska</cfif>
<cfif Trim(state) is "AZ">Arizona</cfif>
<cfif Trim(state) is "AR">Arkansas</cfif>
<cfif Trim(state) is "CA">California</cfif>
<cfif Trim(state) is "CO">Colorado</cfif>
<cfif Trim(state) is "CT">Connecticut</cfif>
<cfif Trim(state) is "DE">Delaware</cfif>
<cfif Trim(state) is "DC">District of Columbia</cfif>
<cfif Trim(state) is "FL">Florida</cfif>
</cfoutput> etc. ...


<cfquery name="getArtists" datasource="cfartgallery">
SELECT state
FROM Artists
</cfquery>

<cfoutput query="getArtists">
<cfif Trim(state) is "AL">Alabama</cfif>
<cfif Trim(state) is "AK">Alaska</cfif>
<cfif Trim(state) is "AZ">Arizona</cfif>
<cfif Trim(state) is "AR">Arkansas</cfif>
<cfif Trim(state) is "CA">California</cfif>
<cfif Trim(state) is "CO">Colorado</cfif>
<cfif Trim(state) is "CT">Connecticut</cfif>
<cfif Trim(state) is "DE">Delaware</cfif>
<cfif Trim(state) is "DC">District of Columbia</cfif>
<cfif Trim(state) is "FL">Florida</cfif>
</cfoutput>

From: "JR "Bob" Dobbs" on 20 Apr 2008 12:03
Your code is synatically valid.

You may wish to consider an alternative to the repeated cfif tags you are
using.

1. Add a state lookup table to your database and add the state name to your
query such as:

SELECT A.state, S.State_Name
FROM Artists AS A INNER JOIN US_States AS S
ON ( A.State = S.Abbreviation )

2. Create a function in ColdFusion to do the lookup, see attached code.


<cffunction name="GetStateName" returntype="string" access="private"
output="no" hint="Gets US state name for specified abbreviation">
<cfargument name="abbreviation" type="string" required="yes" hint="Two
character abbreviation" />

<cfset var local=StructNew() />

<cfset local.retVal="" /> <!--- default to empty string, will return empty
string if no match found --->

<!--- do we have a two letter abbreviation --->
<cfif Len(arguments.abbreviation) eq 2 and REFindNoCase("[^A-Z]",
arguments.abbreviation, 1, false) eq 0>



<!--- struct containing US state values --->
<cfset local.stateInfo=StructNew() />

<cfset local.stateInfo["AL"]="ALABAMA" />
<cfset local.stateInfo["AK"]="ALASKA" />
<cfset local.stateInfo["AZ"]="ARIZONA" />
<cfset local.stateInfo["AR"]="ARKANSAS" />
<cfset local.stateInfo["CA"]="CALIFORNIA" />
<cfset local.stateInfo["CO"]="COLORADO" />
<cfset local.stateInfo["CT"]="CONNECTICUT" />
<cfset local.stateInfo["DE"]="DELAWARE" />
<cfset local.stateInfo["FL"]="FLORIDA" />
<cfset local.stateInfo["GA"]="GEORGIA" />
<cfset local.stateInfo["HI"]="HAWAII" />
<cfset local.stateInfo["ID"]="IDAHO" />
<cfset local.stateInfo["IL"]="ILLINOIS" />
<cfset local.stateInfo["IN"]="INDIANA" />
<cfset local.stateInfo["IA"]="IOWA" />
<cfset local.stateInfo["KS"]="KANSAS" />
<cfset local.stateInfo["KY"]="KENTUCKY" />
<cfset local.stateInfo["LA"]="LOUISIANA" />
<cfset local.stateInfo["ME"]="MAINE" />
<cfset local.stateInfo["MD"]="MARYLAND" />
<cfset local.stateInfo["MA"]="MASSACHUSETTS" />
<cfset local.stateInfo["MI"]="MICHIGAN" />
<cfset local.stateInfo["MN"]="MINNESOTA" />
<cfset local.stateInfo["MS"]="MISSISSIPPI" />
<cfset local.stateInfo["MO"]="MISSOURI" />
<cfset local.stateInfo["MT"]="MONTANA" />
<cfset local.stateInfo["NE"]="NEBRASKA" />
<cfset local.stateInfo["NV"]="NEVADA" />
<cfset local.stateInfo["NH"]="NEW HAMPSHIRE" />
<cfset local.stateInfo["NJ"]="NEW JERSEY" />
<cfset local.stateInfo["NM"]="NEW MEXICO" />
<cfset local.stateInfo["NY"]="NEW YORK" />
<cfset local.stateInfo["NC"]="NORTH CAROLINA" />
<cfset local.stateInfo["ND"]="NORTH DAKOTA" />
<cfset local.stateInfo["OH"]="OHIO" />
<cfset local.stateInfo["OK"]="OKLAHOMA" />
<cfset local.stateInfo["OR"]="OREGON" />
<cfset local.stateInfo["PA"]="PENNSYVANIA" />
<cfset local.stateInfo["RI"]="RHODE ISLAND" />
<cfset local.stateInfo["SC"]="SOUTH CAROLINA" />
<cfset local.stateInfo["SD"]="SOUTH DAKOTA" />
<cfset local.stateInfo["TN"]="TENNESSEE" />
<cfset local.stateInfo["TX"]="TEXAS" />
<cfset local.stateInfo["UT"]="UTAH" />
<cfset local.stateInfo["VT"]="VERMONT" />
<cfset local.stateInfo["VA"]="VIRGINIA" />
<cfset local.stateInfo["WA"]="WASHINGTON" />
<cfset local.stateInfo["DC"]="WASHINGTON DC" />
<cfset local.stateInfo["WV"]="WEST VIRGINIA" />
<cfset local.stateInfo["WI"]="WISCONSIN" />
<cfset local.stateInfo["WY"]="WYOMING" />

<cfif StructKeyExists(local.stateInfo, arguments.abbreviation)>
<cfset local.retVal=local.stateInfo["#arguments.abbreviation#"] />
</cfif>

</cfif>

<cfreturn local.retVal />

</cffunction>

 | 
Pages: 1
Prev: Launching app from image href
Next: MS-SQL to Mysql