Skip to main content
A
Anonymous
September 4, 2006
Question

A to Z

  • September 4, 2006
  • 4 replies
  • 599 views
Hi all,

Could anyone point me in the direction of a tutorial that will take all names from a database and put them on pages listed a to z

Thx
Lee
    This topic has been closed for replies.

    4 replies

    L
    LeftCorner
    Participating Frequently
    September 5, 2006
    Try changing the query to:

    SELECT NAME
    FROM dbo.PPS_PRINCIPALS
    WHERE LEFT(NAME, 1) = '#SortLetter#'

    and see what happens.
    A
    Anonymous
    September 5, 2006
    p.s.

    SELECT name
    FROM dbo.PPS_PRINCIPALS
    WHERE LEFT(name, 1) = 'A'

    works in query analyzer so its not the database
    L
    LeftCorner
    Participating Frequently
    September 4, 2006
    You don't need to make 26 pages, this is what computers are for!

    Most databases support the the Left() function. You can use this function to filter out your query reslts to where the name starts with the letter you wish to filter by. Here is an example:

    <!--- all the letters in the english language we will sort by --->
    <cfset Variables.Alphabet = "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">


    <!--- the default letter to sort by --->
    <cfparam name="SortLetter" default="A">


    <!--- validate the sort choice is valid since the user can manually change it --->
    <cfif NOT ListFindNoCase(Variables.Alphabet, SortLetter) OR Len(SortLetter) GT 1>
    <p>The letter you wish to sort by (<cfoutput>#SortLetter#</cfoutput>) is invalid.</p>
    <cfabort>
    </cfif>


    <!--- the query to get only those names that start with the letter we wish to sort by --->
    <cfquery name="QueryName" datasource="DataSource">
    SELECT Name
    FROM Table

    WHERE LEFT(Name, 1) = '<cfqueryparam value="#SortLetter#" cfsqltype="CF_SQL_VARCHAR" maxlength="1">'
    ORDER BY Name ASC
    </cfquery>

    <!--- the query results --->
    <cfif QueryName.RecordCount>

    <cfoutput quert="QueryName">
    #Name#<br>
    </cfoutput>

    <cfelse>

    <p>No results found</p>

    </cfif>


    <!--- HTML links to sort by any letter --->
    <p>Sort by name starting with:

    <cfloop index="i" list="#Variables.Alphabet#">
    <cfoutput>
    <a href="#CGI.ScriptName#?SortLetter=#i#">#i#</a>
    </cfoutput>
    </cfloop>

    </p>
    A
    Anonymous
    September 5, 2006
    I have taken what you have posted and changed some stuff, just querynames, etc
    but i get --

    Error Executing Database Query.
    [Macromedia][SQLServer JDBC Driver]Invalid parameter binding(s).

    I have looked thru the coding and cant see anything i have missed! Any suggestions?


    <!--- all the letters in the english language we will sort by --->
    <cfset Variables.Alphabet = "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">


    <!--- the default letter to sort by --->
    <cfparam name="SortLetter" default="A">


    <!--- validate the sort choice is valid since the user can manually change it --->
    <cfif NOT ListFindNoCase(Variables.Alphabet, SortLetter) OR Len(SortLetter) GT 1>
    <p>The letter you wish to sort by (<cfoutput>#SortLetter#</cfoutput>) is invalid.</p>
    <cfabort>
    </cfif>


    <!--- the query to get only those names that start with the letter we wish to sort by --->
    <cfquery name="a2z" datasource="breeze" username="*****" password="*****">
    SELECT NAME
    FROM dbo.PPS_PRINCIPALS
    WHERE LEFT(NAME, 1) = '<cfqueryparam value="#SortLetter#" cfsqltype="CF_SQL_VARCHAR" maxlength="1">'
    ORDER BY NAME ASC
    </cfquery>

    <!--- the query results --->
    <cfif a2z.RecordCount>

    <cfoutput query="a2z">
    #NAME#<br>
    </cfoutput>

    <cfelse>

    <p>No results found</p>

    </cfif>


    <!--- HTML links to sort by any letter --->
    <p>Sort by name starting with:

    <cfloop index="i" list="#Variables.Alphabet#">
    <cfoutput>
    <a href="#CGI.ScriptName#?SortLetter=#i#">#i#</a>
    </cfoutput>
    </cfloop>

    </p>

    L
    LeftCorner
    Participating Frequently
    September 4, 2006
    This is pretty straightforward Lee.

    You have two CF tags. <cfquery> to get your data and <cfoutput> to list the results on your page. You can define how the data is ordered in your query.

    This code will order the query by a column called "Name" in ascending order.

    <cfquery name="QueryName" datasource="DataSource">
    SELECT Name
    FROM Table

    ORDER BY Name ASC
    </cfquery>

    <cfoutput query="QueryName">
    #Name#<br />
    </cfoutput>


    This code will order the query n descending order.

    <cfquery name="QueryName" datasource="DataSource">
    SELECT Name
    FROM Table

    ORDER BY Name DESC
    </cfquery>

    <cfoutput query="QueryName">
    #Name#<br />
    </cfoutput>
    A
    Anonymous
    September 4, 2006
    sorry i maybe didnt explaine myself well enough!

    I want them on individual pages, so that they click on A and brings all the people with name begining with A, etc

    Can this be done or would i need to make 26 pages manually?
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices