Hello, Dreamweaver 8 Coldfusion MX7 I have a dynamic .cfm page that displays a bunch of recipes on my website. I have them sorted in categories as such: Category 1 Recipe.................Recipe.................Recipe Recipe.................Recipe.................Recipe Recipe.................Recipe.................Recipe Recipe.................Recipe.................Recipe Category 2 Recipe.................Recipe.................Recipe Recipe.................Recipe.................Recipe Recipe.................Recipe.................Recipe Recipe.................Recipe.................Recipe Category 3 Recipe.................Recipe.................Recipe Recipe.................Recipe.................Recipe Recipe.................Recipe.................Recipe Recipe.................Recipe.................Recipe Category 4 Recipe.................Recipe.................Recipe Recipe.................Recipe.................Recipe Recipe.................Recipe.................Recipe Recipe.................Recipe.................Recipe As you can see, it requires the viewer to scroll down to their desired category, etc. I would like to write a code for a simple filter that may be capable of the following: 1. Filter by category - only display the category selected 2. Filter by keyword search - a box where a user can type in a word, and only those with that work in the recipe name is returned One key issue is that the page is dynamic. It is not a static .html page. So, I assume it must, or should, be done through CF code. Thanks!
There's a whole bunch of free stuff that could fulfill this from dynamicdrive.com - might want to check it out
Check out the filters on my site. http://www.unitedlocalbands.com/user_personal_logo.cfm Is this what you are thinking about? If so then I think I can help. Set up a form to pass the catagory your user would like to view to the url as a variable. Set up a cfparam and name in catagory. Set its default to "" or to whatever dynamic data you would like to show on the first page load. In this example I use a cfquery to select all the DISTINCT enteries to populate the form. Your query to select the catagories may look like this: <cfquery datasource="your dsn" name="recipie"> SELECT DISTINCT CATAGORY FROM YOUR_TABLE_NAME </cfquery> [code] <cfparm name="catagory" default=""> Code (markup): <form action="your_page"> Catagory: <select name="catagory"> <option><cfoutput>#catagory#</cfoutput></option> <cfoutput query="recipie"> <option>#UCase(catagory)#</option> </cfoutput> </select> <input type="submit" value="Submit" /> </form> Code (markup): At last you will need to change you SQL statment to refelct the users selection from the form select list. <cfquery datasource="" name=""> SELECT RECIPIE, CATAGORY FROM YOUR_TABLE_NAME WHERE CATAGORY LIKE '#CATAGORY#' </cfquery> Code (markup): When the user of your site selects a catagory from the select list and hits the submit button the form will pass the selection to the url as a variable which will populate your SQL Statment to change the where clause. Hope this helps. James
That's a good approach. Just a few small suggestions. Scope your variables (ie use form.variableName or url.variableName). Use cfqueryparam. Also don't use LIKE unless you're doing pattern matching. The equals operator (=) is faster than than LIKE. Based on your other posts I would assume your list "value" would be CategoryID <form [B][COLOR="Red"]method="post"[/COLOR][/B] ...> <select name="CategoryID"> <option value="0"> -- All Categories ---</option> <cfoutput query="getCategories"> <option value="#CategoryID#>#Category_Name#</option> </cfoutput> </select> <!--- text box for entering a keyword ---> <input type="text" name="keyword"> </form> Code (markup): If a category was selected, match on the category id. If a keyword was entered, use the LIKE operator to find recipes containing that keyword. If you want to allow multiple keywords, you'll need to use a <cfloop>. <!--- use the URL scope instead if your form uses method="get" ---> <cfparam name="form.categoryId" default="0"> <cfparam name="form.keyword" default=""> <cfquery ....> SELECT ....ColumnNames... FROM YourTable <!--- using WHERE 1 = 1 so the query will return all records if no category or keyword was selected ---> WHERE 1 = 1 <cfif form.CategoryID GT 0> AND CategoryID = <cfqueryparam value="#form.categoryID#" cfsqltype="cf_sql_integer"> </cfif> <cfif len(trim(form.keyword)) GT 0> AND Recipe_Name LIKE <cfqueryparam value="%#form.keyword#%" cfsqltype="cf_sql_varchar"> </cfif> </cfquery> Code (markup):
I successfully added the filters. But then I tried going a bit further to add two more filters. If a recipe includes a specific ingredient or not. I tried implementing this added filter to my working code (Red is the new code I added): <cfquery name="types" datasource="MYDATASOURCE"> SELECT TYPE_ID, TYPE FROM TYPE ORDER BY TYPE_ID </cfquery> <cfquery name="categories" datasource="MYDATASOURCE"> SELECT CATEGORY_ID, CATEGORY FROM TBLCATEGORY ORDER BY CATEGORY_ID </cfquery> <cfquery name="INGREDIENTS" datasource="MYDATASOURCE"> SELECT * FROM TBLINGREDIENTS ORDER BY INGREDIENT </cfquery> <cfquery name="TYPE" datasource="MYDATASOURCE"> SELECT dt.TYPE, r.RECIPE_ID, r.RECIPE_NAME, r.LINK, tt.CATEGORY_ID, tt.CATEGORY, r.TYPE_ID, i[COLOR="red"][B].INGREDIENT_ID, i.RECIPE_ID[/B][/COLOR] FROM [COLOR="red"][B]([/B][/COLOR](TBLRECIPES AS r INNER JOIN TYPE dt ON r.TYPE_ID = dt.TYPE_ID) INNER JOIN TBLCATEGORY AS tt ON r.CATEGORY_ID = tt.CATEGORY_ID[COLOR="red"][B])[/B][/COLOR] [COLOR="red"][B]INNER JOIN TBLRECIPEINGREDIENTS AS i ON r.RECIPE_ID = i.RECIPE_ID[/B][/COLOR] WHERE 1 = 1 <cfif val(form.CATEGORY_ID) GT 0> AND r.CATEGORY_ID = <cfqueryparam value="#form.CATEGORY_ID#" cfsqltype="cf_sql_integer"> </cfif> <cfif val(form.TYPE_ID) GT 0> AND r.TYPE_ID = <cfqueryparam value="#form.TYPE_ID#" cfsqltype="cf_sql_integer"> </cfif> [COLOR="Red"][B] <cfif val(form.INGREDIENT1) GT 0> AND i.INGREDIENT_ID = <cfqueryparam value="#form.INGREDIENT1#" cfsqltype="cf_sql_integer"> </cfif> <cfif val(form.INGREDIENT2) GT 0> AND i.INGREDIENT_ID = <cfqueryparam value="#form.INGREDIENT2#" cfsqltype="cf_sql_integer"> </cfif>[/B][/COLOR] <cfif trim(form.searchfield) neq ""> AND r.RECIPE_NAME LIKE '%#form.searchfield#%' </cfif> ORDER BY r.TYPE_ID ASC, r.RECIPE_NAME ASC, i.RECIPE_ID ASC </cfquery> Code (markup): Note: The code in black alone works fine. The new code with the red added is OK as well, however, When all recipes are displayed, it displays duplicates. I figure it must be with my "group" and how I order my query results. But I realized you cannot do 2 groups in query results (i.e. group="type", group="recipe_name")
You can perform multiple groups in a cfoutput as long as the "group" column order matches the query ORDER BY clause. Is your query really returning duplicates or does it just appear that way? Dump the results of the query. If it actually contains duplicate records the problem is your query. If it does not contain duplicates, the problem is likely your output code. Also, I noticed the RECIPE_ID column is included in the SELECT list twice. This may cause problems. Remove one of the columns so RECIPE_ID is only included once.
I got it. Before I was trying this... <cfoutput query="type" group="type" group="recipe_name"> Code (markup): I got errors. I checked my forta book, and realized I needed to separate each group into an independent cfoutput. It works now. THANKS!
One final question. My form has a checkbox. Essentially, it's a checkbox to enable more search features. I used the same javascript you advised before to hide it unless the checkbox is clicked. When I submit my form, (it submits to the same page), I want the div to still be appearing. Instead, i can get the checkbox to stay checked, but the div doesn't show because it is told to show only when the checkbox is "toggled", and not strictly "checked".
Are you passing that checkbox in the form variables? Is so you could just do something like: <div style="display:<cfif StructKeyExists(form,"whatevercheckbox")>block<cfelse>none</cfif>;"> Code (markup): ...or something along those lines. Since it won't exist if it isn't checked, condition should work. Alternatively you could check the value of the checkbox once the page is rendered and then act on it. I use JQuery a lot for that kind of stuff. If you do you could create something like this: // warning, double check my syntax! $(document).ready(function() { $("#whatevercheckboxid").attr("checked") ? $("#YourDivId").show(); } Code (markup):
I must have dreamed about it last night, because when I woke up I had the answer. I essentially did the same as you suggested first: <div id="moreoptions" style="padding-left:8px; <cfif form.tttt eq "">display:none;<cfelse>display:block;</cfif>" > Code (markup): This worked perfectly. Thanks for the help.
Hmm... something strange is occuring with my filter. It is indeed working, however, it's not displaying all the results it should be. For instance, I searched for an ingredient that multiple recipes contain, but only 1 recipes shows up. I've looked through my code, and cannot seem to find what could be causing the error. At first thought, I would think it's my results query. I included a rough outline of my page code below, and tried labeling each section pretty well. I believe the problem could be with the portion in red: <!----INITIALIZE VARIABLES----------------------> <cfparam name="form.CATEGORY_ID" default=""> <cfparam name="form.TYPE_ID" default=""> <cfparam name="form.INGREDIENT1" default=""> <cfparam name="form.INGREDIENT2" default=""> <cfparam name="form.searchfield" default=""> <cfparam name="form.tttt" default=""> <!----JAVASCRIPT FOR HIDDEN DIV TOGGLE FEATURE----------------------> <script type="text/javascript"> function toggleBrands(id, show) { document.getElementById(id).style.display=(show)?'':'none'; } </script> <!----INITIALIZE FORM FOR SEARCH / FILTER FEATURES ----------------------> Filter by: <form method="post" action="recipe_book.cfm"> <!----QUERIES TO POPULATE LIST BOXES----------------------> <cfquery name="types" datasource="mydatabase"> SELECT TYPE_ID, TYPE FROM TYPE ORDER BY TYPE_ID </cfquery> <cfquery name="categories" datasource="mydatabase"> SELECT CATEGORY_ID, CATEGORY FROM TBLCATEGORY ORDER BY CATEGORY_ID </cfquery> <cfquery name="INGREDIENTS" datasource="mydatabase"> SELECT * FROM TBLINGREDIENTS ORDER BY INGREDIENT </cfquery> <!----QUERY TO SEARCH FOR RECIPES BASED ON SEARCH CRITERIA----------------------> [COLOR="Red"][B]<cfquery name="type" datasource="mydatabase"> SELECT dt.TYPE, r.RECIPE_ID, r.RECIPE_NAME, r.LINK, tt.CATEGORY_ID, tt.CATEGORY, r.TYPE_ID, i.INGREDIENT_ID FROM ((TBLRECIPES AS r INNER JOIN TYPE dt ON r.TYPE_ID = dt.TYPE_ID) INNER JOIN TBLCATEGORY AS tt ON r.CATEGORY_ID = tt.CATEGORY_ID) INNER JOIN TBLRECIPEINGREDIENTS AS i ON r.RECIPE_ID = i.RECIPE_ID WHERE 1 = 1 <cfif val(form.CATEGORY_ID) GT 0> AND r.CATEGORY_ID = <cfqueryparam value="#form.CATEGORY_ID#" cfsqltype="cf_sql_integer"> </cfif> <cfif val(form.TYPE_ID) GT 0> AND r.TYPE_ID = <cfqueryparam value="#form.TYPE_ID#" cfsqltype="cf_sql_integer"> </cfif> <cfif val(form.INGREDIENT1) GT 0> AND i.INGREDIENT_ID = <cfqueryparam value="#form.INGREDIENT1#" cfsqltype="cf_sql_integer"> </cfif> <cfif val(form.INGREDIENT2) GT 0> AND i.INGREDIENT_ID = <cfqueryparam value="#form.INGREDIENT2#" cfsqltype="cf_sql_integer"> </cfif> <cfif trim(form.searchfield) neq ""> AND r.RECIPE_NAME LIKE '%#form.searchfield#%' </cfif> ORDER BY r.TYPE_ID ASC, r.RECIPE_NAME ASC </cfquery>[/B][/COLOR] <!----“TYPE†LIST BOX – SEARCH PARAMETER----------------------> <p align="left">Type: <select name="TYPE_ID"> <option value="" <cfif trim(form.TYPE_ID) eq "">selected </cfif>> All </option> <cfoutput query="types"> <option value="#TYPE_ID#" <cfif form.TYPE_ID eq types.TYPE_ID>selected</cfif>>#TYPE#</option> </cfoutput> </select> <!----“CATEGORY†LIST BOX – SEARCH PARAMETER----------------------> Category: <select name="CATEGORY_ID"> <option value="" <cfif trim(form.CATEGORY_ID) eq "">selected </cfif>> All </option> <cfoutput query="categories"> <option value="#CATEGORY_ID#" <cfif form.CATEGORY_ID eq categories.CATEGORY_ID>selected</cfif>>#CATEGORY#</option> </cfoutput> </select> <!----“SEARCH†TEXT BOX – SEARCH PARAMETER----------------------> <label>Search: <cfoutput> <input name="searchfield" type="text" size="10" value="#form.searchfield#" /></cfoutput> </label> <input name="submit" type="submit" value="Submit" /> <table width="95%"> <tr> <td colspan="2" valign="top"><h3> <!----CHECKBOX TO TOGGLE MORE SEARCH FEATURES ----------------------> <input type="checkbox" <cfif form.tttt neq ""> checked="checked" </cfif> name="tttt" value="1" size="50" onclick="toggleBrands('moreoptions', this.checked)" /> </label> More options </h3></td></tr> <tr> <td><div id="moreoptions" style="padding-left:8px; <cfif form.tttt eq "">display:none;<cfelse>display:block;</cfif>" > <table width="100%" border="0" align="left"> <tr> <!----“INGREDIENT1†AND “INGREDIENT2†LIST BOXES – SEARCH PARAMETERS----------------> <td width="47%" valign="baseline"><h3 class="style6">Contains ingredient(s):</h3></td> <td width="25%" valign="top"><select name="INGREDIENT1" style="Width:20" width="20"> <option value="" <cfif trim(form.INGREDIENT1) eq "">selected </cfif>> All </option> <cfoutput query="INGREDIENTS"> <option value="#INGREDIENT_ID#" <cfif form.INGREDIENT1 eq INGREDIENTS.INGREDIENT_ID>selected</cfif>>#INGREDIENT_NAME#</option> </cfoutput> </select></td> <td width="4%"><h3 class="style6">and</h3></td> <td> </td> <td valign="top"><select name="INGREDIENT2"> <option value="" <cfif trim(form.INGREDIENT2) eq "">selected </cfif>> All </option> <cfoutput query="INGREDIENTS"> <option value="#INGREDIENT_ID#" <cfif form.INGREDIENT2 eq INGREDIENTS.INGREDIENT_ID>selected</cfif>>#INGREDIENT_NAME#</option> </cfoutput> </select></td> </tr> </table> </div></td></tr> </table> </form> <!----OUTPUT RESULTS ----------------------> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <cfoutput query="type" group="type_id"> <tr> <th colspan="3" align="left"><h2><span class="style5">#TYPE#</span></h2> </th> </tr> <tr> <cfset typeRow = 1> <cfoutput group="recipe_name"> <td width="33%"><h3><a href="#LINK#">#RECIPE_NAME#</a></h3></td> <cfif typeRow mod 3 eq 0> </tr><tr> </cfif> <cfset typeRow = typeRow + 1 /> </cfoutput> </tr> </cfoutput> </table> Code (markup): I understand that problems with queries may also be because of the layout of one's datasource. So, here's a quick review of my tables in my database, and what fields exist. I'll place (n) or (t) or (k) next to each to denote whether the field type is number or text or the key ID: TBLRECIPES: RECIPE_ID (k), RECIPE_NAME (t), LINK (t), TYPE_ID (n), CATEGORY_ID (n) TBLCATEGORY: CATEGORY_ID (k), CATEGORY (t) TYPE: TYPE_ID (k), TYPE (t) TBLINGREDIENTS: INGREDIENT_ID (k), INGREDIENT (t), INGREDIENT_NAME (t) TBLRECIPEINGREDIENTS: RECIPEINGREDIENTS_ID (k), INGREDIENT_ID (n), RECIPE_ID (n) So, as I said, the filter works, especially for the category and type portion. The problem is with selecting an ingredient to filter by. It doesn't yield all of the results it should, only some. THANKS P.S. The oddest thing, is that one ingredient I tried, yielded all the results it should. When I try other ingredients, it's the contrary. Very strange...
Looking into it a little more, i'm "thinking" that the recipes that are not displaying, are those that I JUST added to my database. However, I KNOW that they have been added, and uploaded to my server. I also have a separate search criteria for recipes based on the letter they begin with. This uses a LIKE 'A%' SQL parameter for recipes beginning with the letter A. This query works fine and includes all of my new recipes as well. Since the query that does not work is using the RECIPE_ID as its connection to pull the recipes form the TBLRECIPES table, I assume it'd have to do with this. However, what on earth could it be? They're just numbers!!! P.S. One ingredient that is not yielding the accurate results when I search has INGREDIENT_ID = 209. 6 recipes should be returned, but only 1 returns. When I created a separate test page with the following code: <cfquery name="sss" datasource="MYDATABASE"> SELECT r.RECIPE_ID, r.RECIPE_NAME, i.INGREDIENT_ID FROM TBLRECIPES AS r INNER JOIN TBLRECIPEINGREDIENTS i ON r.RECIPE_ID = i.RECIPE_ID WHERE INGREDIENT_ID = 209 </cfquery> <cfoutput query="sss"> <table> <tr> <td>#RECIPE_ID#</td><TD>#RECIPE_NAME#</TD></tr></table></cfoutput> Code (markup): 6 results are shown! So, there's no problem when I execute it like this. I'm even more confused now!
Nevermind, I figured it out. I didn't have a TYPE set for the new recipes in my database, and thus, they would not show up in the list.