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?

Harrington

start with some AJAX tutorials, they are easy to find. – Carminacarminative

Too bad the question was closed. If the OP is still looking, you want something similar to related selects. The easiest way to do that with coldfusion is by binding your cfinputs to cfc methods. The documentation on cfinput will guide you. – Barbirolli

@DanBracuk. This is another example of people having a wee power trip on S/O (see my blog article about an other recent one: adamcameroncoldfusion.blogspot.co.uk/2012/11/…). This is primarily a ColdFusion question yet none of the "closers" have any history with ColdFusion on this site. What a bunch of losers. This is definitely a question, and a legit question. Dan, suggest you vote to reopen... maybe we can find three other people to vote likewise, and we can get on with answering it. – Halloran

Voting to reopen as this probably can be answered definitively by someone with ColdFusion expertise but the answer isn't easily ascertained by a quick search. In other words, if I wouldn't know where to look next either without asking for help, or finding an (answered) question like this. @DanBracuk - if this is reopened, considered expanding your comment into an answer. – Golda

@AdamCameron - my reputation is only 12% of what is required to vote on closing or re-opening questions. However, having had one of my own closed earlier, I know that it's possible for the OP to add comments to this thread. It would be nice to see a sign of interest from him. I read Adam's blog regarding the wording of the reason for closure. It's the same that was used on mine. I suspect it comes from some sort of pick list. – Barbirolli

@DanBracuk, oh yeah, it's def a generic list of excuses to close a question. The thing is, they don't apply in that case or this one. I am... dealing with this problem (as much as I can) as we speak... Stay tuned. – Halloran

volume one, I have found the answer to your question. I cannot post it properly here because the question is closed (I am also following that up), but the answer is on my blog: adamcameroncoldfusion.blogspot.co.uk/2012/12/…. It also makes some observations about the people who closed your - completely legit - question. Sorry you have had such poor treatment on StackOverflow. – Halloran

Thanks for the answers guys, I am just going through them all. I've a bit slow to respond due to the amount of alcohol consumed over Christmas! – Harrington

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>

Barbirolli

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.

Halloran

Answer 3

Ok, here is what I did which seems to work for me so far. But I am going to review Adam's and Dan's kind answers.

I created a form on my Vehicle.cfm page with all my form elements, except the checkboxes.

I created a new CFM template called vehicleOptions.cfm which accepts a parameter, runs a SQL query, and then outputs the actual HTML to create the checkboxes.

I then went to the area in my form on Vehicle.cfm page where I wanted my checkboxes to appear and typed in this: <cfdiv bind="url:cfincludes/vehicleOptions.cfm?VehicleModel={Model}" />

'Model' is the ID of the element on my form which holds the value for which vehicle a user has selected. So now whenever a user changes the Model in the drop-down list the area updates with different checkboxes each time. Its really wonderful and I didn't have to write a single line of Javascript!

This solution works for what I want, but I am guessing it is not the most elegant use of ColdFusion AJAX? As I said I will review the answers and try to work out a better solution. Thanks for all your help fellow CF devs!

Harrington

There is another way to get your checkboxes which you may or may not like better than what you have. In the cfc method, use cfsavecontent to generate the html content you want in your div. It may or may not be faster than the method you came up with. Compliments on that by the way. – Barbirolli

That sounds like an entirely reasonable approach to me, and much less horsing around than the thing in the docs I pointed you to. – Halloran

Answer 4

It's easy. Each option should have the value encoded in it, e.g.

<select>
<option class="car" name="car" value="volvo">Volvo</option>
<option class="car" name="car" value="honda">Honda</option>
<option class="car" name="car" value="ford">Ford</option>
</select>

Then, you use jQuery.

$('#go').on('click', function() {
    var car = $('.car:selected').val(); 
    // make ajax call here
});​

Here's a working example: http://jsfiddle.net/Yxmjk/. It doesn't demonstrate the AJAX call, but it demonstrates how you would get the data that you include in your AJAX call.

Excurvate