Repeating Data - Unsure of my use of cfquery

I posted a few days ago about having multiple cfquery statements in the same document. After some troubleshooting, I finally figured out how to do this. I began building my test form, which is populated from fields in a database, I'm running into a problem where the data is just being repeated, to the point where it's making the page extremely slow to load. I have a feeling this is because I didn't use cfquery properly - but I'm not sure how to remedy the situation. The form kept looking over the data more and more as I added new rows querying new data. Any help is much appreciated.

My Code: (link to live page - http://www2.ccboe.com/summeracademy/app/emp/index.cfm) <cfquery name="getSchools" datasource="filemaker_schools"> SELECT SchNum, SchoolBrief FROM SCHOOLS ORDER BY SchoolBrief </cfquery> <cfquery name="getSrastaff" datasource="filemaker_srmastaff"> SELECT ApplySubject, ApplySchool, School, ApplyPosition, YrsPartic, TshirtSize FROM ApplySchoolList, SRAStaff, ApplySubjectGrList, YrsParticList, TshirtSizeList ORDER BY School </cfquery> <form name="form1" method="post" action="/summeracademy/app/app_process.cfm">   <table width="100%" cellpadding="2" cellspacing="2" border="0">            <tr>                    <td colspan="4" bgcolor="dceaf3"><h4>Contact and Payroll Information:</h4></td>                  </tr>            <tr>                    <td valign="top"><b>Applicant Name:</b></td>             <td valign="top">First: <input name="First" type="text"></td>             <td valign="top">Last: <input name="Last" type="text"></td>             <td valign="top">MI: <input name="MI" type="text"></td>                      </tr>                  <tr>                    <td valign="top"><b>Employee ID:</b></td>             <td colspan="3" valign="top"><input name="EmpID" type="text" maxlength="6"></td>                      </tr>                  <tr>                    <td valign="top"><b>Current Regular School:</b></td>             <td colspan="3" valign="top"><select name="CurrentSchool" size="1">                             <option selected="selected" value="0">Select your school</option>                         <cfoutput query="getSchools">                         <option value="#getSchools.SchoolBrief#">#getSchools.SchoolBrief#<br /></option></cfoutput>                          </select></td>                      </tr>                  <tr>                    <td valign="top"><b>Current Grade and/or Subject that you teach:</b></td>             <td colspan="3" valign="top"><input name="CurrentSubjectorGr" type="text"></td>                      </tr>                  <tr>                    <td valign="top"><b>Phone Numbers:</b></td>             <td valign="top">Home: <input name="PhoneHome" type="text"></td>             <td colspan="2" valign="top">Cell: <input name="PhoneCell" type="text"></td>                      </tr>                  <tr>                    <td valign="top"> </td>             <td valign="top">Work: <input name="PhoneWork" type="text"></td>             <td colspan="2" valign="top">IP Phone ext.: <input name="Voicemail" type="text"></td>                      </tr>                  <tr>                    <td valign="top"><b>Address:</b></td>             <td colspan="3" valign="top">Street: <input name="Address" type="text"><br>                     City: <input name="City" type="text"> State: <input name="State" type="text"> Zip: <input name="Zip" type="text"></td>                      </tr>                   <tr>                    <td valign="top"><b>Email:</b></td>             <td colspan="3" valign="top"> <input name="Email" type="text"><br>         Please type this carefully so that we can reach you. There are no spaces in an email address.</td>                      </tr>                  <tr>                    <td colspan="4" bgcolor="dceaf3"><h4>Application Information for Summer Academy 2010:</h4></td>                  </tr>                  <tr>                    <td valign="top"><b>What position are you applying for:</b></td>             <td colspan="3" valign="top"> <select name="ApplyPosition" size="1">                             <option selected="selected" value="0">Select a position</option>                         <cfoutput query="getSrastaff">                         <option value="#getSrastaff.ApplyPosition#">#getSrastaff.ApplyPosition#</option></cfoutput>                          </select></td>                      </tr>                  <tr>                    <td valign="top"><b>What school would you prefer?</b></td>             <td colspan="3" valign="top"> <select name="ApplyPosition" size="1">                             <option selected="selected" value="0">Select a school</option>                         <cfoutput query="getSrastaff">                         <option value="#getSrastaff.School#">#getSrastaff.School#</option></cfoutput>                          </select></td>                      </tr>                  <tr>                    <td valign="top"><b>If you do not get your preferred school, mark where you are willing to work:</b></td>             <td colspan="3" valign="top"> <cfoutput query="getSrastaff"><input name="SchoolApplyOther" type="checkbox" value="#getSrastaff.School#" /> #getSrastaff.School#</cfoutput></td>                      </tr>                  <tr>                    <td valign="top"><b>What subject do you prefer to teach?</b></td>             <td colspan="3" valign="top"> <select name="SubjectGradePreferred" size="1">                             <option selected="selected" value="0">Select a subject</option>                         <cfoutput query="getSrastaff">                         <option value="#getSrastaff.ApplySubject#">#getSrastaff.ApplySubject#</option></cfoutput>                          </select></td>                      </tr>                  <tr>                    <td valign="top"><b>What other subject(s) would you be willing to teach?</b></td>             <td colspan="3" valign="top"> <cfoutput query="getSrastaff"><input name="SubjectGradeOther" type="checkbox" value="#getSrastaff.ApplySubject#" /> #getSrastaff.ApplySubject#</cfoutput></td>                      </tr>          </table> </form>

Your second query is the problem, both the repeating data and the slowness.  You have a full outer join of five tables, which is not what you want.  You need inner joins.  Here is a simple reference that might help you on your way: http://www.sql-tutorial.net/SQL-JOIN.asp Has a million reference to other pages that might help. If you get that query sorted you should be all set. cheers, barneyb ----- Excess quoted text cut - see Original Post for more -----

Looking at the way the data are used, the OP probably needs five queries. The queries are just building options in select inputs. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ On 15 March 2010 22:36, Barney Boisvert <bboisvert@gmail.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

Barney, would that require that the different tables have a linking relationship? I think that's my problem. There is no common field shared among these tables.

Just separate each table into its own query. Use the data from the right table in the right place and the problem is solved. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ On 15 March 2010 22:39, Steven Sprouse <ssprouse@ccboe.com> wrote: > > Barney, would that require that the different tables have a linking > relationship? I think that's my problem. There is no common field shared > among these tables. > >

You guys are awesome. Sometimes it's the easiest things...

I got my original form to output the way I wanted it to. Now, I'm trying to actually process and validate that form and I'm running into more errors. I sort of know what I'm doing wrong, but unclear how to fix it. Here is my error: Invalid tag nesting configuration. A query driven CFOUTPUT tag is nested inside a CFOUTPUT tag that also has a QUERY= attribute. This is not allowed. Nesting these tags implies that you want to use grouped processing. However, only the top-level tag can specify the query that drives the processing. <br>The error occurred on line 299. Is there an easy way for me to send the file of my processing document on this forum?

<cfoutput query="aQuery"> <cfoutput query="bQuery">      .... </cfoutput> </cfoutput> That is illegal because ColdFusion does some automatic variable scoping that would just not work in such a situation.  To do this you have to use the <cfloop query...> form. I.E. <cfoutput> <cfloop query="aQuery"> <cfloop query="bQuery">      .... </cfloop> </cfloop> </cfoutput> Just realize with this from CF is not going to automatically scope the query column names for you and you will have to keep track of the rows.   You output will look something like this. #aQuery.aColumn[aQuery.currentRow]# and #bQuery.bColumn[bQuery.currentRow]#

Just so I'm clear, are you saying that I need to change my #aQuery.aColumn[aQuery.currentRow]# formatting within the form? For example, I need to change this: <cfoutput query="getSchools">                         <option value="#getSchools.SchoolBrief#">#getSchools.SchoolBrief#<br /></option></cfoutput>                          </cfselect> to this: <cfloop query="getSchools">                         <option value="#getSchools.SchoolBrief[getSchools.currentRow]#">#getSchools.SchoolBrief[getSchools.currentRow]#<br /></option></cfloop>                          </cfselect> If so, what is the currentRow value? Just a little confused there. _______________________________________________ ----- Excess quoted text cut - see Original Post for more -----

On 3/16/2010 8:22 AM, Steven Sprouse wrote: > If so, what is the currentRow value? Just a little confused there. currentRow is one of the values provided by ColdFusion about its query objects, just like columnList and recordCount.  It is simple the current row of the record set that is being processed in the current iteration of a query loop, either <cfoutput query...> or <cfloop query...> loops.   You can use it any time you want to know what iteration you are on. I.E a way to alternate row colors: <cfiif aQuery.currentRow MOD 2>#darkBackground#</cfif> You can normally ignore the need to keep track of the current row when you are looping over a single record set inside of a loop.  But once you nest two or more separate query loops inside each other, ColdFusion can no longer know for certain which iteration of which record set do you want to out put at a given point.  It falls on you to explicitly tell ColdFusion which iteration to use.  I.E. the aQuery.currentRow.

You can put a <CFLoop query="foo"> inside of a <cfoutput query="moo"> tag. I got my original form to output the way I wanted it to. Now, I'm trying to actually process and validate that form and I'm running into more errors. I sort of know what I'm doing wrong, but unclear how to fix it. Here is my error: Invalid tag nesting configuration. A query driven CFOUTPUT tag is nested inside a CFOUTPUT tag that also has a QUERY= attribute. This is not allowed. Nesting these tags implies that you want to use grouped processing. However, only the top-level tag can specify the query that drives the processing. <br>The error occurred on line 299. Is there an easy way for me to send the file of my processing document on this forum?