Coldfusion: Simple form question - displaying drop down list value

Hello,

I have the following code (Coldfusion MX7):

<form id="form1" name="form1" method="post" action="">
  <label>category
  <select name="select">
    <option value="You selected category 1">Category1</option>
    <option value="You selected category 2">Category2</option>
    <option value="You selected category 3">Category3</option>
  </select>
  </label>
</form>

<cfoutput>
#form1.category#
</cfoutput>

Code (markup):

I know this is wrong.

What I want, is when the user selects "Category1", "Category2", or "Category3", I want "You selected category 1", "You selected category 2", or "You selected category 3" to show up respectively below the list.

 

You mean before the form is submitted?

 

well, it doesn't really even have to be a form. I just want a drop down list, and whatever the person selects, appears automatically. No submit, no action page...

 

You could do it with some simple javascript

<script type="text/javascript">
	function showSelection(theList) {
		var selectedValue = theList.options[theList.selectedIndex].value;
		document.getElementById('contentDiv').innerHTML = selectedValue;
	}
</script>
<form>
  <select name="category" onChange="showSelection(this);">
    <option value=""> Select something </option>
    <option value="You selected category 1">Category1</option>
    <option value="You selected category 2">Category2</option>
    <option value="You selected category 3">Category3</option>
  </select>
</form>
<!--- this div will contain the selected list value --->
<div id="contentDiv" style="background-color: #ffff00"></div>

Code (markup):

 

Thanks for the reply. I may have been on the wrong thought process...

Instead of having the value display, I'd like it to be code in a query.

For example:

<script type="text/javascript">
	function showSelection(theList) {
		var selectedValue = theList.options[theList.selectedIndex].value;
		document.getElementById('contentDiv').innerHTML = selectedValue;
	}
</script>
<form>
  <select name="category" onChange="showSelection(this);">
    <option value=""> Select something </option>
    <option value="WHERE CATEGORY_ID = 1">Category1</option>
    <option value="WHERE CATEGORY_ID = 2">Category2</option>
    <option value="WHERE CATEGORY_ID = 3">Category3</option>
  </select>
</form>

<cfquery name="queryname" datasource="mydatasource">
SELECT CATEGORY_ID, CATEGORY
FROM   MYTABLE
#contentDiv#
</cfquery>

Code (markup):

Do you see what I'm trying to do? It will actually create a filter of what should be displayed, based on Category number.

 

For that you'll have to submit the form (or use ajax). The reason is javascript runs in the browser, after the ColdFusion code is already finished. So you have to submit the form so ColdFusion can see the values.

The advantage of using Ajax is that it lets you do this without submitting the form. The disadvantage is its a little more complicated.

not tested

<cfquery name="queryname" datasource="mydatasource">
	SELECT CATEGORY_ID, CATEGORY
	FROM   MYTABLE
	<!--- if the form was submitted, get only the selected category
		otherwise, get all categories 
	--->
	<cfif IsDefined("form.category_id")>
		WHERE  CATEOGORY_ID = <cfqueryparam value="#form.category_ID#" cfsqltype="cf_sql_integer">
	</cfif>
</cfquery>

<form method="post" action="x.cfm">
  <select name="categoryID">
    <option value=""> Select something </option>
    <option value="1">Category1</option>
    <option value="2">Category2</option>
    <option value="3">Category3</option>
  </select>
  
  <input type="submit">
</form>

<cfoutput query="queryname">
	#CATEGORY_ID# #CATEGORY#<br>
</cfoutput>

Code (markup):

 

Gotcha. I have the following. However, when I select one of the categories, it does not automatically filter the results to those with that specific category # in my database.

This is my .cfm. My page is titled Untitled-1.cfm. I think that may be the problem, because I post it to the same page....

<cfquery name="category" datasource="mydatasource">
SELECT CATEGORY, CATEGORY_ID
FROM MYTABLE
<cfif IsDefined("form.category_id")>
		WHERE  CATEGORY_ID = <cfqueryparam value="#form.category_ID#" cfsqltype="cf_sql_integer">
	</cfif>
ORDER BY CATEGORY_ID
</cfquery>


<form method="post" action="Untitled-1.cfm">
  <select name="categoryID">
    <option value=""> Select something </option>
    <option value="1">Category1</option>
    <option value="2">Category2</option>
    <option value="3">Category3</option>
  </select>
  
  <input type="submit">
</form>


<cfoutput query="category">
 
ETC...

</ cfoutput>

Code (markup):

Also, I apologize for a possible double post. I just noticed your response from the following thread:

http://forums.digitalpoint.com/showthread.php?t=578369&highlight=filter

I can check this one out too.

Thanks!

 

Oops. My mistake. There's a typo in the name of the select list.

Change this
<select name="categoryID">

To
<select name="category_ID">

 

That did it!!! Thanks again!!

 

Welcome

 

A follow up. I tried implementing a second "filter". Here, it would be "TYPE". So, I'd have Category and Type drop downs.

I tried the following:

<cfquery name="category" datasource="mydatasource">
SELECT CATEGORY, CATEGORY_ID, TYPE, TYPE_ID
FROM MYTABLE
<cfif IsDefined("form.category_id")>
	WHERE  CATEGORY_ID = <cfqueryparam value="#form.category_ID#" cfsqltype="cf_sql_integer">
</cfif>
<cfif IsDefined("form.type_id")>
	WHERE  TYPE_ID = <cfqueryparam value="#form.type_ID#" cfsqltype="cf_sql_integer">
[COLOR="red"]</cfif>[/COLOR]
ORDER BY CATEGORY_ID, TYPE_ID
</cfquery>


<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>
  </select>
  
  <input type="submit">
</form>

<form method="post" action="Untitled-1.cfm">
  <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>

Code (markup):

This returned an error. I also tried exchanging the second WHERE with an AND. This also did not work. I would get an error on the red line above.

 

Do you want the filters to be mutually exclusive: you can filter on either a category or type but not both?

 

Both in any combination.

For instance, if there were a way to have:

WHERE TYPE_ID = ...
WHERE CATEGORY_ID = ...

Code (markup):

it would be perfect. So if someone solely selects Category 2... all that are category 2 will show up. If they check category 2 and type 5, then all results that are BOTH category 2 and type 5 will show. etc.

 

Then put both lists in the same <form> and try something like this. (It will return all records if neither a category or type is selected)

Not tested

<!--- define defaults for the first time the page loads --->
<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>


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

Code (markup):

 

Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] The specified field 'TYPE_ID' could refer to more than one table listed in the FROM clause of your SQL statement.  
  
The error occurred in C:\CFusionMX7\wwwroot\mywebsite\test.cfm: line 14
 
12 : 	<cfif val(form.TYPE_ID) GT 0>
13 : 		AND	TYPE_ID = <cfqueryparam value="#form.TYPE_ID#" cfsqltype="cf_sql_integer">
14 : 	</cfif>
15 : 	ORDER BY CATEGORY_ID, TYPE_ID
16 : 

 

Code (markup):

 

That's a problem with your real query. Its saying the TYPE_ID column exists in more than one table in your query. You have to specify which one you're referring to (ie use TableName.ColumnName = instead of just ColumName = )

 

You're a genius.

It works now. The only remaining issue is that when I select a category (say... Category 1)... and hit submit, it filters properly, however, the drop down list reverts to saying "All" instead of "Category 1". This could be confusing to the user. If they select a category or type, I would like that name to remain appeared in the list.

Does that make sense?

 

Yes, it does. If you're generating a plain html select list from a cfquery you'll need to add a cfif condition. If the selected category id equals the id in the current row of the query, mark it as selected

<!--- 
       not tested
--->
<select ...>
<cfoutput query="yourQuery">
   <option value="#Category_ID#"
     <cfif form.category_id eq yourQuery.Category_ID>selected</cfif>>#Category_name#</option>
</cfoutput>
</select>

Code (markup):

 

But does this keep the selected list item displayed in the list? Currently, after I filter the data on the page, the list boxes at the top of the page look like this:



Instead of displaying "All", i'd like it to display what was chosen. Right now it's a bit confusing, because one may think he/she is looking at All results, but is really looking at what he/she just filtered on.


Also, is there an easy way to create a "search box" filter with coldfusion code? I would want it to:

1. Not be case sensitive.
2. Only have to match any part of a field (recipe name).

So, if someone typed "ROT"... the recipe "Carrot cake" would still show.

Thanks.

 

If you've coded it like we discussed it should maintain the previous selection. If its always reverting to "All" then there is an error in your code.

Just create a text box in your form. Then in your query use the LIKE operator. You can see an example in the "Using LIKE" section here
http://www.w3schools.com/sql/sql_where.asp

Most db's are case sensitive by default, its likely that won't be an issue