Dynamic Alphabetical Navigation

Question!

I'm using ColdFusion to return a result set from a SQL database and turn it into a list. I need some way to generate an alphabetical navigation bar for that list. I have ColdFusion and the jQuery library available. I'm looking to generate something like this:

A | B | C | ...      
- A
- A
- B
- B
- B
- C
- D

Where clicking on one of the letters drops you down the page to the first item for that letter. Not all 26 letters of the alphabet are necessarily in use.

By : alexp206


Answers

So, there were plenty of good suggestions, but none did exactly what I wanted. Fortunately I was able to use them to figure out what I really wanted to do. The only thing the following doesn't do is print the last few unused letters (if there are any). That's why I have that cfif statement checking for 'W' as that's the last letter I use, otherwise it should check for Z.

<cfquery datasource="#application.dsn#" name="qTitles">
SELECT title, url, substr(titles,1,1) as indexLetter
FROM list
ORDER BY indexLetter,title
</cfquery>

<cfset linkLetter = "#asc('A')#">
<cfoutput query="titles" group="indexletter">
    <cfif chr(linkLetter) eq #qTitles.indexletter#>
        <a href="###ucase(qTitles.indexletter)#">#ucase(qTitles.indexletter)#</a>
        <cfif asc('W') neq linkLetter>|</cfif>
        <cfset linkLetter = ++LinkLetter>
    <cfelse>
        <cfscript>
        while(chr(linkLetter) != qTitles.indexletter)
                {
                        WriteOutput(" " & chr(linkLetter) & " ");
                        IF(linkLetter != asc('W')){WriteOutput("|");};
                        ++LinkLetter;
                }
        </cfscript>

        <a href="###ucase(qTitles.indexletter)#">#ucase(qTitles.indexletter)#</a>
        <cfif asc('W') neq linkLetter>|</cfif>
        <cfset linkLetter = ++LinkLetter>
    </cfif>
</cfoutput>

<ul>
<cfset currentLetter = "">
<cfoutput query="qTitles" group="title">
<cfif currentLetter neq #qTitles.indexletter#>
    <li><a name="#ucase(qTitles.indexletter)#">#ucase(qTitles.indexletter)#</a></li>
</cfif>
<cfset currentLetter = #qTitles.indexletter#>
<li><a href="#url#">#title#</a></li>
</cfoutput>
</ul>
By : alexp206


You could use the query grouping function on your query of records. You will obviously have to change the query fields according to your data and the left() function may be different syntax depending on your database engine. The query below works on MSSQL.

<cfquery datasource="#application.dsn#" name="qMembers">
SELECT firstname,lastname, left(lastname,1) as indexLetter
FROM member
ORDER BY indexLetter,lastName
</cfquery>


<p id="indexLetter">
<cfoutput query="qMembers" group="indexLetter">
    <a href="###qMembers.indexLetter#">#UCase(qMembers.indexLetter)#</a>
</cfoutput>
</p>




<cfif qMembers.recordCount>

    <table>

    <cfoutput query="qMembers" group="indexLetter">
    	<tr>
    		<th colspan="99" style="background-color:##324E7C;">
    			<a name="#qMembers.indexLetter#" style="float:left;">#UCase(qMembers.indexLetter)#</a> 
    			<a href="##indexLetter" style="color:##fff;float:right;">index</a>
    		</th>
    	</tr>

    	<cfoutput>
    	<tr>
    		<td><strong>#qMembers.lastName#</strong> #qMembers.firstName#</td>
    	</tr>
    	</cfoutput>
    </cfoutput>

    </table>

<cfelse>
    <p>No Members were found</p>
</cfif>
By : mjb


To generate the navigation bar, you could do something like this:

<cfoutput>
<cfloop from="#asc('A')#" to="#asc('Z')#" index="i">
    <a href="###chr(i)#">#chr(i)#</a>
    <cfif asc('Z') neq i>|</cfif>
</cfloop>
</cfoutput>

(CFLOOP doesn't work on characters, so you have to convert to ascii codes and back.)


To display the items in your query you could do something like this.

<cfset currentLetter = "">
<cfoutput query="data">
<cfif currentLetter neq left(data.name, 1)>
    <h3><a name="#ucase(left(data.name, 1))#">#ucase(left(data.name, 1))#</a></h3>
</cfif>
<cfset currentLetter = left(data.name, 1)>
#name#<br>
</cfoutput>


This video can help you solving your question :)
By: admin