Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Display Query Results by Dynamic Drop Down Selects

Posted on 2012-08-13
10
Medium Priority
?
1,366 Views
Last Modified: 2012-09-26
I'm using jquery to populate some drop down boxes.  The user selects an option from the first select (facility) and gives them options in the second select (names of a survey).  In the second select, it has the value that will trigger what data is displayed from the query.  Is there a way to do this in jquery as well or another way so not to do a page refresh.

<!--- set my variables --->
<cfscript>
 if (IsDefined("form.fldAction")){
         varAction = form.fldAction;
         varSvyID = form.svy_id;}
 else {
         varAction = "";
         varSvyID = 0;}
</cfscript>

<!--- this is my query --->
<cfquery>
select *
from myTable
where svy_id = #varSvyID#
</cfquery>

<!--- this is my form on page1.cfm --->
<cfform>
<table>
    <tr>
        <td>Select Facility:</td>
        <td><cfselect name="fac_id"
                bind="cfc:myCFC.getFacility()"
                bindonload="true" /></td>
        <td>Select Survey:</td>
        <td><cfselect name="svy_id"
                bind="cfc:myCFC.getSurvey({fac_id})" /></td>
    </tr>
&#9;<tr>
    &#9;<td colspan="4" style="float:right;">
        &#9;<cfinput name="varAction" CLASS="form_button_050" TYPE="button" VALUE="Run">
        </td>
    </tr>
</table>
</cfform>

<!--- then here would be my query results --->
results     results     results    results

Open in new window


On my form button, do I need an onClick command?  Can this be done in jquery?  Or a javascript command?
0
Comment
Question by:Lee R Liddick Jr
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 38290672
you have to use jquery and AJAX.
0
 
LVL 8

Expert Comment

by:soupBoy
ID: 38291802
Will the values of the query be available on the initial page load or are they dependent on the choice of the first select?
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 38291875
they dependent on the choice of the first select ..
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Lee R Liddick Jr
ID: 38292167
The values in the query is dependent on the selection of the choice in the second select.  The succession would go like this:

1.  Page Loads, pulls facilities for 1st Select
2.  User selects facility and jquery populates 2nd select
3.  User selects survey name from 2nd select, hits run.
4.  !!!  On the user submit, the results of the query should run on the page.

The code in my initial example are all on the same page1.cfm.  The query to populate the select boxes from the form is in the CFC that is being called.
0
 
LVL 8

Assisted Solution

by:soupBoy
soupBoy earned 400 total points
ID: 38292491
Here is how I would perform this...

1. Page loads and populates 1st select
2. 'onChange' of 1st select, data is passed to script via ajax
3.  Script performs logic and an returns formated <option> elements
4. On completion of ajax, build the 2nd select and populate with returned <option> elements
5. 'onClick' of run button, data is passed to script via ajax and values are returned.

Here is a basic jsFiddle example....since I have a hard time doing ajax requests within jsFiddle and I don't know coldfusion (.cfm?), I used dummy data but I left the ajax calls in place (but commented out).  Here is the fiddle: Jquery Ajax and dynamic select
0
 

Author Comment

by:Lee R Liddick Jr
ID: 38298544
I like this and it is exactly what I need...but for the life of me I can't get it to even start when I start trying to add my cf queries and all of that stuff to it.  I'm going to have a buddy of mine look at this tonight to see if he can figure it out.
0
 
LVL 53

Accepted Solution

by:
_agx_ earned 1600 total points
ID: 38298862
4.  !!!  On the user submit, the results of the query should run on the page.

Not to dissuade you from using jquery, but ... since you're using CF for everything else couldn't you do #4 with a <cfdiv>?

Here's a working example (requires cf8.02+ I think).  The data's hard coded gibberish .. but gives the general idea.

form.cfm
===========
<script type="text/javascript">
	function showResults() {
               // get the selected id's
		var facilityID = ColdFusion.getElementValue("fac_id");
		var surveyID = ColdFusion.getElementValue("svy_id");
                // refresh the div and display the results
		ColdFusion.navigate('displayResults.cfm?fac_id='+ facilityID +'&svy_id='+surveyID
				, 'resultsDiv');
		
	}
</script>
<cfform>
<table>
    <tr>
        <td>Select Facility:</td>
        <td><cfselect id="fac_id" name="fac_id"
                bind="cfc:myCFC.getFacility()"
				value="fac_id"
				display="fac_name"
                bindonload="true" 
				selected="" /></td>
        <td>Select Survey:</td>
        <td><cfselect id="svy_id" name="svy_id"
                bind="cfc:myCFC.getSurvey({fac_id})" 
				value="svy_id"
				display="svy_name"
				/></td>
    </tr>
<tr>
    <td colspan="4" style="float:right;">
        <cfinput name="varAction" type="button" VALUE="Run" onClick="showResults()">
        </td>
    </tr>
</table>
</cfform>
<!--- create an empty div that will display the results  --->
<cfdiv id="resultsDiv" style="width:90%;">

myCFC.cfc
=============
<cfcomponent>
   <cffunction name="getFacility" access="remote" returnType="query">
	    <!--- simulate query for testing --->
		<cfset var qry = queryNew("")>
		<cfset queryAddColumn(qry, "fac_id", listToArray("1,5,8,16"))>
		<cfset queryAddColumn(qry, "fac_name", listToArray("Facility A,Facility B,Facility R,Facility Z"))>
		<cfreturn qry>
	</cffunction>
	
   <cffunction name="getSurvey" access="remote" returnType="query">
		<cfargument name="fac_id" type="string" />
		
	    <!--- simulate query for testing --->
		<cfset var qry = queryNew("")>
		<cfset queryAddColumn(qry, "fac_id", listToArray("5,5,8,16,16"))>
		<cfset queryAddColumn(qry, "svy_id", listToArray("22,36,92,105,116"))>
		<cfset queryAddColumn(qry, "svy_name", listToArray("ABC,EFG,HIJ,KLM,NOP"))>
		<cfreturn qry>
	</cffunction>

   <cffunction name="getResults" access="remote" returnType="query">
		<cfargument name="fac_id" type="string" />
		<cfargument name="svy_id" type="string" />
		
	    <!--- simulate query for testing --->
	    <cfset var row = "">
		<cfset var qry = queryNew("fac_id,svy_id")>
		<cfloop from="1" to="#RandRange(1,10)#" index="row">
		    <cfset queryAddRow(qry, 1)>
			<cfset qry.fac_id[row] = "arguments.fac_id= "& arguments.fac_id>
			<cfset qry.svy_id[row] = "arguments.svy_id= "& arguments.svy_id>
		</cfloop>
		<cfreturn qry>
	</cffunction>
</cfcomponent>

displayResults.cfm
=====================
<cfparam name="url.fac_id" default="">
<cfparam name="url.svy_id" default="">

<!--- run the query and get the data --->
<cfset myComponent = createObject("component", "myCFC")>
<cfset resultsQuery = myComponent.getResults( fac_id=url.fac_id, svy_id=url.svy_id)>

<!--- display results --->
<!--- note, you could skip this if your CFC method is generating the HTML --->
<cfoutput>
<strong>Results: #resultsQuery.recordCount#</strong><br>
<cfloop query="resultsQuery">
	[#currentRow#] #resultsQuery.fac_id# | #resultsQuery.svy_id#<br>
</cfloop>
</cfoutput>

Open in new window

0
 

Author Comment

by:Lee R Liddick Jr
ID: 38329154
agx...let me try this because I still can't get the other to work properly.  As always, thanks for your input!!!!
0
 

Author Closing Comment

by:Lee R Liddick Jr
ID: 38403163
Sorry for the delay. Finally had a chance to finish this piece up. AGX, that worked and I'm using that. SoupBoy, I provided you some points for responding with an example. Thank you both for taking the time.
0
 
LVL 53

Expert Comment

by:_agx_
ID: 38437969
Glad to hear everything worked out :)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to build a simple, quick and effective accordion menu using just 15 lines of jQuery and 2 css classes
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Suggested Courses
Course of the Month15 days, 18 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question