Coldfusion: Simple form question - displaying drop down list value

<cfparam name="form.category_ID" default="">
<cfparam name="form.type_ID" default="">

<cfquery name="category" datasource="mydatasource">
	SELECT CATEGORY, CATEGORY_ID, TYPE, TYPE_ID
	FROM MYTABLE
	<!--- return all records if neither option was selected --->
	WHERE  1 = 1
	<cfif val(form.category_id) GT 0>
		AND	 CATEGORY_ID = <cfqueryparam value="#form.category_ID#" cfsqltype="cf_sql_integer">
	</cfif>
	<cfif val(form.type_id) GT 0>
		AND	TYPE_ID = <cfqueryparam value="#form.type_ID#" cfsqltype="cf_sql_integer">
	</cfif>
	ORDER BY CATEGORY_ID, TYPE_ID
</cfquery>

[COLOR="red"]<cfoutput query="category">[/COLOR]
<form method="post" action="Untitled-1.cfm">
  <select name="category_ID">
    <option value=""> Select something </option>
    <option value="1">Category1</option>
    <option value="2">Category2</option>
    <option value="3">Category3</option>

[COLOR="Red"]<option value="#Category_ID#"[/COLOR][COLOR="SeaGreen"]     <cfif form.category_id eq category.Category_ID>selected</cfif>>#Category_name#</option>[/COLOR]



  </select>
  
  <select name="type_ID">
    <option value=""> Select something </option>
    <option value="1">Type1</option>
    <option value="2">Type2</option>
    <option value="3">Type3</option>
  </select>

  <input type="submit">
</form>


<!---Rest of category query output--->
</cfoutput>

Code (markup):

I get the following error:

Error Occurred While Processing Request  
Variable CATEGORY_NAME is undefined.  
 

Code (markup):

on the green line above.

 

Do you have a column named Category_name column in your query ?

 

Haha... yeah, I noticed. However, since I didn't need the CATEGORY_NAME field before, I figured I wouldn't HAVE TO use it here.

I do not have a CATEGORY_NAME in my query to pull. I'd have to pull it from another table, TBLCATEGORIES,... and I just don't wanna mess with the joins I have in the code. Is there a simple way to create another query just for this? Or, would I run into the problem of trying to execute a query within a query

How can I easily draw the CATEGORY_NAME field from the TBLCATEGORIES within my current query without connecting it to any other tables' fields?

 

What does this actually do in the query?

I'm trying to think of how to integrate a search feature as well, but the WHERE function is already being used. Can WHERE be used twice in the same query (I would think no - but i've heard of executing "queries within queries"...).

I was thinking of setting a parameter...

<cfset Q="">

Code (markup):

Then including in the query:

WHERE RECIPE_ID = %#Q#%

Code (markup):

 

Well, I'm a bit confused by the code on this one. I understand you're trying to pre-select an item in the list (ie preserve the user's previous selection). But I don't know whether its really a hard coded list like this

Or if you're really generating the list from a query.

 

You can only have one WHERE clause. But.. you can have multiple AND / OR conditions.

Its a technique used to make constructing a dynamic WHERE clause simpler when you have two or more optional parameters. It will also ensure the query always returns all records if no search criteria was entered

WHERE 1 = 1 -- this will always be true for all records

You just add the optional criteria like this. The resulting sql statement will always be valid.

<cfif trim(form.someField) neq "">
AND SomeColumn = #form.someField#
</cfif>
<cfif trim(form.someKeyWordField) neq "">
AND SomeOtherColumn LIKE '%#form.someKeyWordField#%'
</cfif>

 

Yeah, I see that being my problem. It is hard coded. I think I did that just to get it to work, rather than thinking it through...

I guess I could have code within the query as such:

<cfparam name="form.category_ID" default="">
<cfparam name="form.type_ID" default="">

<cfquery name="category" datasource="mydatasource">
	SELECT r.CATEGORY_ID, r.TYPE, r.TYPE_ID, tt.CATEGORY, tt.CATEGORY_ID
	FROM MYTABLE AS r

[COLOR="SeaGreen"]INNER JOIN TBLCATEGORIES tt
   ON r.CATEGORY_ID = tt.CATEGORY_ID
[/COLOR]

	WHERE  1 = 1
	<cfif val(form.category_id) GT 0>
		AND	 CATEGORY_ID = <cfqueryparam value="#form.category_ID#" cfsqltype="cf_sql_integer">
	</cfif>
	<cfif val(form.type_id) GT 0>
		AND	TYPE_ID = <cfqueryparam value="#form.type_ID#" cfsqltype="cf_sql_integer">
	</cfif>
	ORDER BY CATEGORY_ID, TYPE_ID
</cfquery>

<cfoutput query="category">
<form method="post" action="Untitled-1.cfm">
  <select name="category_ID">
    <option value=""> Select something </option>
    <option value="[COLOR="SeaGreen"]#category.CATEGORY_ID#[/COLOR]">#[COLOR="Red"]category.CATEGORY#</option>[/COLOR]


<option value="#Category_ID#"     <cfif form.category_id eq category.Category_ID>selected</cfif[COLOR="Red"]>>#CATEGORY#</[/COLOR]option>



  </select>
  
  <select name="type_ID">
    <[COLOR="SeaGreen"]option value="#category.TYPE_ID"> [/COLOR]#[COLOR="Red"]category.TYPE#</option>[/COLOR]
  </select>

  <input type="submit">
</form>


<!---Rest of category query output--->
</cfoutput>

Code (markup):

But, I don't know how if the first green part above will pull the CATEGORY value appropriately. Secondly, the second green part, how to select the option value appropriately (would CATEGORY_ID be appropriate)?

 

I modified my code as such:

<body>

<cfparam name="form.CATEGORY_ID" default="">
<cfparam name="form.TYPE_ID" default="">

  <cfquery name="myquery" datasource="mydatabase">
SELECT dt.TYPE, r.TYPE_ID, r.RECIPE_ID, r.RECIPE_NAME, r.CATEGORY_ID, r.LINK, tt.CATEGORY_ID, tt.CATEGORY
FROM   (TBLRECIPES AS r INNER JOIN TBLTYPE dt
	ON r.TYPE_ID = dt.TYPE_ID)
INNER JOIN TBLCATEGORY AS tt
   ON r.CATEGORY_ID = tt.CATEGORY_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>
	ORDER BY r.TYPE_ID
</cfquery>

<form method="post" action="Untitled-1.cfm">
TYPE:
 
        <select name="TYPE_ID">
		<option value="">All</option>
		<cfoutput query="myquery">
		
			<cfoutput>
        			<option value="#myquery.TYPE_ID#"># myquery.TYPE#</option>	
			</cfoutput> 
				 
			<cfoutput>
					<option value="#TYPE_ID#"
     					<cfif form.type_id eq myquery.TYPE_ID>selected
						</cfif>>#myquery.TYPE#</option>
	 		</cfoutput>
			
		</cfoutput>
        </select> 
		
CATEGORY:
        <select name="CATEGORY_ID">
		<option value="">All</option>
		<cfoutput query="myquery">
		
			<cfoutput>
            		<option value="#myquery.CATEGORY_ID#">#myquery.CATEGORY#</option>	 	 
			</cfoutput>
			
			<cfoutput>
					<option value="#CATEGORY_ID#"
     					<cfif form.CATEGORY_ID eq myquery.CATEGORY_ID>selected
	 					</cfif>>#myquery.CATEGORY#</option>
	 	 	</cfoutput>
		
		</cfoutput>
        </select>
  </form>
<table width="100%" border="0" cellspacing="0" cellpadding="0">

<cfoutput query="myquery" group="type">
   <tr>
   	 <th colspan="3" align="left"><h2><span class="style5">#TYPE#</span></h2>
   	   </th>
   </tr>
   <tr>
   <cfset typeRow = 1>
   <cfoutput>
      	  <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>



</body>
</html>

Code (markup):

The bottom portion of this code (the table) is fine (we didn't touch this).

Some problems:

1. The drop down lists contain many of each type and category (I think it listed the value for each recipe). I tried thinking about this, because I did this before and you corrected me (I guess the cfoutputs nested).

2. When I filter by a type and category, then try choosing another type and category from the drop down, only the one that has been chosen is available to choose from (all the others aren't listed).

 

What problem do you think you would have with the first section in green? Assuming there is a matching CATEGORY_ID value for all records in MYTABLE, the query results should be the same. The difference being the results will include the CATEGORY column (name I assume?).

But the SELECT list should not include two columns with the same name, so remove one of the CATEGORY_ID columns. For example

SELECT r.TYPE, r.TYPE_ID, tt.CATEGORY, tt.CATEGORY_ID
...

Code (markup):

To generate the two select lists, move the cfoutput inside the select tags.

<cfparam name="form.category_ID" default="">
<cfparam name="form.type_ID" default="">

... the query code ...

<select name="category_ID">
    <option value="" <cfif trim(form.category_ID) eq "">selected</cfif>> Select something </option>
    <cfoutput query="category">
		<option value="#category#" 
			<cfif form.category_id eq category.Category_ID>selected</cfif>>
					#CATEGORY#
		</option>
	</cfoutput>
  </select>
  
  <select name="type_ID">
    <cfoutput query="category">
	    <option value="#category.TYPE_ID#" <cfif form.type_ID eq category.TYPE_ID>selected</cfif>>#category.TYPE#</option>
	</cfoutput>
  </select>

Code (markup):

 

Thank you. Here is my new and improved code:

<cfparam name="form.CATEGORY_ID" default="">
<cfparam name="form.TYPE_ID" default="">

  <cfquery name="myquery" datasource="mydatabase">
SELECT dt.TYPE, r.RECIPE_ID, r.RECIPE_NAME, r.LINK, tt.CATEGORY_ID, tt.CATEGORY, r.TYPE_ID
FROM   (TBLRECIPES AS r INNER JOIN TBLTYPES dt
	ON r.TYPE_ID = dt.TYPE_ID)
INNER JOIN TBLCATEGORY AS tt
   ON r.CATEGORY_ID = tt.CATEGORY_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>
	ORDER BY r.TYPE_ID
</cfquery>

<form method="post" action="Untitled-2.cfm">

        <select name="TYPE_ID">
		<option value="" <cfif trim(form.TYPE_ID) eq "">selected									
							</cfif>> Select something </option>

		<cfoutput query="myquery">
        <option value="#myquery.TYPE_ID#" <cfif form.TYPE_ID eq myquery.TYPE_ID>selected</cfif>>#myquery.TYPE#</option>	
		</cfoutput> 
        </select> 
		
        <select name="CATEGORY_ID">
		<option value="" <cfif trim(form.CATEGORY_ID) eq "">selected
							</cfif>> Select something </option>
							
		<cfoutput query="myquery">
		<option value="#myquery.CATEGORY_ID#" <cfif form.CATEGORY_ID eq myquery.CATEGORY_ID>selected</cfif>>#myquery.CATEGORY#</option>
		</cfoutput>
        </select>
		
		<input name="submit" type="submit" />
  </form>
<table width="100%" border="0" cellspacing="0" cellpadding="0">

<cfoutput query="myquery" group="TYPE">
   <tr>
   	 <th colspan="3" align="left"><h2><span class="style5">#TYPE#</span></h2>
   	   </th>
   </tr>
   <tr>
   <cfset typeRow = 1>
   <cfoutput>
      	  <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):

The filter features work, as well as placing the selected category and type in the drop down list after filtering the results.

I still have the two issues from my last post:

 

Oh, I see what you mean now. I think you'll need to use (3) queries here

1) To get all available types from your TYPE table
2) To get all available categories from your CATEGORY table
3) To get the filtered results for display in the <table>

Then populate the TYPE select list with query 1. Populate the category select list with query 2. Output the <table> with query 3 (as you were doing before).

The reason is because the current query doesn't always include all types and categories due to the filters. So you must run separate queries to get all available types and categories. Does that make sense?

 

Do you mean simple queries such as:

<cfquery name="types" datasource="mydatabase">
SELECT TYPE_ID, TYPE
FROM TBLTYPES
ORDER BY TYPE_ID
</cfquery>

<cfquery name="categories" datasource="mydatabase">
SELECT CATEGORY_ID, CATEGORY
FROM TBLCATEGORY
ORDER BY CATEGORY_ID
</cfquery>

Code (markup):

Or, queries that are joined to the TBLRECIPES table as such:

<cfquery name="types" datasource="mydatabase">
SELECT t.TYPE_ID, t.TYPE, r.TYPE_ID
FROM TBLTYPES AS c
INNER JOIN TBLRECIPES r
ON t.TYPE_ID = r.TYPE_ID
ORDER BY TYPE_ID
</cfquery>

<cfquery name="categories" datasource="mydatabase">
SELECT c.CATEGORY_ID, c.CATEGORY, r.CATEGORY_ID
FROM TBLCATEGORY AS c
INNER JOIN TBLRECIPES r
ON c.CATEGORY_ID = r.CATEGORY_ID
ORDER BY CATEGORY_ID
</cfquery>

Code (markup):

I believe the latter, so we can intcorporate it into the filter function. Right?


Once this is done, does the rest of the code need to be modified with this new approach (3 query approach)?

 

You need the simple queries. If you incorporate the filters into the first (2) queries you won't get all of the available types and categories. So you'll get similar results to what's happening now: not all items will be displayed.

Once you add the (2) queries to get the types and categories, just change the query names used in the select lists. Caveat - this assumes the column names are the same as in the original query.

This is not tested, so beware of typos..

<select name="CATEGORY_ID">
   <option value="" <cfif trim(form.CATEGORY_ID) eq "">selected
				</cfif>> Select something </option>
							
   <cfoutput query="[COLOR="SeaGreen"]categories[/COLOR]">
	 <option value="#CATEGORY_ID#" 
          <cfif form.CATEGORY_ID eq [COLOR="SeaGreen"]categories[/COLOR].CATEGORY_ID>selected</cfif>>#CATEGORY#</option>
   </cfoutput>

... do the same for the type list

Code (markup):

 

To clarify, the third query "myquery" will still contain the filters.

 

Excellent! It works. I implemented the following code as recommended, and I now have no issues:

<cfparam name="form.CATEGORY_ID" default="">
<cfparam name="form.TYPE_ID" default="">

<form method="post" action="recipe_book.cfm">

<cfquery name="types" datasource="mydatabase">
SELECT TYPE_ID, TYPE
FROM TBLTYPES
ORDER BY TYPE_ID
</cfquery>

<cfquery name="categories" datasource="mydatabase">
SELECT CATEGORY_ID, CATEGORY
FROM TBLCATEGORY
ORDER BY CATEGORY_ID
</cfquery>

<cfquery name="listitems" datasource="mydatabase">
SELECT dt.TYPE, r.RECIPE_ID, r.RECIPE_NAME, r.LINK, tt.CATEGORY_ID, tt.CATEGORY, r.TYPE_ID
FROM   (TBLRECIPES AS r INNER JOIN TBLTYPES dt
	ON r.TYPE_ID = dt.TYPE_ID)
INNER JOIN TBLCATEGORY AS tt
   ON r.CATEGORY_ID = tt.CATEGORY_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>
	ORDER BY r.TYPE_ID
</cfquery>

 <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>
		

<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>
		
		<input name="submit" type="submit" />
  </form>
<table width="100%" border="0" cellspacing="0" cellpadding="0">

<cfoutput query="listitems" group="TYPE">
   <tr>
   	 <th colspan="3" align="left"><h2><span class="style5">#TYPE#</span></h2>
   	   </th>
   </tr>
   <tr>
   <cfset typeRow = 1>
   <cfoutput>
      	  <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>
</body>

Code (markup):

Back on a previous comment of your's on integrating a search field:

I'm wondering why you have the two IF statements. Is it because the first one will return results that FULLY match the search entirely, and the second will return results that PARTIALLY match the search?

I'm thinking of having the search be able to search solely the RECIPE_NAME and return results that include any part of the search in its name:

<cfquery name="listitems" datasource="mydatabase">
SELECT dt.TYPE, r.RECIPE_ID, r.RECIPE_NAME, r.LINK, tt.CATEGORY_ID, tt.CATEGORY, r.TYPE_ID
FROM   (TBLRECIPES AS r INNER JOIN TBLTYPES dt
	ON r.TYPE_ID = dt.TYPE_ID)
INNER JOIN TBLCATEGORY AS tt
   ON r.CATEGORY_ID = tt.CATEGORY_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>
[COLOR="Red"]<cfif trim(form.searchfield) neq "">
AND r.RECIPE_NAME LIKE '%#form.searchfield#%'
</cfif> [/COLOR]
	ORDER BY r.TYPE_ID
</cfquery>

Code (markup):

The % within the red statement should be able to take care of the partially correct searches (i.e. "ROT" will return "CARROT CAKE" recipe).

 

NM, I got it:

[COLOR="Red"]<cfparam name="form.searchfield" default="">[/COLOR]
<cfquery name="listitems" datasource="mydatabase">
SELECT dt.TYPE, r.RECIPE_ID, r.RECIPE_NAME, r.LINK, tt.CATEGORY_ID, tt.CATEGORY, r.TYPE_ID
FROM   (TBLRECIPES AS r INNER JOIN TBLTYPES dt
	ON r.TYPE_ID = dt.TYPE_ID)
INNER JOIN TBLCATEGORY AS tt
   ON r.CATEGORY_ID = tt.CATEGORY_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>
[COLOR="Red"]<cfif trim(form.searchfield) neq "">
AND r.RECIPE_NAME LIKE '%#form.searchfield#%'
</cfif> [/COLOR]
	ORDER BY r.TYPE_ID
</cfquery>

Code (markup):

 

Minor issue... is there a way to keep the search text in the search box after the search has been performed? (so the user remember what he/she searched on)?

I tried looking at the other code to add something within the search text field like:

value="#form.searchfield#"

Code (markup):

but I know that's not right.

 

No, that's what I would have suggested. How is it not right?

 

I just figured i'd have something wrong. I'll try it. Thanks.