Help with CFSELECT in a Drop-Down Menu? 3

Chinyere · Sep 16, 2003

Hi Guys,

I am back with another CF question for you.

I am trying to use the <CFSELECT> tag with no real success so far... I am using it in a form (drop-down menu) and I would like after the user selects an option for details of that selected option to be displayed on the next (post) page. This seems simple to do in theory to me but I just cannot put it into practice.

I have an idea that it requires that an ID be passed from the first page to the second but I cannot get it to work. In my database, I have all of the fruits listed with information for each of them, such as number_of_calories, color, vitamin_content, etc. I would like for the second page to display these details depending on what the user selects. I have created a template for the second page and I would like for the database to just "spit out" information to this template using CFOUTPUT.

Here is the sample code:

Code:

Here is my QUERY:

<CFQUERY NAME=&quot;get_fruit&quot; DATASOURCE=&quot;fruits&quot;>
SELECT * FROM fuitdatabase
WHERE fruit_ID = &quot;fruit_ID&quot;
</CFQUERY>
______________________________________________________________


Code for First Page:

<CFFORM ACTION=&quot;details_page.cfm&quot; METHOD=Post>

<CFSELECT name=&quot;fruit&quot;>
<option value=&quot;Select Your Fruit&quot;>Select Your Fruit
<option value=&quot;Apples&quot;>Apples
<option value=&quot;Oranges&quot;>Oranges
<option value=&quot;Grapes&quot;>Grapes
</CFSELECT><BR><BR>
<INPUT TYPE=&quot;hidden&quot; NAME=&quot;#fruit_ID#&quot; VALUE=&quot;#fruit_ID#&quot;>
<INPUT TYPE=&quot;Image&quot; Name=&quot;Point&quot; SRC=&quot;cdrh\images\submit.gif&quot; border=&quot;0&quot;>
</CFFORM>

______________________________________________________________

Code for Second Page

<CFQUERY NAME=&quot;get_fruit&quot; DATASOURCE=&quot;fruits&quot;>
SELECT * FROM fuitdatabase
WHERE fruit_ID = &quot;fruit_ID&quot;
</CFQUERY>

CFOUTPUT QUERY=&quot;get_fruit&quot;>

<TABLE WIDTH=&quot;100%&quot; Cellpadding=&quot;0&quot; Cellspacing=&quot;0&quot; BORDER=&quot;1&quot;>

<TR>
<TD colspan=&quot;2&quot;>
<H2>Fruits ----</H2></TD>
</TR>

<TR>
<TD colspan=&quot;2&quot;>
<H2>----APPLE----</H2></TD>
</TR>

<TR>
<TD>
<B>Number of Calories:</B> </TD><TD>#form.number_of_calories#</TD>
</TR>

<TR>
<TD><B>Color:</B></TD> <TD>#form.color#</TD>
</TR>

<TR>
<TD><B>Vitamin Content:</B></TD> <TD>#form.vitamin_content#</TD>
</TR>
</TABLE>

______________________________________________________________

This is what the Second Page should look like:

Fruits ----

----APPLE----

Number of Calories: approximately 75 per apple

Color: Red

Vitamin Content: Vitamin A, B, C, and D

______________________________________________________________

Thanks for any help at all!

Chinyere [afro2]

ecobb · Sep 16, 2003

1) If you're using cfselect, you don't need the <option> tags, cfselect can populate itself from the query.
See faq232-3628 for more on setting up cfselect
See faq232-1158 for more on doing the same thing with a regular <select>

2) On the second page, you will need to reference your form variables in your query in order for it to work. Like this:

<CFQUERY NAME="get_fruit" DATASOURCE="fruits">
SELECT * FROM fuitdatabase
WHERE fruit_ID = "#Form.fruit_ID#"
</CFQUERY>

If you want the query to pull information relavent to the selection in the select box, you will need to pass the fruit_ID in the select box, and not a hidden field. If you're populating it from a query, it should be something like this:

<option value="#fruit_ID#>#FruitName#</option>

Hope This Helps!

Ecobb
- I hate computers!

csteinhilber · Sep 16, 2003

Chinyere,
I'm not sure if your sample code is actual code, or theory. But just to be sure... the

Code:

<CFSELECT>

tag writes the OPTIONS itself... particularly if you're using it in conjunction with a query. You would generally do something like:

Code:

<CFQUERY NAME=&quot;get_fruit&quot; DATASOURCE=&quot;fruits&quot;>
SELECT fruit_id, fruit_name FROM fuitdatabase
</CFQUERY>

<CFSELECT name=&quot;selected_fruit_id&quot; message=&quot;Please select a fruit&quot; query=&quot;get_fruit&quot; value=&quot;fruit_ID&quot; display=&quot;fruit_name&quot; required = &quot;Yes&quot; ...>
</CFSELECT>

this will automatically produce a dropdown box listing the names of the fruits (assuming the column name is something like 'fruit_name'). And when the form is submitted, by specifying 'fruit_id' as the value attribute, the action page will receive the ID in the variable

Code:

#FORM.fruit#

.

Some things to remember, though. CFSELECT only works when it's within CFFORM tags. If you're using standard FORM tags, you'll need to use a regular SELECT and create the options yourself. This is what I do anyway. I haven't used CFSELECT for over 3 years. But it can have it's place... and using it in conjunction with a query is it. If you want to manually populate the option tags, it's probably better to just use a SELECT.

Also, when you're doing your queries (on the action page) like

Code:

<CFQUERY NAME=&quot;get_fruit_info&quot; DATASOURCE=&quot;fruits&quot;>
SELECT * FROM fuitdatabase
WHERE fruit_ID = &quot;fruit_ID&quot;
</CFQUERY>

you need to make sure that you're using variables where they're called for. For instance, I doubt that this query will return any results because most likely there are NO records where fruit_id = "fruit_id". You're probably looking to stick a variable in there... something more like:

Code:

<CFQUERY NAME=&quot;get_fruit&quot; DATASOURCE=&quot;fruits&quot;>
SELECT * FROM fuitdatabase
WHERE fruit_ID = '

Code:

#FORM.selected_fruit_id#

Code:

' </CFQUERY>

where you want to compare the fruit_id field against the ID that the form submitted (ie - the value chosen from the dropdown box). You also want to make sure you use single quotes (') instead of double-quotes (&quot in your SQL statements. While some databases are going to support double-quotes, most aren't. And if you ever were to change databases, you'd then have to go in and change all your queries. Always using single quotes makes your queries much more standard and much more portable.

Finally, the code for the action page. After you do the query, it sounds like you want to reference that query's resultset in your output rather than the FORM variables you have in your sample code.

Code:

<CFQUERY NAME="get_fruit_info" DATASOURCE="fruits"> SELECT * FROM fuitdatabase WHERE fruit_ID = "#FORM.selected_fruit_id#" </CFQUERY> <CFIF get_fruit_info.RecordCount GT 0> <TABLE WIDTH="100%" Cellpadding="0" Cellspacing="0" BORDER="1"> <TR> <TD colspan="2"> <H2>Fruits ----</H2> </TD> </TR> <CFOUTPUT QUERY="get_fruit_info"> <TR> <TD colspan="2"> <H2>----#get_fruit_info.fruit_name#----</H2> </TD> </TR> <TR> <TD> <B>Number of Calories:</B> </TD> <TD> #get_fruit_info.number_of_calories# </TD> </TR> <TR> <TD> <B>Color:</B> </TD> <TD> #get_fruit_info.color# </TD> </TR> <TR> <TD> <B>Vitamin Content:</B> </TD> <TD> #get_fruit_info.vitamin_content# </TD> </TR> </CFOUTPUT> </TABLE> <CFELSE> The fruit you selected is not in our database. </CFIF>

Hope it helps. Let me know if it's not clear.

-Carl

Chinyere · Sep 16, 2003

Thanks you two!!! [wavey2]

I gave your suggestions a try...

csteinhilber,
When I tried it, I received an answer of:

"The condition that you have selected is not in our database."

as my recordset even though I had selected a fruit.

Do I have to modify the following line:

<CFIF get_fruit_info.RecordCount GT 0>

Thanks again for all the help!

Chinyere [afro2]

csteinhilber · Sep 16, 2003

Change the name of your query to get_fruit_info and it should work.

Code:

<CFQUERY NAME=&quot;

Code:

get_fruit_info

Code:

&quot; DATASOURCE=&quot;fruits&quot;>
SELECT * FROM fuitdatabase
WHERE fruit_ID = &quot;#FORM.selected_fruit_id#&quot;
</CFQUERY>

-Carl

Chinyere · Sep 17, 2003

Hi csteinhilber,

I actually did change my query name to get_fruit_info with still the same result.

This is what my code looks like now:

First Page:

Code:

<CFQUERY NAME=&quot;get_fruit&quot; DATASOURCE=&quot;fruits&quot;>
SELECT * FROM fruitdatabase
ORDER BY fruit
</CFQUERY>

<CFFORM ACTION=&quot;details_page.cfm&quot; METHOD=Post>

<CFSELECT name=&quot;fruit&quot; message=&quot;&quot; query=&quot;get_fruit&quot; value=&quot;fruit_ID&quot; display=&quot;fruit&quot; required = &quot;No&quot;>
</CFSELECT>
<BR><BR>
<INPUT TYPE=&quot;Image&quot; Name=&quot;Point&quot; SRC=&quot;images\submit.gif&quot; border=&quot;0&quot;>
</CFFORM>

_______________________________________________________

Second (Action) Page:

Code:

<CFQUERY NAME=&quot;get_fruit_info&quot; DATASOURCE=&quot;fruits&quot;>
SELECT * FROM fruitdatabase
WHERE 'fruit_ID' = '#form.fruit#'
</CFQUERY>


<CFIF get_fruit_info.RecordCount GT 0>

   <TABLE WIDTH=&quot;100%&quot; Cellpadding=&quot;0&quot; Cellspacing=&quot;0&quot; BORDER=&quot;1&quot;>

   <TR>
   <TD colspan=&quot;2&quot;>
      <H2>Fruits ----</H2>
   </TD>
   </TR>

   <CFOUTPUT QUERY=&quot;get_fruit_info&quot;>

     <TR>
     <TD colspan=&quot;2&quot;>
        <H2>----#get_fruit_info.fruit_name#----</H2>
     </TD>
     </TR>

     <TR>
     <TD>
        <B>Number of Calories:</B>    
     </TD>
     <TD>
        #get_fruit_info.number_of_calories#
     </TD>
     </TR>

     <TR>
     <TD>
        <B>Color:</B> 
     </TD> 
     <TD> 
        #get_fruit_info.color#
     </TD>
     </TR>

     <TR>
     <TD>
        <B>Vitamin Content:</B>
     </TD> 
     <TD>
        #get_fruit_info.vitamin_content# 
     </TD>
     </TR>
   </CFOUTPUT>

   </TABLE>

<CFELSE>
   The fruit you selected is not in our database.
</CFIF>

_______________________________________________________

I always end up getting the <CFELSE> result, when I would like to display the respective content in the database. I tried to use the info that you gave me before but maybe something was lost in the translation.

I have like twenty of these drop-downs on the page (since I would like to alphabeticize the fruit). I don't know if this is affecting my results.

BTW, this is just sample code (in theory). But I will need to use something just like it pretty soon. If only I could get it to work...

Thanks for all of your help!

Chinyere [afro2]

ecobb · Sep 17, 2003

<CFQUERY NAME="get_fruit_info" DATASOURCE="fruits">
SELECT * FROM fruitdatabase
WHERE 'fruit_ID' = '#form.fruit#'
</CFQUERY>

I'm not sure if this will help, but you don't need the quotes around 'fruit_ID' in your where clause, and if #form.fruit# is a numeric ID, it doesn't need quotes either.

Hope This Helps!

Ecobb
- I hate computers!

mansii · Sep 17, 2003

Code:

WHERE 'fruit_ID' = '#form.fruit#'
</CFQUERY>

It seems that you did not apply the excellent suggestion above. Try to remove the ' outside fruit_ID.

mansii

Chinyere · Sep 17, 2003

Thanks for the suggestions.

I tried to remove the ' sign and received this error message:

Code:

Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = 22005 (Error in assignment)

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (145:1) to (145:55).

Yes, fruit_ID is numeric but '#form.fruit#' is not.

Does anyone know how to solve this issue?

Thanks.

Chinyere [afro2]

csteinhilber · Sep 17, 2003

Okay... if you're using this on the first page:

Code:

<CFQUERY NAME=&quot;get_fruit&quot; DATASOURCE=&quot;fruits&quot;>
SELECT * FROM fruitdatabase
ORDER BY fruit
</CFQUERY>

<CFFORM ACTION=&quot;details_page.cfm&quot; METHOD=Post>

<CFSELECT name=&quot;fruit&quot; message=&quot;&quot; query=&quot;get_fruit&quot; value=&quot;fruit_ID&quot; display=&quot;fruit&quot; required = &quot;No&quot;>
</CFSELECT>
<BR><BR>
<INPUT TYPE=&quot;Image&quot; Name=&quot;Point&quot; SRC=&quot;images\submit.gif&quot; border=&quot;0&quot;>
</CFFORM>

that means that, on the second page,

Code:

#FORM.fruit#

is going to contain the fruit_id of the selected fruit.

You say that the fruit_id is numeric, then it's always going to be numeric... and

Code:

#FORM.fruit#

is always going to be numeric as well.

So the query on the second/action must do a numeric compare. Thus, the query needs to be:

Code:

<CFQUERY NAME=&quot;get_fruit_info&quot; DATASOURCE=&quot;fruits&quot;>
   SELECT * FROM fruitdatabase
   WHERE fruit_ID = #form.fruit#
</CFQUERY>

-Carl

Chinyere · Sep 17, 2003

Thanks csteinhilber! Thanks everyone!

I have finally solved this issue! I had a eureka moment when I realized that my CFSELECT name and display name where called the same thing (you can see it above). When I changed it, as csteinhilber suggested, it worked! I am so glad [bigsmile]

. Thanks all .

Now, part B, of this issue is that I have 26 different select boxes on the page (one for each letter of the alphabet). Does that mean that I will need 26 different queries? I think that 26 queries would be easier to do than 26 different tables LOL. This is what one of the queries looks like so far:

(This is the query for the fruits that start with the letter A.)

Code:

<CFQUERY NAME=&quot;get_fruit&quot; DATASOURCE=&quot;fruits&quot;>
SELECT * FROM fruitdatabase
WHERE fruit_name LIKE 'A%'
ORDER BY fruit_name
</CFQUERY>

If that is the case, then I will need like 26 different queries. That is definitely doable but is there an easier way to do this?

Thanks again.

Chinyere [afro2]

P.S. Btw, how does one vote for helpful tipsters? I would like to nominate you all!!!

ecobb · Sep 17, 2003

If I may make a suggestion, wouldn't it be easier to have 1 select box with all of the fruits in it listed in alphabetical order?

That way, you could have 1 select box, populated from 1 query, like this:

<CFQUERY NAME="get_fruit" DATASOURCE="fruits">
SELECT fruit_name, fruit_id
FROM fruitdatabase
ORDER BY fruit_name
</CFQUERY>

You will get the same results faster since CF only has to hit the database 1 time instead of 26.

Hope This Helps!

Ecobb
- I hate computers!

csteinhilber · Sep 17, 2003

I don't think it's a question of them being in alphabetical order, Ecobb... but being able to choose one for each letter. Like maybe it's a kids game (or a psych test ;-))

You can certainly do this pretty easily Chinyere.

If you can do it in the SQL statement, that'd be the ultimate solution, I think. But how you do this depends greatly on the database you're using. In Oracle, for example, you would do something like:

Code:

<CFQUERY NAME=&quot;get_fruit&quot; DATASOURCE=&quot;fruits&quot;>
   SELECT fruit_name, fruit_id, SUBSTR(fruit_name,1,1) AS fruit_letter
   FROM fruitdatabase
   ORDER BY fruit_letter, fruit_name
</CFQUERY>

this will dynamically "pick off" the first letter of each fruit's name and "alias" the result as a virtual column (fruit_letter) in your resultset. Then you define the ORDER BY to be by that column.

Then it's a simple matter of using the "group" attribute in your CFOUTPUTs:

Code:

<CFOUTPUT query=&quot;get_fruit&quot; group=&quot;fruit_letter&quot;>
   <select name=&quot;fruitselection&quot;> 
      <option value=&quot;#get_fruit.fruit_letter#&quot;>Select a fruit for letter #get_fruit.fruit_letter#</option>
      <CFOUTPUT group=&quot;fruit_name&quot;>
         <option value=&quot;#get_fruit.fruit_id#&quot;>#fruit#</option>

      </CFOUTPUT>
   </select><br />&nbsp<br />
</CFOUTPUT>

then, on your action page, it'll receive a list of all selected fruit ids as a comma-separated list.
So that

Code:

   <CFOUTPUT>#ListGetAt(FORM.fruitselection,10)#</CFOUTPUT>

would output the selection for the 'J' fruit (the 10th letter in the alphabet) or simply "J" if no selection was made.

-Carl

Chinyere · Sep 18, 2003

Hi csteinhilber,

Thanks for the reply. Do you think that this would work also with an Access database (what I am using for the mock-up) too or with a SQL Server 2000 database (what I will have to use for the real project)?

Hi Ecobb,

I actually do need the 26 different select boxes on the page. Each category (alphabet) contains too many items that I could not possibly fit it into one SELECT box.

They want an alphabeticized index on the top of the page so that users could click down to their SELECT box of choice.

For example:

A B C D E F G ... etc. will appear on the top of the page with the SELECT boxes down below.

Chinyere [afro2]

ecobb · Sep 18, 2003

Gotcha. Yeah, I guess I wasn't thinking about the number of items there could possbily be in the select box. (Duh, there's a lot of fruit out there!) It would definately be better to break it down into seperate boxes if you have a lot of selections.

Hope This Helps!

Ecobb
- I hate computers!

csteinhilber · Sep 18, 2003

First questions first.

I would imagine there's a way to do the same thing using the Access driver, yes. I don't know the exact syntax. SQL Server definitely has an equivilent function, but if memory serves it's called SUBSTRING(), rather than SUBSTR. The rest of the SQL statement would be exactly the same.

As for your interface... it actually sounds like a "cascading select series" would be more benefitial to the user. Where they're presented with just two select boxes, one that lists all the letters A-Z, and the other that lists all the fruits based on the letter that's selected in the first select box. In other words, the user selects "G" from the first select box... and upon that selection, the second list box clears out all previous items and then dynamically populates with items such as "Grapes", "Grapefruit", etc.

You can do this pretty easily using javascript.
First, you have ColdFusion write out the appropriate javascript arrays for each letter. Then you create a javascript function that fires on the first selectbox's onChange event that populates the second selectbox from those arrays.

-Carl

Chinyere · Sep 22, 2003

Thanks again guys! I am still a few posts back in trying what you have suggested. I am now trying the SELECT box script that csteinhilber suggested with the SUBSTRING(), rather than SUBSTR.

I hope that I will be able to call upon you two if I have any other questions regarding this issue (which seem likely [lookaround]

).

Thanks again!

Chinyere [afro2]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Help with CFSELECT in a Drop-Down Menu? 3

Chinyere

Programmer

ecobb

Programmer

csteinhilber

Programmer

Chinyere

Programmer

csteinhilber

Programmer

Chinyere

Programmer

ecobb

Programmer

mansii

Programmer

Chinyere

Programmer

csteinhilber

Programmer

Chinyere

Programmer

ecobb

Programmer

csteinhilber

Programmer

Chinyere

Programmer

ecobb

Programmer

csteinhilber

Programmer

Chinyere

Programmer

Similar threads

Part and Inventory Search

Sponsor