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!
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):
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
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
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!
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)
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