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

Answer 1

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>