Got alphabet search bar?

Discussion in 'Programming' started by unitedlocalbands, Jul 27, 2007.

  1. #1
    I would like to let user refine the page by selecting what they want to order the query by and also by selecting to only see records that start with the letter they choose.

    You can see the page I'm working on at

    http://www.unitedlocalbands.com/user_band_logo.cfm?orderby=groupname

    I use coldfusion to pull my records from the database. Then I let the users change the orderby statement by selecting links that pass a url variable. This variable changes the orderby statment and reorganizes the page.

    Maybe theres a better way to do this aswell, but one thing at a time.

    If you can help that would be great!
     
    unitedlocalbands, Jul 27, 2007 IP
  2. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Here's a example of a simple loop that creates a link for each letter of the alphabet.

    
    <cfoutput>
    <cfloop from="#Asc('A')#" to="#Asc('Z')#" index="code">
    	<!--- you can also add other parameters to the url --->
    	<a href="http://www.unitedlocalbands.com/user_band_logo.cfm?firstLetter=#chr(code)#">#chr(code)#</a>
    </cfloop>
    </cfoutput>
    
    Code (markup):
     
    cfStarlight, Jul 27, 2007 IP
  3. unitedlocalbands

    unitedlocalbands Well-Known Member

    Messages:
    246
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    128
    #3
    Thank you, this is a start in the right direction. Now I just have to figure out how to write my SQL statement so it will only return records that start with the "firstletter" variable.

    Take Care!

    James
     
    unitedlocalbands, Jul 27, 2007 IP
  4. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Look up the sql LIKE operator

    WHERE ColumnName LIKE 'A%'

    Good luck!
     
    cfStarlight, Jul 27, 2007 IP
  5. unitedlocalbands

    unitedlocalbands Well-Known Member

    Messages:
    246
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    128
    #5
    The LIKE operator does work, but it returns every record with the firstletter variable no matter weather it starts with it or not.

    The = operator works too but only returns records exactly like the letter.

    Thanks though, I will keep trying different stuff. If I figure it out I'll post the SQL statement here with the loop you gave me
     
    unitedlocalbands, Jul 27, 2007 IP
  6. unitedlocalbands

    unitedlocalbands Well-Known Member

    Messages:
    246
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    128
    #6
    I found a forum where a developer wanted to capitalize the firstletter of a column when it was returnd. He used a string like "left(column_name, 1)"

    Here what my sql looks like:

    
    <cfquery name="" datasource="">
    SELECT BANDID, LOGINID, BIO, LOGOURL, GROUPNAME, URL, CITY, STATE, GENRE
    FROM USER_BAND
    
    WHERE Left(GROUPNAME, 1) LIKE '%#firstletter#%'
    
    ORDER BY <cfoutput>#ORDERBY#</cfoutput>
    </cfquery>
    
    and use the cfloop tag from above:
    
    
    <cfoutput>
    <cfloop from="#Asc('A')#" to="#Asc('Z')#" index="code">
    	<!--- you can also add other parameters to the url --->
    	<a href="http://www.unitedlocalbands.com/user_band_logo.cfm?firstLetter=#chr(code)#">#chr(code)#</a>
    </cfloop>
    </cfoutput>
    
    Code (markup):
    Pass any url variables you need.

    Thanks again,

    I really apreciate it!
     
    unitedlocalbands, Jul 27, 2007 IP
  7. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #7
    You can use either one. If you're using LIKE you must arrange the wildcards "%" properly. For example

    WHERE ColumnName LIKE 'A%' -- find values that start with "A"

    Just replace "A" with your variable
    WHERE ColumnName LIKE '#url.firstLetter#%'

    To use Equals:
    WHERE LEFT(ColumnName,1) = '#firstLetter#'

    (Don't use wildcards % when using equals)
     
    cfStarlight, Jul 27, 2007 IP
  8. unitedlocalbands

    unitedlocalbands Well-Known Member

    Messages:
    246
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    128
    #8
    Thank for your help cfStarlight. I got it working. Now all I need is some bands to sign up and add content...

    You can check it out at:

    http://www.unitedlocalbands.com/user_band_logo.cfm
     
    unitedlocalbands, Jul 27, 2007 IP