Checkbox to update dropdown list with coldfusion query

What I'd like to do is create a dropdown list with a coldfusion query using checkboxes.

For example...i have a query that displays some options as checkboxes at the top of the page.  Group 1, Group 2, Group 3, Group 4, Group 5, etc. (I think right now there are 7 selections)...so for this, there will be seven checkboxes at the top of the page.

If none of them are checked, nothing gets populated in the drop down...if one or more are checked, then data from another table is populated into that drop down, and if the checkbox is unchecked, it removes those items from that dropdown.  So let's say, Group 2 is checked...so in the drop down, it would display Items associated with Group 2 only.  In another example, let's say Group 2 and Group 5 and Group 7 were checked...so in the drop down, it would display items associated with Groups 2, 5, and 7...and look something like:

Select
=========
--Group 2--
Item 1
Item 2
etc etc
--Group 5--
Item 1
Item 2
etc etc
--Group 7--
Item 1
Item 2
etc etc

Any help would be much appreciated.

Thanks
<cfoutput query="getGroups">
<cfinput type="Checkbox" name="frmGroup" value="#ID#">
</cfoutput>

<cfselect query="getItems" name="frmItem" display="strItemName" value="intItemID"></cfselect>

Open in new window

Lee R Liddick JrReporting AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

azadisaryevCommented:
do you want to refresh the select with or without page reload?

Azadi
0
azadisaryevCommented:
on a second thought - why don't you just skip the checkboxes and have 7 <select>s instead?
(it's possible to do it with checkboxes + dynamic select, but 7 separate <select>s will sure be easier...)

Azadi
0
Lee R Liddick JrReporting AnalystAuthor Commented:
I would rather not do a page reload on the select...and the business wants the check boxes...I had already suggested doing the multiple select boxes.  I have to stick with the check boxes.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

_agx_Commented:
How much data, and how frequently does it change? If it's small and relatively static,  a simple option is to run a query to get all of the data when the page loads.  Convert it to js object, and use javascript to refresh the list in the onChange event of the checkboxes.  If it's larger or volatile, you'd be better off with the CF8 ajax functionality.
0
Lee R Liddick JrReporting AnalystAuthor Commented:
The list of items is around 400 total. They don't change much. The most in any one group might be close to a 100.
0
azadisaryevCommented:
>> The list of items is around 400 total

with that many items, i would strongly suggest you DO NOT use a <select> drop-down to display them.
it would be rather unusable...

consider instead implementing a tabbed layout where each 'group' is a separate tab, with group's items displayed as checkboxes in a multi-column layout. a user would click on a group's tab to see its items, and click on items to select them. a lot more user-friendly that a huge <select> list :)

this can be easily achieved with jquery+jqueryUI or even with cf's <cflayout> and <cflayoutarea> tags and a bit of css magic...

but if you are set on using a <select>, then attached is a 'proof of concept' code.
you will notice that it does not create grouped options in the <select> - that's because, unfortunately, cfc-binded cfselects do not support grouping of options into <optgroup>s. so if you must have grouping, you will have to code the whole thing yourself using javascript...
cfc-binded selects aslo do not easily support pre-selection of items - so if you need that also (i.e. if you want to keep previously selected items on adding/removing a 'group'), you will have to code that yourself, too...

i'll be happy to help you in either case - with a tabbed layout (strongly suggested) or regular <select>

Azadi
<!--- test.cfm page --->
<!--- 
first we'll create a query with some data to display in cfselect, and store it as session var. ignore this - it is just for this example.
to run this example, make sure session management is enabled in your application.
--->
<cfset session.data = querynew('id,name,g', 'integer,varchar,varchar')>
<cfloop from="1" to="12" index="i">
	<cfset queryaddrow(session.data)>
	<cfset querysetcell(session.data, 'id', 1)>
	<cfset querysetcell(session.data, 'name', monthasstring(i))>
	<cfif i lte 3>
		<cfset q = 1>
	<cfelseif i lte 6>
		<cfset q = 2>
	<cfelseif i lte 9>
		<cfset q = 3>
	<cfelse>
		<cfset q = 4>
	</cfif>
	<cfset querysetcell(session.data, 'g', 'Group ' & q)>
</cfloop>

<!--- the form --->
<cfform name="f1">
<!--- create the 'group' checkboxes --->
<cfoutput>
  <cfloop from="1" to="4" index="x"><input type="checkbox" name="frmGroup" value="Group #x#" />Group #x#&nbsp;&nbsp;&nbsp;</cfloop>
</cfoutput>
<br><br>
<!--- and this is our cfc-binded cfselect --->
<cfselect name="s1" bind="cfc:test.getData({frmGroup})" bindonload="no" display="name" value="id" />
</cfform>


<!--- test.cfc - save in same folder as test.cfm --->
<cfcomponent>
  <cffunction name="getData" access="remote" returntype="query">
    <cfargument name="groups" type="any" required="no" default="">
    <cfset var myResult = "">
    <cfquery name="myResult" dbtype="query">
    SELECT id, name, g
    FROM session.data
    WHERE 1=0
    <cfif len(trim(arguments.groups))>OR g IN (<cfqueryparam cfsqltype="cf_sql_varchar" list="yes" value="#trim(arguments.groups)#">)</cfif>
    ORDER BY g, name
    </cfquery>
    <cfreturn myResult>
  </cffunction>
</cfcomponent>

Open in new window

0
_agx_Commented:
Yeah,  400 is a bit too much for a select list.  If you _have to_ go with the list, at least make it > size 1.

> then attached is a 'proof of concept' code.

   I had tried that same thing, but noticed some slight issues (CF8).  For some reason it took two clicks
   to expand the list of items right after it is refreshed. Did you notice anything like that.
0
_agx_Commented:
> it took two clicks to expand the list

Oh, I think I see the problem. It's probably being called onChange or something.  Calling it on click makes it work as
expected:

<cfselect name="s1" bind="cfc:test.getData({frmGroup@click})" bindonload="no" display="name" value="id" />

0
azadisaryevCommented:
>> For some reason it took two clicks to expand the list of items right after it is refreshed.
>> Did you notice anything like that.

just tested it in cf8, and no, didn;t notice anything like it in FF - select expanded nicely with just one click...
could be browser-specific issue, perhaps?

Azadi
0
_agx_Commented:
Yes, it seems like an IE issue.  FF works, but in IE the events don't seem to fire correctly unless you use {frmGroup@click}.  The list doesn't even populate until after the second box is checked.
0
azadisaryevCommented:
and here's a proof-of-concept for a tabbed layout using <cflayout>
much nicer than a <select>! :)

Azadi
<!--- create query of data to display and save as session var --->
<cfset session.data2 = querynew('id,name,g', 'integer,varchar,varchar')>
<cfloop from="2007" to="2010" index="y">
  <cfloop from="1" to="12" index="i">
    <cfset queryaddrow(session.data2)>
    <cfset querysetcell(session.data2, 'id', y & i)>
    <cfset querysetcell(session.data2, 'name', monthasstring(i) & ' ' & y)>
    <cfset querysetcell(session.data2, 'g', 'Group ' & (y-2006))>
  </cfloop>
</cfloop>
<cfquery name="session.data3" dbtype="query">
SELECT *
FROM session.data
ORDER BY g, id
</cfquery>

<!--- the form --->
<cfform name="f1">
<cfset itemcolumns = 5><!--- number of columns of items to display in each tab --->
<cflayout type="tab" name="l1">
  <cfoutput query="session.data3" group="g"><!--- output query, grouping it by 'g' column --->
    <cfset itemcounter = 0><!--- init group's item counter --->
    <!--- calculate how many items to display per column --->
    <cfset arrItems = []>
    <cfoutput><cfset arrayappend(arrItems, id)></cfoutput>
    <cfset itemspercol = int(arraylen(arrItems)/itemcolumns)>
    <cflayoutarea name="#rereplace(g, '[\W]', '-', 'all')#" title="#g#">
      <div style="width:#int(100/itemcolumns)-5#%; margin:0; margin-right:1%; float:left; padding:0 0 20px 0; display:inline">
      <cfoutput><!--- nested cfoutput to display group's items --->
        <cfset itemcounter++><!--- increment group's item counter --->
        <input type="checkbox" name="items" value="#id#">#name#<br>
        <cfif itemcounter lt arraylen(arrItems) AND itemcounter mod itemspercol eq 0></div><div style="width:#int(100/itemcolumns)-5#%; margin:0; margin-right:1%; float:left; padding:0 0 20px 0; display:inline"></cfif>
      </cfoutput>
      </div>
    </cflayoutarea>
  </cfoutput>
</cflayout>
</cfform>

Open in new window

0
azadisaryevCommented:
>> Yes, it seems like an IE issue.  FF works, but in IE the events don't seem to fire correctly unless you use {frmGroup@click}.  The list doesn't even populate until after the second box is checked.

damn IE! good to know. thanks!

Azadi
0
_agx_Commented:
> much nicer than a <select>! :)

   Now that IS nice! Very sharp.
0
Lee R Liddick JrReporting AnalystAuthor Commented:
Before I continue to read and look at the examples to start, the total of the table is around 400 items; however, the drop down never has all 400 items in it.  The most I can see right now is 101 after running some queries on the tables.  I will look at the examples, etc. but I am a little restricted to what the business wants.  Although I provide them examples, they don't always accept them.  :)
0
azadisaryevCommented:
even with 101 items the <select> will be very hard to use.

check the examples out, show them to the client. make sure you explain to them (with an example, preferably) how ridiculous it will be for the users to work with a <select> containing 100+ items. if they still insist on checkboxes+select, i can help you code it.

Azadi
0
Lee R Liddick JrReporting AnalystAuthor Commented:
The first example wasn't working for me.  The page comes up but if click a box, nothing populates in the drop down.
The second example I don't think is going to work...this is going to be too much data on a form that is already very long and the items are not for multiple selections.  You can select multiple groups, but you can only select one item.
I think I may need to do the select box after the checkboxes.  The drop down will at least not contain 400 items as it does now.
0
Lee R Liddick JrReporting AnalystAuthor Commented:
In your tabbed example, I can't get it to work with my query.  My query and the form is shown below.
I get the following error:
Division by zero. Division by zero is not allowed.   The error occurred in E:\Inetpub\wwwroot\nationalops\eventNotif\tabbedtest.cfm: line 53
51 : <cfset itemcounter++><!--- increment group's item counter ---> 52 : <input type="checkbox" name="items" value="#intSystemID#">#strSystemName#<br> 53 : <cfif itemcounter lt arraylen(arrItems) AND itemcounter mod itemspercol eq 0></div><div style="width:#int(100/itemcolumns)-5#%; margin:0; margin-right:1%; float:left; padding:0 0 20px 0; display:inline"></cfif> 54 : </cfoutput> 55 : </div>  

<cfquery datasource="MYDNS" name="getSystems"
SELECT 		sys.intSystemId, sys.strSystemName, sys.strSystemDesc, sys.BU_ID, bun.Business_Unit, bun.Business_Unit + ': ' + sys.strSystemName AS FullName
FROM 	SYSTEMTABLE sys
		inner join BUSINESSUNITTABLE bun on sys.BU_ID = bun.BU_ID
WHERE	sys.intSystemID = sys.intSystemID
             AND	bitActive = 1
ORDER BY sys.BU_ID, sys.strSystemName
</cfquery>


<!--- the form ---> 
<cfform name="f1"> 
<cfset itemcolumns = 5><!--- number of columns of items to display in each tab ---> 
<cflayout type="tab" name="l1"> 
  <cfoutput query="qrySelSystems" group="BU_ID"><!--- output query, grouping it by 'g' column ---> 
    <cfset itemcounter = 0><!--- init group's item counter ---> 
    <!--- calculate how many items to display per column ---> 
    <cfset arrItems = []> 
    <cfoutput><cfset arrayappend(arrItems, intSystemID)></cfoutput> 
    <cfset itemspercol = int(arraylen(arrItems)/itemcolumns)> 
    <cflayoutarea name="#rereplace(BU_ID, '[\W]', '-', 'all')#" title="#Business_Unit#"> 
      <div style="width:#int(100/itemcolumns)-5#%; margin:0; margin-right:1%; float:left; padding:0 0 20px 0; display:inline"> 
      <cfoutput><!--- nested cfoutput to display group's items ---> 
        <cfset itemcounter++><!--- increment group's item counter ---> 
        <input type="checkbox" name="items" value="#intSystemID#">#strSystemName#<br> 
        <cfif itemcounter lt arraylen(arrItems) AND itemcounter mod itemspercol eq 0></div><div style="width:#int(100/itemcolumns)-5#%; margin:0; margin-right:1%; float:left; padding:0 0 20px 0; display:inline"></cfif> 
      </cfoutput> 
      </div> 
    </cflayoutarea> 
  </cfoutput> 
</cflayout> 
</cfform>

Open in new window

0
azadisaryevCommented:
which columns of your query define a 'group' and which defined a group's 'items'?

it looks like you got it all mixed up which query columns to output where...

if you can post a screenshot of cfdump of your query it may help figure it out.

Azadi
0
Lee R Liddick JrReporting AnalystAuthor Commented:
Okay, trying to just what it looks like.  Will need to try and figure out how to put this in the checkbox to dropdown though.
0
Lee R Liddick JrReporting AnalystAuthor Commented:
The group is the Business_Unit or BU_ID.  Then all the systems associated with that Business Unit are the groups 'items'.
 
 
0
azadisaryevCommented:
right.

try this:

change this line:

<cfset itemspercol = int(arraylen(arrItems)/itemcolumns)>

to this:

<cfset itemspercol = ceiling(arraylen(arrItems)/itemcolumns)>

[if arraylen(arrItems) is less than itemcolumns, then int(arraylen(arrItems)/itemcolumns) would return 0. but ceiling(arraylen(arrItems)/itemcolumns) will instead round it up to 1.]

Azadi
0
Lee R Liddick JrReporting AnalystAuthor Commented:
Okay...that's what it was.  Never heard of ceiling.  Learn something new every day!
Now I might be able to utilize this type of setup in another area that I am working on; however, it's not going to satisfy my requirement on the form.  
How can I get these items to show up in a drop down after clicking on the business unit checkbox?
0
azadisaryevCommented:
i am working on it now.
but it is 2:22 am here in hk, so will likely post tomorrow...

Azadi
0
Lee R Liddick JrReporting AnalystAuthor Commented:
Hahahahahaha!  I keep getting an error invoking the cfc.  Go to bed!
0
azadisaryevCommented:
ok, here's what i came up with so far (see attached code).

the code uses jquery js library - download from http://jquery.com if you do not already use it.
make sure you change the path to jquery-1.4.2.min.js to correct one in the <script> tag

the sample code uses cf's built-in cfartgallery datasource - make sure you have it set up in cf admnistrator!

the code assumes that test2.cfc is in the same dir as the cfm page.

i am sure you will have questions about the code, but they will have to wait until tomorrow - i am dead tired...

Azadi
<!--- the cfm page --->
<script type="text/javascript" src="/js/jquery-1.4.2.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
	$("input.gbox").click(function(){
		var elSelect = $("#s1");
		elSelect.empty();
		$.ajax({
			url: 'test2.cfc?method=getData&returnformat=json',
			data: $("input.gbox").serialize(),
			dataType: 'json',
			success: function(res) {
				for (var i=0; i<res.length; i++) {
					var og = $('<optgroup label="' + res[i].GROUPNAME + '"></optgroup>');
					for (var j=0; j<res[i].ITEMIDS.length; j++) {
						og.append('<option value="' + res[i].ITEMIDS[j] + '">' + res[i].ITEMNAMES[j] + '</option>');
					}
					elSelect.append(og);
				}
			}
		});
	});
});
</script>

<!--- query to create checkboxes --->
<cfquery name="q1" datasource="cfartgallery">
SELECT DISTINCT a.artistid, a.firstname || ' ' || a.lastname AS aname
FROM artists a INNER JOIN art ON a.artistid = art.artistid
ORDER BY artistid
</cfquery>

<cfform name="f1">
<cfoutput query="q1">
	<label><input class="gbox" type="checkbox" name="frmGroup" value="#artistid#">#aname#</label>&nbsp;&nbsp;&nbsp;
</cfoutput>
<br>
<br>
<select id="s1" size="30" style="width:200px;"></select>
</cfform>


<!--- test2.cfc --->
<cfcomponent>
  <cffunction name="getData" access="remote" returntype="array" output="no">
    <cfargument name="frmGroup" type="any" required="no" default="">
    <cfset var myResult = arrayNew(1)>
    <cfset var q = "">
    <cfset var s = "">
    <cfquery name="q" datasource="cfartgallery">
    SELECT a.artistid, a.firstname || ' ' || a.lastname AS aname, a2.artid, a2.artname
    FROM artists a INNER JOIN art a2 ON a.artistid = a2.artistid
    WHERE a.artistid IN (<cfqueryparam cfsqltype="cf_sql_integer" list="yes" value="#trim(arguments.frmGroup)#">)
    ORDER BY a.artistid, a2.artid
    </cfquery>
    <cfoutput query="q" group="artistid">
      <cfset s = structNew()>
      <cfset s.groupname = aname>
      <cfset s.itemids = arraynew(1)>
      <cfset s.itemnames = arraynew(1)>
      <cfoutput>
        <cfset arrayappend(s.itemids, artid)>
        <cfset arrayappend(s.itemnames, artname)>
      </cfoutput>
      <cfset arrayappend(myResult, duplicate(s))>
    </cfoutput>
    <cfreturn myResult>
  </cffunction>
</cfcomponent>

Open in new window

0
Lee R Liddick JrReporting AnalystAuthor Commented:
unfortunately I don't have the luxury of having access to the CFAdmin to make sure the built in datasource artgallery is setup.
So I tried getting this to work with my queries; but it doesn't populate anything in the select.  I thought I had everything matched up correctly.
Did I need to do anything to the jquery####.js file?

<!--- the test2.cfm page --->
<script type="text/javascript" src="/javascripts/jquery-1.4.2.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
	$("input.gbox").click(function(){
		var elSelect = $("#s1");
		elSelect.empty();
		$.ajax({
			url: 'test2.cfc?method=getData&returnformat=json',
			data: $("input.gbox").serialize(),
			dataType: 'json',
			success: function(res) {
				for (var i=0; i<res.length; i++) {
					var og = $('<optgroup label="' + res[i].GROUPNAME + '"></optgroup>');
					for (var j=0; j<res[i].ITEMIDS.length; j++) {
						og.append('<option value="' + res[i].ITEMIDS[j] + '">' + res[i].ITEMNAMES[j] + '</option>');
					}
					elSelect.append(og);
				}
			}
		});
	});
});
</script>

<!--- query to create checkboxes --->
<cfquery datasource="MYDNS" name="q1">
SELECT 		BU_ID, Business_Unit
FROM		BUTABLE
</cfquery> 

<cfform name="f1">
<cfoutput query="q1">
	<label><input class="gbox" type="checkbox" name="frmGroup" value="#BU_ID#">#Business_Unit#</label>&nbsp;&nbsp;&nbsp;
</cfoutput>
<br>
<br>
<select id="s1" size="30" style="width:200px;"></select>
</cfform>



<!--- the test2.cfc page --->
<cfcomponent>
  <cffunction name="getData" access="remote" returntype="array" output="no">
    <cfargument name="frmGroup" type="any" required="no" default="">
    <cfset var myResult = arrayNew(1)>
    <cfset var q = "">
    <cfset var s = "">
<cfquery datasource="MYDNS" name="q">
SELECT 		sys.intSystemId, sys.strSystemName, sys.BU_ID, bun.Business_Unit, bun.Business_Unit + ': ' + sys.strSystemName AS FullName
FROM 		SYSTEMTABLE sys
				inner join BUTABLE bun on sys.BU_ID = bun.BU_ID
WHERE		sys.intSystemID = sys.intSystemID
AND			bitActive = 1
ORDER BY sys.BU_ID, sys.strSystemName
</cfquery>

    <cfoutput query="q" group="BU_ID">
      <cfset s = structNew()>
      <cfset s.groupname = Business_Unit>
      <cfset s.itemids = arraynew(1)>
      <cfset s.itemnames = arraynew(1)>
      <cfoutput>
        <cfset arrayappend(s.itemids, intSystemId)>
        <cfset arrayappend(s.itemnames, FullName)>
      </cfoutput>
      <cfset arrayappend(myResult, duplicate(s))>
    </cfoutput>
    <cfreturn myResult>
  </cffunction>
</cfcomponent>

Open in new window

0
azadisaryevCommented:
can you make this test page web-accessible so i can check if/what errors are thrown?

Azadi
0
Lee R Liddick JrReporting AnalystAuthor Commented:
unfortunately i can't...im working with an intranet.  I'm trying to play with it to find out where I am screwing up.  
But it's not throwing any error on the page or on the browser bottom bar.
0
azadisaryevCommented:
>>unfortunately i can't
that is unfortunate.

check that your query works correctly: run it in a cfm page and cfdump it to see if it returns correct/any data.

use firefox+firebug to debug any js errors, including ajax call to cfc and cfc response.

Azadi
0
Lee R Liddick JrReporting AnalystAuthor Commented:
I'll download the stuff...never used it before.
0
Lee R Liddick JrReporting AnalystAuthor Commented:
I can't figure that firebug out or how to see the errors.  is there anything in the js code on the test.cfm page that needs changed or is it just the queries and page items?
0
Lee R Liddick JrReporting AnalystAuthor Commented:
Since I can't see the example working because of not having access to the CFAdmin, we are sure that this example code works, correct?  I'm assuming yes...but I can't figure out why this wouldn't be working with my queries.  
All the cfdumps on the queries work.  I even copied it all over again and started over.  It still doesn't work and it doesn't give me any errors to go off of.
0
azadisaryevCommented:
>> I can't figure that firebug out or how to see the errors

js errors would be shown in the 'console' tab.
you should also check the 'net' tab to make sure there are no 'file not found' errors for any of the scripts loaded in the page.

'console' tab will also show the request and response data of the ajax calls - check the call's response data to make sure your cfc returns expected json data and not a cf error.

>> we are sure that this example code works, correct?

works fine for me, yes.

>> I can't figure out why this wouldn't be working with my queries

it's really hard to say without being able to see your page in action...

one other thing i would suggest to try:

create a cfm page that invokes your cfc method using <cfinvoke> and run it to make sure your cfc does not throw some error.

i will create anothe example that uses session vars instead of built-in cf datasource so you can run it.

Azadi
0
azadisaryevCommented:
attached is an example that uses session vars instead of built-in cfartgallery dsn.

save the cfc as test3.cfc in the same folder as cfm page.
edit the path to jquery js script.

Azadi
<!--- CFM PAGE --->
<cfif structkeyexists(url, 'reset')>
	<cfset structdelete(session, 'qQuarters')>
</cfif>
<cfif NOT structkeyexists(session, 'qQuarters')>
	<cfset session.qQuarters = querynew('qId,qName', 'integer,varchar')>
	<cfloop from="1" to="4" index="i">
		<cfset queryaddrow(session.qQuarters)>
		<cfset querysetcell(session.qQuarters, 'qId', i)>
		<cfset querysetcell(session.qQuarters, 'qName', 'Quarter ' & i)>
	</cfloop>
	<cfset session.qMonths = querynew('monthId,mName,qId', 'integer,varchar,integer')>
	<cfloop query="session.qQuarters">
		<cfloop to="#session.qQuarters.qId * 3#" from="#(session.qQuarters.qId * 3) - 2#" index="x">
			<cfset queryaddrow(session.qMonths)>
			<cfset querysetcell(session.qMonths, 'monthId', x)>
			<cfset querysetcell(session.qMonths, 'mName', monthasstring(x))>
			<cfset querysetcell(session.qMonths, 'qId', session.qQuarters.qId)>
		</cfloop>	
	</cfloop>
</cfif>
<script type="text/javascript" src="/js/jquery-1.4.2.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
	$("input.gbox").click(function(){
		var elSelect = $("#s1");
		elSelect.empty();
		$.ajax({
			url: 'test3.cfc?method=getData&returnformat=json',
			data: $("input.gbox").serialize(),
			dataType: 'json',
			success: function(res) {
				for (var i=0; i<res.length; i++) {
					var og = $('<optgroup label="' + res[i].GROUPNAME + '"></optgroup>');
					for (var j=0; j<res[i].ITEMIDS.length; j++) {
						og.append('<option value="' + res[i].ITEMIDS[j] + '">' + res[i].ITEMNAMES[j] + '</option>');
					}
					elSelect.append(og);
				}
			}
		});
	});
});
</script>
<cfform name="f1">
<cfoutput query="session.qQuarters">
	<label><input class="gbox" type="checkbox" name="frmGroup" value="#qId#">#qName#</label>&nbsp;&nbsp;&nbsp;
</cfoutput>
<br>
<br>
<select id="s1" size="30" style="width:200px;"></select>
</cfform>


<!--- TEST3.CFC --->
<cfcomponent>
	<cffunction name="getData" access="remote" returntype="array" output="no">
		<cfargument name="frmGroup" type="any" required="no" default="">
		<cfset var myResult = arrayNew(1)>
		<cfset var q = "">
		<cfset var s = "">
		<cfset var t1 = session.qQuarters>
		<cfset var t2 = session.qMonths>
		<cfquery name="q" dbtype="query">
		SELECT t1.qId, t1.qName, t2.monthId, t2.mName
		FROM t1, t2
		WHERE t1.qId = t2.qId AND t1.qId IN (<cfqueryparam cfsqltype="cf_sql_integer" list="yes" value="#trim(arguments.frmGroup)#">)
		ORDER BY qId, monthId
		</cfquery>
		<cfoutput query="q" group="qId">
			<cfset s = structNew()>
			<cfset s.groupname = qName>
			<cfset s.itemids = arraynew(1)>
			<cfset s.itemnames = arraynew(1)>
			<cfoutput>
				<cfset arrayappend(s.itemids, monthId)>
				<cfset arrayappend(s.itemnames, mName)>
			</cfoutput>
			<cfset arrayappend(myResult, duplicate(s))>
		</cfoutput>
		<cfreturn myResult>
	</cffunction>
</cfcomponent>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee R Liddick JrReporting AnalystAuthor Commented:
Even your new example, I get the same result.  No errors, nothing appears in the select.
0
azadisaryevCommented:
>> No errors, nothing appears in the select.

there must be errors then, but you just do not see them.

firebug is indispensable in debugging such issues:

- what is output in firebug's 'console' tab (if any) after you select a checkbox?
- does firebug's 'net' tab report any problems with loading scripts?

also, not related to firebug:

- do you have sessions enabled in cf admin / your application?
- does your Application.cfm/cfc output anything (like page headers, etc)?
- do you have secureJSON enabled in cf admin / your application? what is secure json prefix if you have it enabled?

Azadi
0
Lee R Liddick JrReporting AnalystAuthor Commented:
I am not seeing any errors n the 'console' or 'net' tabs in firebug.  The output in the 'console' tab shows all the items.  It just doesn't put them in the select box.
I have asked our IT department to find out if even the secureJSON is enabled.  I'm guessing that it probably isn't since I'm not seeing any errors on the screen or in this firebug.
0
azadisaryevCommented:
if in the 'console' tab you click on the little + icon next to the ajax request url (the request to test3.cfc), and then view the 'Response' tab in the expanded request info, what is shown there? can you post a copy of the data displayed there?

Azadi
0
Lee R Liddick JrReporting AnalystAuthor Commented:
I get the following (in the code area) for test3.
>> also, not related to firebug:
>> - do you have sessions enabled in cf admin / your application?    ***Yes sessions are enabled.
>> - does your Application.cfm/cfc output anything (like page headers, etc)?       ***It does output page header
>> - do you have secureJSON enabled in cf admin / your application? what is secure json prefix if you have it enabled?     ***still waiting on an answer as I don't have access to our CFAdmin

//[{"ITEMNAMES":["January","February","March"],"ITEMIDS":[1,2,3],"GROUPNAME":"Quarter 1"}]</div>

Open in new window

0
azadisaryevCommented:
>> //[{"ITEMNAMES":["January","February","March"],"ITEMIDS":[1,2,3],"GROUPNAME":"Quarter 1"}]</div>

- is that ALL the data that was there, or just a part of it?
- you DO have secureJSON enabled (the // in front of the line). can it be turned off for this app?
- some code somewhere in your application (in Application.cfm or in onRequestEnd() method of Application.cfc) also seems to add </div> at the end of the line. can this be changed?

Azadi
0
azadisaryevCommented:
>> //[{"ITEMNAMES":["January","February","March"],"ITEMIDS":[1,2,3],"GROUPNAME":"Quarter 1"}]</div>
if the above is all the data returned by ajax call to the cfc, and if you can't disable securejson, and you can't edit your application.cfc/cfm to not output anything, then change the $.ajax() call in my previous example to this:

$.ajax({
  url: 'test3.cfc?method=getData&returnformat=json',
  data: $("input.gbox").serialize(),
  success: function(r) {
    var res = eval(r.replace(/^(?:[^\[]*)(.+}\])(.*)$/, '$1'));//strip non-json data and convert to json object
    for (var i=0; i<res.length; i++) {
      var og = $('<optgroup label="' + res[i].GROUPNAME + '"></optgroup>');
      for (var j=0; j<res[i].ITEMIDS.length; j++) {
        og.append('<option value="' + res[i].ITEMIDS[j] + '">' + res[i].ITEMNAMES[j] + '</option>');
      }
      elSelect.append(og);
    }
  }
});

the above will only work if your secure json prefix is // - if it ever changes to something different, then you will need to edit the regex.

Azadi
0
Lee R Liddick JrReporting AnalystAuthor Commented:
I changed the ajax call to what you have above but now get a syntax error in the firebug but it says 686 out of range 609...
And I can't change application.cfm to not output my header and maybe that is where the </div> is coming from.
0
azadisaryevCommented:
>> get a syntax error in the firebug but it says 686 out of range 609

do you get this with the test code i posted that uses in-session query, or is this error when you run your code/queries?

>> And I can't change application.cfm to not output my header

but you CAN exclude requests to .cfc files from having the header appended to them. something like:

<cfif NOT listlast(cgi.script_name, ".") eq "cfc">
  <!--- code to add page header here --->
</cfif>

and it's rather strange how only </div> is added to the returned cfc data, but not the reast of header code... are you sure you do not just have a stray </div> in your code in Application.cfm?

Azadi
0
Lee R Liddick JrReporting AnalystAuthor Commented:
I did the code to exclude the header from the cfc files but for some reason I am still getting the </div> </div> </body> </html>  at the bottom of the page.
It's the test query that I'm using...says syntax error 705 out of range 615
 

//[{"ITEMNAMES":["January","February","March"],"ITEMIDS":[1,2,3],"GROUPNAME":"Quarter 1"}]</div>
</div>
</body>
</html>

Open in new window

0
Lee R Liddick JrReporting AnalystAuthor Commented:
I foud the file that was doing it...not sure what is calling that file though, but in any case.  I got those trailing </div> stuff out.
Now the error shows:
 res is undefined:
                  for (var i=0; i<res.length; i++) {  
0
azadisaryevCommented:
did you change the js as per my comment #32176137 above? the one that uses a regex to strip non-json data from cfc return?

if you did, then after the line
var res = eval(r.replace(/^(?:[^\[]*)(.+}\])(.*)$/, '$1'));

add this line:
console.log(res);

now run the test page, click a checkbox and copy the output from firebug console and post it here.

Azadi
0
Lee R Liddick JrReporting AnalystAuthor Commented:
Yes I did use the new stuff above...some things are just not easy!  Ugh.  
Clicked on Quarter 1:
res is undefined
for (var i=0; i<res.length; i++)  {
The 'Console' 'Response' data is below:


//[{"ITEMNAMES":["January","February","March"],"ITEMIDS":[1,2,3],"GROUPNAME":"Quarter 1"}]

Open in new window

0
azadisaryevCommented:
thanks...

i think i need to tweak the regex a bit... though it did work in my tests, in your case it does not seem to remove the // in front of json data, and also leaves an empty second line (though this may be just the way you copied/pasted the output)

will post updated regex soon.

meanwhile, if you can edit your Application.cfm file, try adding securejson="false" to <cfapplication> tag and see if the test page works then.

Azadi
0
Lee R Liddick JrReporting AnalystAuthor Commented:
STOP!  I did the change to the application page...and your example works now!  I'm going to try and tweak it with my queries now and see what happens.
0
azadisaryevCommented:
great!

i will still tweak my regex - it seems to be failing if data returned from cfc has multiple lines in it - so that you can use the code even when you have securejson enabled and some output from Application.cfm/cfc or other place appended to responses.

Azadi
0
Lee R Liddick JrReporting AnalystAuthor Commented:
Okay...working with my queries now but for some reason on mine, it is displaying the first system on the 'group name'.
For Example
Group 1 Item 1
 
0
Lee R Liddick JrReporting AnalystAuthor Commented:
Sorry, I hit submit before finishing.  The example is now:
Group 1 Item 1                          (this shows as the group name)
             Item 1
             Item 2
         
0
Lee R Liddick JrReporting AnalystAuthor Commented:
Forget all of that, I found what I did wrong there.  My fault.  Why doesn't this allow you to just delete your 'stupid' comments.  LOL.
Dude, this is awesome!  I wish I could just pay you with something for all of this help!  I love this site and love dealing with guys like you!
0
azadisaryevCommented:
i am really happy i could help!

and here's a new regex. this one works properly (at least in my tests) with single- and multi-line cfc responses, bith with securejson enabled and disabled. just replace the corresponding line in code posted previously.

var res = eval(r.replace(/^[^\[]*(.+}\])(.|\s)*/m, '$1'));

glad we have tackled this!
happy coding!

Azadi
0
Lee R Liddick JrReporting AnalystAuthor Commented:
I did that replacement.  I'm getting an error in IE of
'console' is undefined
 
0
Lee R Liddick JrReporting AnalystAuthor Commented:
Sorry, incomplete statement again.  I'm hitting something wrong.  In any case, I just removed that line from the code and I don't get that errror in IE now.  What was that line of ajax code for?
0
azadisaryevCommented:
just remove the line console.log(res); - this was for debugging in firebug only. since IE does not have firebug, that line throws an error.

Azadi
0
Lee R Liddick JrReporting AnalystAuthor Commented:
That's what I did...thank you again so much!  You are amazing at what you do!  Not sure what you do for a living, but kudos for that company to have you.
0
Lee R Liddick JrReporting AnalystAuthor Commented:
The expert azadisaryev on this question was amazing.  I know this took tons of extra time to figure out what was going on with this to make it work for my environment.  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.