11 Replies Latest reply on Sep 14, 2010 12:38 PM by JasonMichalak

    How to get dynamic query results from an array/structure

    JasonMichalak Level 1

      I have an edit page that is set up to display phone number fields from the user stored in our database. The properties for the phone number fields are set by a structure of arrays. My problem is that when a user has more than 1 phone number in my database, my structures correctly show this on the form by displaying 2 phone numbers. The problem I am having is that when it shows multiple phone numebrs, it always shows the first result and just repeats it as opposed to dropping the 2nd or 3rd phone number in their respective fields.

       

       

      array and structure code below:

       

            <!--- Mobile --->
            <cfset mobile = StructNew()>
            <cfset mobile.dynamic = false>
            <cfset mobile.dynamicLabel = '+ Add'>
            <cfset mobile.fields = ArrayNew(1)>
      <cfif #checkuserv.recordcount# GT '0'>   
            <cfset mobile.fields[1] = StructNew()>
            <cfset mobile.fields[1].required = false>
            <cfset mobile.fields[1].label = 'Phone Number 1'>
            <cfset mobile.fields[1].displayIcon = false>
            <cfset mobile.fields[1].voice = true>
            <cfset mobile.fields[1].voiceChecked = true>
            <cfset mobile.fields[1].toolTip = "Please choose if you would like to receive a text or voice call on this number">
      </cfif>
      <cfif #checkuserv.recordcount# IS '2'>    
            <cfset mobile.fields[2] = StructNew()>
            <cfset mobile.fields[2].required = false>
            <cfset mobile.fields[2].label = 'Phone Number 2'>
            <cfset mobile.fields[2].displayIcon = false>
            <cfset mobile.fields[2].toolTip = "Please choose if you would like to receive a text or voice call on this number">
           
            <cfset mobile.fields[2].voice = true>
            <cfset mobile.fields[2].voiceChecked = true>
      </cfif>
      <cfif #checkuserv.recordcount# IS '3'>     
            <cfset mobile.fields[3] = StructNew()>
            <cfset mobile.fields[3].required = false>
            <cfset mobile.fields[3].label = 'Phone Number 3'>
            <cfset mobile.fields[3].displayIcon = false>
            <cfset mobile.fields[3].toolTip = "Please choose if you would like to receive a text or voice call on this number">
            <cfset mobile.fields[3].voice = true>
            <cfset mobile.fields[3].voiceChecked = true>
      </cfif>

       

       

      Here is the code for my fields that call the array info:

       

      <!--- Voice 1 --->      

       

             
              <cfloop index="i" from="1" to="#ArrayLen(mobile.fields)#">
                  <cfif i EQ 1 OR NOT mobile.dynamic OR form.mobileDisplayed GTE i>
                      <cfparam name="form.areacode_#i#" default="">
                      <cfparam name="form.prefix_#i#" default="">
                      <cfparam name="form.suffix_#i#" default="">
                  <div class="fieldBlock phoneBlock" id="phoneBlock#i#">
                      <label for="areacode_#i#">
                          <cfif mobile.fields[i].required><span>*</span></cfif>
                          <cfif mobile.fields[i].displayIcon><img src="/images/sm_phone.jpg" /></cfif>
                          #mobile.fields[i].label#:
                      </label>
                      <div class="inputBlock">
                          <input type="text" maxlength="3" onKeyUp="numTyped(this, 'prefix_#i#', 3, event)" name="areacode_#i#" id="areacode_#i#" class="areacode" value="#trim(left(checkuserv.sub_user_number, '3'))#" />
                          <input type="text" maxlength="3" onKeyUp="numTyped(this, 'suffix_#i#', 3, event)" name="prefix_#i#" id="prefix_#i#" class="prefix" value="#trim(mid(checkuserv.sub_user_number, "4", '3'))#" />
                          <input type="text" maxlength="4" name="suffix_#i#" id="suffix_#i#" class="suffix"  value="#trim(mid(checkuserv.sub_user_number, "7", '4'))#" />
                          <cfif StructKeyExists(mobile.fields[i], "voice") and mobile.fields[i].voice>
                              <div class="voice" id="voice#i#">
                                  <input type="radio" value="0" name="voice_#i#"<cfif Not StructKeyExists(mobile.fields[i], "voiceChecked") or Not mobile.fields[i].voiceChecked> checked="checked"</cfif> />
                                  <label>Text</label>
                                  <input type="radio" value="1" name="voice_#i#"<cfif StructKeyExists(mobile.fields[i], "voiceChecked") and mobile.fields[i].voiceChecked> checked="checked"</cfif> />
                                  <label>Voice</label>
                              </div>
                          </cfif>
                          <cfif StructKeyExists(mobile.fields[i], "toolTip") and mobile.fields[i].toolTip neq "">
                              <div class="toolTip" id="toolTip_#i#" title="#mobile.fields[i].toolTip#" onClick="alert('#mobile.fields[i].toolTip#')">?</div>
                          </cfif>
                      </div>
      <!--- This number was invalid or if geocoding failed, and they've picked a carrier to override, display the carrier override dropdown--->
                      <cfif ListFindNoCase(invalidMobileIndexList, i) or ( showMap and IsDefined("form.carrierOverride" & i) )>
                          <div id="carrierOverrideBox#i#" class="carrierOverrideBlock">
                              <label>Carrier:</label>
                              <div class="inputBlock">
                                  <select name="carrierOverride#i#" id="carrierOverride#i#">
                                      <option value="-1">-- Pick your carrier --</option>
                                      <cfloop query="carriers">
                                          <!--- 1111 is voice, 0 is NONE, don't display  --->
                                          <cfif Not ListFindNoCase("0,1111", carriers.carrier_id)>
                                              <option value="#Trim(carriers.carrier_id)#"<cfif IsDefined("form.carrierOverride" & i) and form["carrierOverride" & i] eq Trim(carriers.carrier_id)> selected="selected"</cfif>>#Trim(carriers.carrier_title)#</option>
                                          </cfif>
                                      </cfloop>
                                  </select>
                                  <a href="http://www.inspironlogisticscontact.cfm?account_id=#account_id#&carrierOverride=1"
                                         title="Carrier help"
                                         onClick="window.open('http://www.inspironlogisticscontact.cfm?account_id=#account_id#&carrierOverride=1','#accou nt_id#','width=500,height=800,scrollbars=no,screenX=100,screenY=100,top=100,left=100,resiz able=1'); return false;"
                                        >?</a>
                              </div>
                          </div>
                      </cfif>
                      <cfif mobile.dynamic AND i EQ form.mobileDisplayed AND i LT ArrayLen(mobile.fields)>
                      <div class="dynamicAddBlock dynamicAddMobileBlock" id="dynamicAddmobile_#i#">
                            <a href="javascript: submitAddField('mobile')">#mobile.dynamicLabel#</a>
                      </div>
                      </cfif>
                  </div>
                  </cfif>
              </cfloop>
              <cfif mobile.dynamic>
                  <input name="mobileDisplayed" id="mobileDisplayed" value="#form.mobileDisplayed#" type="hidden" />
              </cfif>
              <input name="carrierOverrideActive" id="carrierOverrideActive" value="<cfif carrierOverrideActive>1<cfelse>0</cfif>" type="hidden" />

       

       

      I have been stuck on this for days, finally turning to the forum today with a few different issues. I hate trying to work within the framwork of other peoples code.

       

      I use coldfusion 8
             

        • 1. Re: How to get dynamic query results from an array/structure
          JR "Bob" Dobbs Level 4

          I'd start by making sure that this part "mobile.dynamic OR form.mobileDisplayed GTE i" isn't causing your problem.

          • 2. Re: How to get dynamic query results from an array/structure
            JasonMichalak Level 1

            I dont htink that is the problem. I removed it completly and the page stills runs without error and produces the same problem. It recognizes that there are multiple numbers found for the user, but it just keeps repeating the first number found.

            • 3. Re: How to get dynamic query results from an array/structure
              JR "Bob" Dobbs Level 4

              You'll need to post some more code in order for us to recreate your problem.  For example, what are "checkuserv" and "carriers"?   These objects are not defined in your sample.  You might also post a simpler block of code that can be used to recreate your problem.

              • 4. Re: How to get dynamic query results from an array/structure
                JasonMichalak Level 1

                I understand, this is my big problem. I am woriking with someone elses code, and they are  not around to explain what exactly they are doing.

                I was hoping someone might see it and understand better than I. Carrier information has to do with fields in the databse.

                 

                I am about ready to just pay someone to figure this out for me.

                 

                Here is the querys and the variablesetings.

                 

                <cfquery name="checkuser" datasource="WENS">
                SELECT s.sub_firstname,s.sub_lastname,s.sub_email,s.sub_email2,s.sub_id,p.sub_id,p.sub_user_numb er
                FROM         SUBSCRIPTION AS s, PHONENUMBERS AS p
                WHERE        (s.sub_email2 = '#session.getuser#')
                AND    s.active = 1
                AND    s.sub_id = p.sub_id
                </cfquery>
                <cfquery name="checkuserv" datasource="WENS">
                SELECT s.sub_firstname,s.sub_lastname,s.sub_email2,s.sub_id,p.sub_id,p.sub_user_number
                FROM         SUBSCRIPTION AS s, PHONENUMBERS AS p
                WHERE        (s.sub_email2 = '#session.getuser#')
                AND    s.sub_id = p.sub_id
                AND    s.active = 1
                AND    p.comm_type = 'v'
                </cfquery>
                <cfquery name="checkusert" datasource="WENS">
                SELECT s.sub_firstname,s.sub_lastname,s.sub_email2,s.sub_id,p.sub_id,p.sub_user_number
                FROM         SUBSCRIPTION AS s, PHONENUMBERS AS p
                WHERE        (s.sub_email2 = '#session.getuser#')
                AND    s.sub_id = p.sub_id
                AND    s.active = 1
                AND    p.comm_type = 't'
                </cfquery>

                 


                <!--- If checkuser record count is 0, then user does not exist
                <cfif #checkuser.recordcount# GT '0'>     --->

                 


                <cfparam name="acct_title" default="Naval War College">
                <cfparam name="form.subscribe" default="">
                <cfparam name="URL.unsubscribe" default="">
                <cfparam name="URL.confirm" default="">
                <cfparam name="account_id" default="291">
                <cfparam name="send_email" default="0">
                <cfparam name="group_id" default="3">
                <cfparam name="sub_group_id" default="">
                <cfparam name="form.language" default="english">
                <cfparam name="form.group_id" default="#group_id#">
                <cfparam name="form.sub_group_id" default="#sub_group_id#">
                <cfparam name="form.areacode" default="">
                <cfparam name="form.prefix" default="">
                <cfparam name="form.suffix" default="">
                <cfparam name="form.email" default="">
                <cfparam name="form.fname" default="">
                <cfparam name="form.lname" default="">
                <cfparam name="form.password" default="">
                <cfparam name="URL.ep_id" default="">
                <cfparam name="form.mobileDisplayed" default="1">
                <cfparam name="form.emailDisplayed" default="1">
                <cfinclude template="/functions2.cfm">

                 

                 

                 

                its a debacle.

                • 5. Re: How to get dynamic query results from an array/structure
                  JR "Bob" Dobbs Level 4

                  Start small.  The form code you've posted is some what convoluted.  Comment out the sections that aren't giving you problems and focus only on the troubled area.

                   

                  Can you post some sample data to go with the queries you posted?

                  • 6. Re: How to get dynamic query results from an array/structure
                    JasonMichalak Level 1

                    the data is very simple, first name, lastname, phone number, email address where the users email address = a session variable.

                     

                    example:

                    john  doe   333-333-333  345-555-5555  jdoe@jdoe.com

                     

                    I am not sure why the previous developer did it this way, it seems lke major overkill.

                     

                    My solution originally was to just wrap this section in a <CFOUTPUT query=.....> hoping that is would just run this block of code fer every record result from the query. Unfortunately, every time I have tried this, I get an error telling me that <cfoutput> tags that call on querys cant be nested inside of each other. The problem is, I can't locate any other <cfoutput with a query call. I have scanned through thousands of lines of code, multiple times, I have used find and replace on dreamweaver, and cant locate it. I was wondering if a cfloop that references a query would count, maybe that is the issue. I see a cfloop that calls a query that would be nested inside. But that is the only thing I can come up with to this point.

                    • 7. Re: How to get dynamic query results from an array/structure
                      JR "Bob" Dobbs Level 4

                      I think you're on the right track.  Start fresh with a page containing queries and CFOUTPUT or CFLOOP and add bits of the old code a piece at a time.

                      • 8. Re: How to get dynamic query results from an array/structure
                        JasonMichalak Level 1

                        I broke the chunk of code away from the page and am now getting teh phone numbers in the right spots, but I am still getting a coldfusion error.

                         

                        Element 2 is undefined in a Java object of type class coldfusion.runtime.Array.

                         

                        Here is my code...

                         

                        <cfset invalidMobileIndexList = "">
                        <cfset showMap = false>
                        <cfset carrierOverrideActive = false>
                            
                        <!--- Voice 1 --->       

                              
                                <cfloop index="i" from="1" to="#ArrayLen(mobile.fields)#">
                        <!------>            <cfif i EQ 1 OR NOT mobile.dynamic OR form.mobileDisplayed GTE i>
                                        <cfparam name="form.areacode_#i#" default="">
                                        <cfparam name="form.prefix_#i#" default="">
                                        <cfparam name="form.suffix_#i#" default="">
                                    <div class="fieldBlock phoneBlock" id="phoneBlock#i#">
                                    <label for="areacode_#i#">
                                            <cfif mobile.fields[i].required><span>*</span></cfif>
                                            <cfif mobile.fields[i].displayIcon><img src="/images/sm_phone.jpg" /></cfif>
                                            #mobile.fields[i].label#:
                                        </label>
                                        <cfoutput query="checkuserv" ><div class="inputBlock">
                                            <input type="text" maxlength="3" onKeyUp="numTyped(this, 'prefix_#i#', 3, event)" name="areacode_#i#" id="areacode_#i#" class="areacode" value="#trim(left(checkuserv.sub_user_number, '3'))#" />
                                            <input type="text" maxlength="3" onKeyUp="numTyped(this, 'suffix_#i#', 3, event)" name="prefix_#i#" id="prefix_#i#" class="prefix" value="#trim(mid(checkuserv.sub_user_number, "4", '3'))#" />
                                            <input type="text" maxlength="4" name="suffix_#i#" id="suffix_#i#" class="suffix"  value="#trim(mid(checkuserv.sub_user_number, "7", '4'))#" />
                                            <cfif StructKeyExists(mobile.fields[i], "voice") and mobile.fields[i].voice>
                                                <div class="voice" id="voice#i#">
                                                    <input type="radio" value="0" name="voice_#i#"<cfif Not StructKeyExists(mobile.fields[i], "voiceChecked") or Not mobile.fields[i].voiceChecked> checked="checked"</cfif> />
                                                    <label>Text</label>
                                                    <input type="radio" value="1" name="voice_#i#"<cfif StructKeyExists(mobile.fields[i], "voiceChecked") and mobile.fields[i].voiceChecked> checked="checked"</cfif> />
                                                    <label>Voice</label>
                                               
                                            </cfif></div></cfoutput>
                                            <cfif StructKeyExists(mobile.fields[i], "toolTip") and mobile.fields[i].toolTip neq "">
                                                <div class="toolTip" id="toolTip_#i#" title="#mobile.fields[i].toolTip#" onClick="alert('#mobile.fields[i].toolTip#')">?</div>
                                            </cfif>
                                        </div>
                                        <!--- This number was invalid or if geocoding failed, and they've picked a carrier to override, display the carrier override dropdown--->
                                        <cfif ListFindNoCase(invalidMobileIndexList, i) or ( showMap and IsDefined("form.carrierOverride" & i) )>
                                            <div id="carrierOverrideBox#i#" class="carrierOverrideBlock">
                                                <label>Carrier:</label>
                                                <div class="inputBlock">
                                                    <select name="carrierOverride#i#" id="carrierOverride#i#">
                                                        <option value="-1">-- Pick your carrier --</option>
                                                        <cfloop query="carriers">
                                                            <!--- 1111 is voice, 0 is NONE, don't display  --->
                                                            <cfif Not ListFindNoCase("0,1111", carriers.carrier_id)>
                                                                <option value="#Trim(carriers.carrier_id)#"<cfif IsDefined("form.carrierOverride" & i) and form["carrierOverride" & i] eq Trim(carriers.carrier_id)> selected="selected"</cfif>>#Trim(carriers.carrier_title)#</option>
                                                            </cfif>
                                                        </cfloop>
                                                    </select>
                                                    <a href="http://www.inspironlogistics.com/wens/contact.cfm?account_id=#account_id#&carrierOverride= 1"
                                                           title="Carrier help"
                                                           onClick="window.open('http://www.inspironlogistics.com/wens/contact.cfm?account_id=#account_id#&carrierOverride= 1','#account_id#','width=500,height=800,scrollbars=no,screenX=100,screenY=100,top=100,left =100,resizable=1'); return false;"
                                                          >?</a>
                                                </div>
                                            </div>
                                        </cfif>
                                        <cfif mobile.dynamic AND i EQ form.mobileDisplayed AND i LT ArrayLen(mobile.fields)>
                                        <div class="dynamicAddBlock dynamicAddMobileBlock" id="dynamicAddmobile_#i#">
                                              <a href="javascript: submitAddField('mobile')">#mobile.dynamicLabel#</a>
                                        </div>
                                        </cfif>
                                    </div>
                                    </cfif>
                               
                                <cfif mobile.dynamic>
                                    <input name="mobileDisplayed" id="mobileDisplayed" value="#form.mobileDisplayed#" type="hidden" />
                                </cfif>
                                <input name="carrierOverrideActive" id="carrierOverrideActive" value="<cfif carrierOverrideActive>1<cfelse>0</cfif>" type="hidden" /><!------>
                               
                               </cfloop>

                        • 9. Re: How to get dynamic query results from an array/structure
                          JasonMichalak Level 1

                          OK, I have really trimmed the code down now

                           

                           

                          <cfoutput query="checkuserv" >
                          <div class="inputBlock">
                          <input type="text" maxlength="3" onKeyUp="numTyped(this, 'prefix_#count#', 3, event)" name="areacode_#count#" id="areacode_#count#" class="areacode" value="#trim(left(checkuserv.sub_user_number, '3'))#" />
                          <input type="text" maxlength="3" onKeyUp="numTyped(this, 'suffix_#count#', 3, event)" name="prefix_#count#" id="prefix_#count#" class="prefix" value="#trim(mid(checkuserv.sub_user_number, "4", '3'))#" />
                          <input type="text" maxlength="4" name="suffix_#count#" id="suffix_#count#" class="suffix"  value="#trim(mid(checkuserv.sub_user_number, "7", '4'))#" />
                          </div>   
                          </cfoutput>

                           

                           

                          I am getting the following error:

                           

                          Variable COUNT is undefined.

                           

                          I am hoping count will add a 1, 2 or 3 depending on which record it is. I figure this will replace the number placed there by the array, so no code down the road will break. But it is telling me count is undefined.

                          • 10. Re: How to get dynamic query results from an array/structure
                            ilssac Level 5

                            count is not a automatically generated variable.  YOU would need to define it as something if you wanted to use it.

                             


                            For what you seem to be trying to do, I THINK you want the currentRow value from the query object.

                             

                            I.E.

                             

                            'prefix_#checkuserv.currentRow#'

                            1 person found this helpful
                            • 11. Re: How to get dynamic query results from an array/structure
                              JasonMichalak Level 1

                              Awesome, #checkuserv.currentRow# is money and doing exactly what I need. I have it working now by itself, lets see if I can incorporate it back in.