How would you go about executing a database query based on the value from a form select element?

Question

I am using ColdFusion as my application server and SQL Server for the database. I have a select form element which lists a number of vehicles: Volvo S60, BMW M6, VW Jetta.

Based on what vehicle the user selects, I need my webpage to perform a database query to find out what 'type' of vehicle they selected e.g. SUV, Coupe, Convertible. Depending on what 'type' is returned from the database, the database will return a list of options suitable for that vehicle type. My database tables can do this based on the vehicle drop-down's value so that's all good.

Now then, I want to now list the available options for that vehicle 'type' as a group of checkboxes. Doing this should be a simple case of looping through the database resultset and generating a checkbox for each row.

I want to do this without refreshing the page. How do I dynamically get the value from the drop-down, pass this value to the database, get the result back and then show the appropriate checkboxes?

Answer 1

I mentioned in my earlier comment that the simplest way to do this in ColdFusion was to bind your form elements to cfc methods. A google search on "cfinput bind" will lead to lot's of examples, but since I was asked to provide an answer, I'll show an example I once wrote. It's not exactly what the OP wants, but it shows the general idea. It will populate one text box based on the value of another.

Note that the cfc and cfm files have to be in the same directory.

.cfm file

<!--- When you type a clinic code here: ---->
<div id="clinicCodeInput" class="hidden">
Clinic Code <input name="clinicCode" type="text" />
</div>

<!---- A query result will appear here ---->
<div id="clinicNameFromPatientSatisfaction" class="hidden">
Patient Satisfaction Name <cfinput type="text" 
name="NameOfClinic" 
bind="cfc:PatientSatisfactionClinics.GetClinicName({clinicCode})" 
bindonload="no"> 
</div>

.cfc file

<cffunction name="GetClinicName" access="remote" returntype="string">
<cfargument name="clinicCode" type="string" required="yes">
<cfscript>
var clinicName = QueryNew("a");
var returnString = "No Record for Clinic Code " & arguments.clinicCode & ".";
var clinicCodeAsInt = 0;

if (isNumeric(arguments.clinicCode) 
and round(arguments.clinicCode) is arguments.clinicCode)
clinicCodeAsInt = arguments.clinicCode;
</cfscript>

<cfif clinicCodeAsInt gt 0>
<cfquery name="clinicName" datasource="dw">
select name
from patient_satisfaction_clinic
where clinic_code = 
<cfqueryparam cfsqltype="cf_sql_integer" value="#clinicCodeAsInt#">
</cfquery>

<cfif clinicName.recordcount gt 0>
<cfset returnString = clinicName.name[1]>
</cfif>
</cfif>  <!--- clinicCodeAsInt gt 0 --->

<cfreturn returnString>

</cffunction>

Answer 2

There's an example of what you need to do in the Adobe ColdFusion docs for <cfajaxproxy>, which demonstrate the necessary techniques. This does not do exactly what you want, but it's a matter of changing the mark-up from just plain text to checkboxes to fit your requirement. it's too much code to reproduce here, but the key is that you use <cfajaxproxy> to set up a proxy between the JS on the client side and a CFC on the server side to enable the JS to retrieve data from the server.

Using <cfajaxproxy> negates the need to roll your own JS AJAX treatment, or use JQuery (etc) to do so. That said, doing it by hand is not too difficult... it'd be easy enough to employ the techniques demonstrated in that doc to decouple CF from the front-end code completely (there's a good case for not using CF to do client-side stuff)... it all just boils down to having event handlers listening to the relevant events, and then making some manner of AJAX call back to the server to get the data, then bung it in a <div> or something. In this case CF only rely provides the proxying bit: you still need to do the rest yerself.