Using dropdown menu and referencing query results

Question

I am hoping this is an easy one I just don't know how to google the right answer.

I have a dropdown that is populated using a query.

<cfquery name="getFruits" datasource="#application.dsnName#" username="#application.dsnUser#" password="#application.dsnPass#">
SELECT TOP 3 fruit, color, size FROM fruit_table
</cfquery>

<cfselect name="fruits" query="getFruits" display="fruit" value="fruit" selected="#form.fruit#" queryPosition="below" required="yes" >
<option value="">Select Fruit</option>
</cfselect>

Is it possible when a user selects a 'fruit' from the dropdown to reference the 'size' and 'color' both associated with the 'fruit' they chose in another query?

For example:

<cfquery name="getFruits" datasource="#application.dsnName#" username="#application.dsnUser#" password="#application.dsnPass#">
SELECT vegetable FROM vegetable_table WHERE size = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#form.size#"> AND color = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#form.color#">
</cfquery>

Thanks!

This appears to be a simple form submission. However, the fact that your fruits `cfselect` has a selected attribute seems odd. Was there an earlier form submission to get to this fruits page? — Dan Bracuk, Dec 31 '20 at 17:09
@DanBracuk This would make sense if it was a self-posting form. — user12031119, Dec 31 '20 at 17:36
@DanBracuk That is from me just copying code. Submission of this form would reload the page and I wanted to display what was selected before using that attribute. — AShoes, Dec 31 '20 at 17:36
You can use a combination of ColdFusion and Javascript to change parts of a web page based on user input. A google search of `coldfusion refresh div` will lead you to some code examples. — Dan Bracuk, Dec 31 '20 at 19:46

user12031119 · Accepted Answer · 2021-01-05T16:28:55.283

When your form gets submitted, the value form.fruits gets posted without the color and size attributes. So the way you would recode your <cfquery> (which I renamed to "getVegetables") to get those attributes from the selected fruit would be like this.

<cfquery name="getVegetables" datasource="#application.dsnName#" username="#application.dsnUser#" password="#application.dsnPass#">
    SELECT
        vegetable 
    FROM 
        vegetable_table 
    WHERE (size, color) IN
        (SELECT
            size, color 
        FROM 
            fruit_table
        WHERE
            fruit = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#form.fruits#">)
</cfquery>

BTW, I'm not a fan of using <cfselect> or any <cfform> tags, but that wasn't your question. However, I would highly suggest discarding it and refactoring your code.

EDIT (second attempt):

As an alternate answer, if you want to pass the size,color columns concatenated with a comma and aliased as sizeColor from the form instead of the the fruit column from your <cfquery>, then what you can do instead is change the first select statement to

<cfquery name="getFruits" datasource="#application.dsnName#" username="#application.dsnUser#" password="#application.dsnPass#">
SELECT TOP 3 
fruit,
size || ',' || color AS sizeColor 
FROM fruit_table
</cfquery>

then change the value attribute to value="sizeColor" in your <cfselect> which will be the aforementioned comma delimited list of the size and color. So the code would instead be

<cfselect name="fruits" query="getFruits" display="fruit" value="sizeColor" selected="#form.fruit#" queryPosition="below" required="yes" >
<option value="">Select Fruit</option>
</cfselect>

Then you can change the <cfquery> on the posted page to this.

<cfquery name="getVegetables" datasource="#application.dsnName#" username="#application.dsnUser#" password="#application.dsnPass#">
SELECT vegetable FROM vegetable_table 
WHERE 
    size = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#form.fruits.listGetAt(1)#"> AND 
    color = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#form.fruits.listGetAt(2)#">
</cfquery>

Thank you, that is exactly what I envisioned but didn't know how to implement. I am attempting this now but running into an error. When I use the comma delimited value I get an error "Query column invalid or missing..." even though these are two valid columns from the cfquery and work fine if used individually. Any advice? — AShoes, Jan 04 '21 at 13:22
Ah, this comes from my lack of experience with `` which doesn't allow variable names in the `value` field but a string literal of a specific column from your SQL select statement. What you can do instead is create a computed column of size, comma, color concatenated and aliased and use the aliased column e.g. `size || ',' || color sizeColor` (presuming double pipe is your concatenation operator) and use `sizeColor` as your `value` attribute. I'll modify my answer. — user12031119, Jan 05 '21 at 16:20
You are a wise man user1203119, thanks again for your assistance. — AShoes, Jan 06 '21 at 14:53

Using dropdown menu and referencing query results

1 Answers1