filtering for items in alphabetical order

cgacfox · cgacfox

It was suggested to me to add a feature to my media db and be able to filter
searches alphabetically. I have a couple of pages that pull up all DVDs and
books and there is paging capabilities coded in but if someone wants to look up
all movies or books that start with a certain letter I know that I will have to
do a query to filter this. Something like: Select * From DVDs WHERE movietitle
LIKE '%A%' Order by movietitle I am not sure if I need to do one of these
queries for each letter in the alphabet and have an area in my .cfm page that
lists the alphabet with links to the queries. Could someone verify that I am on
the right track or direct me on the right path to accomplish this? Thanks.

jonwrob · jonwrob

Your query will result in all records from DVDs with an uppercase "A" in the
title. I suspect that's not what you want. Try:

select *
from DVDs
where movietitle like 'A%'

Furthermore, I would create the links to pass a URL variable to the page and
use that, as:

<cfquery datasource="#dsn#">
select *
from DVDs
where movietitle like '#URL.letter#%'
</cfquery>

JR

cgacfox · cgacfox

I am sorry that I am so slow at figuring this out. I will post the code that I
have for the displayalldvds.cfm page. I am not sure where to put the code or
how to word the code so that it works. If someone can give me some generic code
help, then I can tweak it to fit my needs. I want a list of letters that the
user can click on and pull up all DVDs that start with that letter, (i.e. A,
B, C,etc). I am slowly learning what CF can do but I am far from proficient at
it. Here is my current code. I didn't include stuff for my link buttons, this
is just the code for querying the db for all movie titles. Help would be
greatly appreciated. Thanks very much.








<CFQuery Name="Displayalldvds" Datasource="media">
Select *
From DVDS
Order by movieTitle
</cfquery>

<CFQuery Name="genre" Datasource="media">
Select genre
From genre
Order by genre
</cfquery>

<CFQuery Name="Parentalrating" Datasource="media">
Select Parentalrating
From parentalRating
Order by parentalRating
</cfquery>

<CFSET RowsPerPage = 10>
<cfparam name="url.startRow" default="1" type="numeric">
<cfset TotalRows = displayalldvds.RecordCount>
<cfset EndRow = Min(url.StartRow + RowsPerPage - 1, TotalRows)>
<cfset StartRowNext = EndRow + 1>
<cfset StartRowBack = url.StartRow - RowsPerPage>

<html>

<head>
<title>Media Display</title>
</head>

<body bgcolor="#5A2E10" link="#FFFFFF" vlink="#FF0000" alink="#FF0000"
onLoad="MM_preloadImages

('../Images/Coributton_f2.gif','../Images/Coributton_f4.gif','../Images/Coributt
on_f3.gif',

'../Images/Gregbutton_f2.gif','../Images/Gregbutton_f4.gif','../Images/Gregbutto
n_f3.gif',

'../Images/Ashtonbutton_f2.gif','../Images/Ashtonbutton_f4.gif','../Images/Ashto
nbutton_f3.gif',

'../Images/Chrisbutton_f2.gif','../Images/Chrisbutton_f4.gif','../Images/Chrisbu
tton_f3.gif',

'../Images/petsbutton_f2.gif','../Images/petsbutton_f4.gif','../Images/petsbutto
n_f3.gif',

'../Images/Dee&Gloriabutton_f2.gif','../Images/Dee&Gloriabutton_f4.gif',
.../Images/Dee&Gloriabutton_f3.gif',

'../Images/Mikebutton_f2.gif','../Images/Mikebutton_f4.gif','../Images/Mikebutto
n_f3.gif',

'../Images/Mark&candybutton_f2.gif','../Images/Mark&candybutton_f4.gif',
'../Images/Mark&candybutton_f3.gif',

'../Images/hometheaterbutton_f2.gif','../Images/hometheaterbutton_f4.gif','../Im
ages/hometheaterbutton_f3.gif',

'/images/dvdmaintbutton_f2.gif','images/dvdmaintbutton_f4.gif','images/dvdmaintb
utton_f3.gif'

'../Images/thelibrarybutton_f2.gif','../Images/thelibrarybutton_f4.gif','../Imag
es/thelibrarybutton_f3.gif',

'images/bookmaintbutton_f2.gif','images/bookmaintbutton_f4.gif','images/bookmain
tbutton_f3.gif'

'../Images/guestbookbutton_f2.gif','../Images/guestbookbutton_f4.gif','../Images
/guestbookbutton_f3.gif',

'../Images/contactusbutton_f2.gif','../Images/contactusbutton_f4.gif','../Images
/contactusbutton_f3.gif')">

<table width="704" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td height="85" valign="top">
<div align="center">
<img src="Images/hometheaterheader.gif" alt="foxdenheader" width="703"
height="83" align="top">
</div>
</td>
</tr>
</table>
<table width="704" border="0" align="center" cellpadding="2" cellspacing="2"
summary="">
<tr>
<td width="691" height="361" valign="top" background="images/BGDVD4.gif"
bgcolor="#5A2E10">
<h1 align="center">
<font color="#000000" face="Geneva, Arial, Helvetica, sans-serif">All DVDs
Results</font>
</h1>
<form method="POST" action="Genre.cfm">
<font color="#000000" size="4" face="Geneva, Arial, Helvetica,
sans-serif"> Select Genre:<br>
 
<Select Name="Genre">
<Option value="">Filter by Genre</option>
<cfoutput query="Genre">
<option value="#genre#">#genre#</option>
</cfoutput>
</select>
<input type="submit" value="Go">
</font>
</FORM>
<form method="POST" action="Parentalrating.cfm">
<font color="#000000" size="4" face="Geneva, Arial, Helvetica,
sans-serif">
Select Parental Rating:<br>
 
<Select Name="Parentalrating">
<Option value="">Filter by Parental Rating</option>
<cfoutput query="Parentalrating">
<option value="#Parentalrating#">#parentalRating#</option>
</cfoutput>
</select>
<input type="submit" value="Go">
</font>
</FORM>
<form method="Post" action="movietitle.cfm">
<font color="#000000" size="4" face="Geneva, Arial, Helvetica,
sans-serif">
Enter name of the Movie (or word from movie) that you are interested in
seeing.<br>
<input type="text" name="movietitle" size="50">
<input type="submit" value="Go">
</font>
</form>
<form method="Post" action="mainactors.cfm">
<font color="#000000" size="4" face="Geneva, Arial, Helvetica,
sans-serif">
Enter name of the Actor that you are interested in seeing.<br>
<input type="text" name="mainactors" size="50">
<input type="submit" value="Go">
</font>
</form>
<form method="Post" action="studiohouse.cfm">
<font color="#000000" size="4" face="Geneva, Arial, Helvetica,
sans-serif">
Enter name of the Movie Studio House that you are interested in seeing.<br>
<input type="text" name="studiohouse" size="50">
<input type="submit" value="Go">
</font>
</form>
</td>
</tr>
</table>

<table width="704" border="0" align="center" cellpadding="0" cellspacing="1">
<tr>
<td height="116" colspan="2">

<cfoutput>
<p><span class="style4">
<font color="##FFFFFF" face="Geneva, Arial, Helvetica, sans-serif">
Displaying <b>#url.StartRow#</b> to <b>#EndRow#</b>
of <b>#TotalRows#</b> Records</font></span></p>
<p><font color="##FFFFFF" face="Geneva, Arial, Helvetica,
sans-serif">Sorted by page</font></p>
<p><cfinclude template="NextNIncludePageLinks.cfm">
<br>
</p>
</cfoutput>
</td>
<td>
<div align="left">
<font color="#FFFFFF" face="Geneva, Arial, Helvetica, sans-serif">
Choose a letter for all titles that start with that letter

</font>
</div>
</td>
<td align="right">

<cfinclude template="NextNIncludeBackNext1.cfm">
</td>
</tr>
<tr>
<td width="23%" class="style1">
<div align="left" class="style2">
<strong><font color="#FFFFFF" face="Geneva, Arial, Helvetica,
sans-serif">Movie Title </font></strong>
</div>
</td>
<td width="20%" class="style1">
<div align="center"><strong><font color="#FFFFFF" face="Geneva, Arial,
Helvetica, sans-serif">Genre </font></strong>
</div>
</td>
<td width="25%" class="style1">
<div align="center"><strong><font color="#FFFFFF" face="Geneva, Arial,
Helvetica, sans-serif">Parental Rating </font></strong>
</div>
</td>
<td width="32%" class="style1">
<div align="center"><strong><font color="#FFFFFF" face="Geneva, Arial,
Helvetica, sans-serif">Main Actors </font></strong>
</div>
</td>
</tr>


<cfloop query="Displayalldvds" StartRow="#url.StartRow#"
Endrow="#EndRow#">
<cfoutput>



<cfif currentrow mod 2 is 1>
<cfset bgcolor="996633">
<cfelse>
<cfset bgcolor="cc9966">
</cfif>

<tr bgcolor="#bgcolor#">

<td width="23%" class="style1"><div align="left" class="style3">
<div align="center"><font size="4">
<a href="dvdDetail.cfm?movietitle=#Displayalldvds.mov ietitle#">
#movieTitle# </a> </font>
</div>
</td>
<td width="20%" class="style1">
<div align="center"><font size="4">#genre# </font>
</div>
</td>
<td width="25%" class="style1">
<div align="center"><font size="4">#parentalRating# </font>
</div>
</td>
<td width="32%" class="style1">
<div align="center"><font size="4">#mainActors# </font>
</div>
</td>
</tr>
</cfoutput>
</cfloop>


<td colspan="2"><cfoutput>
<p><font color="##FFFFFF" face="Geneva, Arial, Helvetica,
sans-serif">Sorted by page</font></p>
<p>
<cfinclude template="NextNIncludePageLinks.cfm">
</p>
</cfoutput>
</td>
<td width="25%"> </td>
<td width="32%" align="right">

<cfinclude template="NextNIncludeBackNext1.cfm">
</td>
</tr>
</table>
</body>
</html>

zoeski80 · zoeski80

You need to have all your A-Z links on the screen
eg. <A HREF="page.cfm?letter=Z">Z</A>
hint: use ASC() function and the ascii codes for the letters to use a loop to
make the A - Z links rather than make the 26 links manually.

Then in your query to retreive the movies you need to check if a letter was
selected or not, if it was only get movies starting with that letter
eg.
<CFQuery Name="Displayalldvds" Datasource="media">
Select *
From DVDS
<CFIF IsDefined("URL.letter")>
WHERE movieTitle LIKE '#url.letter#%'
</CFIF>
Order by movieTitle
</cfquery>
this says that the first letter of the movie title must be the selected letter
ie #URL.letter# and then anything can be after that ie. %

You should highlight the letter that was selected or something so the user
knows what is being displayed on the screen.

Thats pretty much it.

HTH

Zoe

cgacfox · cgacfox

Thanks, Zoe. I tested the code and this does work using the letter A. However,
I am not sure what to do about the ASC() function. I had a few programming
classes in college but it has been a long time and I don't remember how to do
this.

zoeski80 · zoeski80

Got it the wrong way round - you need to use Chr() rather than Asc()

<CFLOOP FROM="65" TO="90" INDEX="thisAsc">
<A HREF="page.cfm?letter=#Chr(thisAsc)#">#Chr(thisAsc )#</A>    
</CFLOOP>

list of ascii codes: [url]http://www.ascii.cl/[/url]

HTH

philh · philh

What if you don't have any DVDs that start with Q? I like to get the actual
contents and provide a link only for existing titles, so <CFQUERY
name='getIndex' datasource='MyDSN'> SELECT DISTINCT left(movietitle,1) as alpha
from DVDS ORDER BY alpha </cfquery> Then, constructing the list: <CFLOOP
FROM='65' TO='90' INDEX='thisAsc'> <cfif
listfind(valuelist(getIndex.alpha),chr(thisAsc))> <A
HREF='page.cfm?letter=#Chr(thisAsc)#'>#Chr(thisAsc )#</A> <cfelse>
#chr(thisAsc)# </cfif> &nbsp; &nbsp; </CFLOOP> HTH,

filtering for items in alphabetical order

Thread Tools

Display

filtering for items in alphabetical order

Similar Questions and Discussions

Exporting form fields in non alphabetical order

Sorting by alphabetical order

ridiculous question about alphabetical order by MACOSX

Arrays and Alphabetical order

[PHP] Arrays and Alphabetical order

Re: filtering for items in alphabetical order

Re: filtering for items in alphabetical order

Re: filtering for items in alphabetical order

Re: filtering for items in alphabetical order

Re: filtering for items in alphabetical order

Re: filtering for items in alphabetical order

Posting Permissions