So yesterday I needed to display the alphabet letters only if a database field started with the letter. Uh? Well let's say you have a table and you wanted last names, and then lets say there are only 2 name John Ramon, and Joshua Cry (sorry Josh) I wanted to output the alphabet only matching our last names. So...

| C | J |

I use Joshua cause I asked him for help and funny story he had to do the same thing yesterday, funny how things work out. So on to the code we came up with.

It's fairly straight forward see the code below.

Code

<!--- Get all names from query --->
<cfquery name="getName" datasource="#application.datasource#">
select firstName, lastName, Left(lastName, 1) as firstLetter
from names
order by lastName asc
</cfquery>

<!--- Set the letters in a list --->
<cfset alpha = ValueList(showAll.firstLetter)>

<!--- Use startCount to assign first letter found as default --->
<cfset startCount = 0>
<!--- Loop through the list of letters --->
<cfloop index="strLetter" list="A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z" delimiters=",">
<!--- Check to see the first letter is in the list --->
<cfif listFind(alpha,strLetter)>
<!--- check to see if user is requesting a letter, if not assign first letter as default --->
<cfif not isDefined("url.s")>
<!--- If user is not asked for a letter check startCount, if 0 assign default letter --->
<cfif startCount eq 0>
<cfset s = '#strLetter#'>
<!--- set startCount to 1 so no other letters are assigned --->
<cfset startCount = 1>
</cfif>
</cfif>
<!--- Output letter that are in the db --->
<cfoutput><a href="?s=#strLetter#">#strLetter#</a></cfoutput>
</cfif>
</cfloop>

<!--- Query of queries to get default or user request letter --->
<cfquery name="getNameLetter" dbtype="query">
select *
from getName
where lastName LIKE <cfqueryparam value="#s#%" cfsqltype="CF_SQL_VARCHAR" maxlength="1">
order by lastName desc
</cfquery>

<!--- output names that match --->
<cfoutput query="getNameLetter">
#getNameLetter.firstName# #getNameLetter.lastName# <br />
</cfoutput>