group results by first letter? Phone directory application

[Benedict]

I am building a phone directory for my intranet. I have a list of alphabet
letters at the top of a page, those are links that will be anchors on the
same page to group query results.

My problem is this. Let say that as an example I have 5 results, Allan,
Albertson, Angel, Bradford and Bentley.

I want to group like this (results only shown for this)

A
Allan
Albertson
Angel

B
Bradford
Bentley


Seemed pretty easy when I started but I cannot get the grouping right by
just the first letter.

Any help?

Thanks!

Ben

[The Albino]

Ben,
This might not be the most efficient way, but it works.

<cfquery datasource="#request.dsn#" name="listnames">
Select l_name, f_name from tbl_user
Order By l_name
</cfquery>

<cfset myLetter = ''>
<cfoutput query="listnames">
<cfif left(l_name,1) EQ myLetter>
#l_name#, #f_name#
<br>
<cfelse>
<br>
#UCase(left(l_name,1))# <br>
<cfset myLetter = '#UCase(left(l_name,1))#'>
#l_name#, #f_name#
<br>
</cfif>
</cfoutput>

[Benedict]

Hey that worked quite well. Now you made me think of one more thing.

Let say I have results for A,B,C,D,F none for E.

I want to display A-Z in the links list but only have links on letters when
there is a result. I am showing my links list right now like this:

<CFLOOP INDEX="alpha"FROM ="65" TO="90">
<cfoutput><a href="###chr(alpha)#">#chr(alpha)#</a></cfoutput>
</CFLOOP>

This gives me A-Z with an anchor link to each letter. But if there is no E
it just stays there, but visually there is a link.

I would do an if statement but I am not sure where or if I need to loop
through the query?

Thanks,

Ben
"The Albino" <webforumsuser@macromedia.com> wrote in message
news:d6fs73$e3o$1@forums.macromedia.com...

> Ben,
> This might not be the most efficient way, but it works.
>
> <cfquery datasource="#request.dsn#" name="listnames">
> Select l_name, f_name from tbl_user
> Order By l_name
> </cfquery>
>
> <cfset myLetter = ''>
> <cfoutput query="listnames">
> <cfif left(l_name,1) EQ myLetter>
> #l_name#, #f_name#
> <br>
> <cfelse>
> <br>
> #UCase(left(l_name,1))# <br>
> <cfset myLetter = '#UCase(left(l_name,1))#'>
> #l_name#, #f_name#
> <br>
> </cfif>
> </cfoutput>
>

[The Albino]

Ben,

Again, might not be the most effcient.

Brendan

<cfoutput>
<cfset myList = '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'>
<cfloop list="#myList#" index="CurLtr">

<cfquery name="qry_ChkLink" datasource="brendan">
SELECT Distinct left(l_name,1) As Ltr
FROM tbl_user
WHERE left(l_name,1) = '#CurLtr#'
</cfquery>


<cfif qry_ChkLink.RecordCount EQ 1>
<a href="##CurLtr##">#CurLtr#</a>&nbsp;&nbsp;
<cfelse>
#CurLtr# &nbsp;&nbsp;
</cfif>
</cfloop>
</cfoutput>

[ultrafresh]

Hi Ben,

As an alternative to the cfif ... try using a group clause on your cfoutput...
like this... this was done with sqlserver so the left function in the query
might not be applicable but I think most DBs have a left function...

<cfquery name="g" datasource="#SQLDatabase#">

select hs_name,
left(hs_name,1) as lefty
from hs_code
order by hs_name

</cfquery>

<cfoutput query="g" group="lefty">

<strong>#lefty#</strong><br>
<cfoutput>
#hs_name#<br>
</cfoutput>

</cfoutput>