[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

what am i doing wrong in this code?

Posted on 2003-11-17
9
Medium Priority
?
281 Views
Last Modified: 2013-12-24
I have a form fields as following:

<select name="Mkt_area">
<option value="" selected>Select a Marketing Area</option>
<option value="All">All Marketing Areas</option>
<option value="Arizona,02,0">Arizona</option>
<option value="Arkansas,03,0">Arkansas</option>
<option value="Central Georgia,10,2">Central Georgia</option>
<option value="East Kentucky,16,2">East Kentucky</option>
<option value="East North Carolina,32,3">East North       
</select>


<select name="Member_AgeRange" size="1" id="Member_AgeRange">
 <option value="" selected>Select an Age Range</option>
 <option value="all_age">All Age Groups</option>
 <option value="no_age">No Age Groups</option>
<option value="25 - 34">25 - 34</option>
    <option value="35 - 44">35 - 44</option>
   <option value="45 - 54">45 - 54</option>
  </select>


<select name="Member_Status" size="1" id="Member_Status">
 <option value="" selected>Select Member Status</option>
  <option value="all_mem">Both</option>
<option value="Member">Member</option>
  <option value="Non-Member">Non-Member</option>
  </select>

then I have this code to process the data..(ofcourse i hav a submit button)


<cfif IsDefined ('form.submit')>
        
        <!--- Mkt_Area Info collection --->
              <cfif #form.Mkt_Area# NEQ "ALL">      
                  <cfset states = #form.Mkt_Area#>
                  <cfset state = #ListGetAt(states,2)#>
                  <cfset div = #ListGetAt(states,3)#>
                  <cfquery name="getzip" datasource="woodmenzip">
                        select ZipCode from ZipCode
                        where State = '#state#' and Division = '#div#'
                  </cfquery>
                  
            <cfelseif #form.Mkt_Area# EQ "ALL">
                  <cfquery name="getzip" datasource="woodmenzip">
                        select ZipCode from ZipCode
                  </cfquery>
            </cfif>
            
            
            
             <!--- Mkt_Area Info collection --->
                  
      <!--- Age Range Info collection --->
            
            <cfif (#form.Member_AgeRange# NEQ "all_age")      OR (#form.Member_AgeRange# NEQ "no_age")>
                  <cfset age = #form.Member_AgeRange#>
                  <cfquery name="getmem" datasource="gchart">
                        select * from GC_members
                        where Member_AgeRange = '#form.Member_AgeRange#' and flag = 0 or flag = 1
                  </cfquery>
            <cfelseif #form.Member_AgeRange# EQ "all_age">
                  <cfquery name="getmem" datasource="gchart">
                        select * from GC_members
                        where flag = 0 or flag 1
                  </cfquery>
            <cfelseif #form.Member_AgeRange# EQ "no_age">
                  <cfquery name="getmem" datasource="gchart">
                        select * from GC_members
                        where Member_AgeRange = "" and flag = 1 or flag = 0
                        
                  </cfquery>
            </cfif>
            
            <!--- Age Range Info collection --->
            
            <cfif #form.Member_Status# NEQ "all_mem">
                  <cfset stat = #form.Member_Status#>
                  <cfquery name="getstat" datasource="gchart">
                        select * from GC_members
                        where Member_Status = '#form.Member_Status#' and flag = 0 or flag = 1
                  </cfquery>
            <cfelseif #form.Member_Status# EQ "all_mem">
                  <cfquery name="getstat" datasource="gchart">
                        select * from GC_members
                        where flag = 0 or flag = 1
                  </cfquery>
            </cfif>
            <cfquery name="getall" dbtype="query">
                  select
                        getmem.Member_FName as Member_FName,
                        getmem.Member_LName as Member_LName,
                        getmem.Member_Add1 as Member_Add1,
                        getmem.Member_City as Member_City,
                        getmem.Member_State      as Member_State,
                        getmem.Member_Zip as Member_Zip,
                        getmem.Member_phone as Member_phone
                  from getzip,getmem,getstat
                  where getzip.ZipCode = getmem.Member_Zip and
                          getmem.Member_Zip = getstat.Member_Zip and
                          getzip.ZipCode = getstat.Member_Zip
            </cfquery>

So what I'm trying to do is to have a bunch of zipcodes from the first query, and then a bunch of members from the second and third query. Compare the zip codes and then display the members who fall in the criteria. I'm not getting any error msgs. I dont know what to do here. Pls help...
0
Comment
Question by:adonis1976
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 11

Author Comment

by:adonis1976
ID: 9766843
i'm trying to output these

<cfoutput query="getall">
              <tr>
            <td>#Member_FName# #Member_LName#</td>
            <td>#Member_Add1#</td>
            <td>#Member_City#</td>
            <td>#Member_State# #Member_Zip#</td>
            <td>#Member_Phone#</td>
          </tr>
              </cfoutput>


thanks in advance
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9767292
try these changes

        <cfquery name="getzip" datasource="woodmenzip">
                    select ZipCode from ZipCode
                              <cfif #form.Mkt_Area# NEQ "ALL">    
                    where State = '#ListGetAt(states,2)#' and Division = '#ListGetAt(states,3)#'
                              </cfif>
               </cfquery>

       
               <cfquery name="getmem" datasource="gchart">
                    select * from GC_members
                             <cfif #form.Member_AgeRange# EQ "no_age">
                                    where Member_AgeRange = ""
                              <cfelseif #form.Member_AgeRange# NEQ "all_age">
                                     where Member_AgeRange = '#form.Member_AgeRange#'
                              </cfif>
               </cfquery>
         

         
             
               <cfquery name="getstat" datasource="gchart">
                    select * from GC_members
                              <cfif #form.Member_Status# NEQ "all_mem">
                    where Member_Status = '#form.Member_Status#'
                              </cfif>
               </cfquery>

          <cfquery name="getall" dbtype="query">
               select
                    getmem.Member_FName as Member_FName,
                    getmem.Member_LName as Member_LName,
                    getmem.Member_Add1 as Member_Add1,
                    getmem.Member_City as Member_City,
                    getmem.Member_State     as Member_State,
                    getmem.Member_Zip as Member_Zip,
                    getmem.Member_phone as Member_phone
                           from getzip left outer join getmem on getzip.ZipCode = getmem.Member_Zip left outer join getzip.ZipCode = getstat.Member_Zip
                           where getzip.ZipCode in (select ZipCode
                                                                   from ZipCode
                                                                   <cfif #form.Mkt_Area# NEQ "ALL">    
                                                         where State = '#ListGetAt(states,2)#' and Division = '#ListGetAt(states,3)#'
                                                                   </cfif>)
                     
          </cfquery>
0
 
LVL 11

Author Comment

by:adonis1976
ID: 9768073
thanks for your input jester..

the code you posted doesnt work...it doesnt work for some condition.. or else i get the error

unknown exception condition

PCodeRuntimeContextImp::executeSQLTagCFQuery::endTag

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (224:11) to (224:48).
0
Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

 
LVL 17

Expert Comment

by:anandkp
ID: 9768984
ok first tell me ...

do all the 3 tables being queried  "getzip,getmem,getstat" have relevant matching records ???

i mean do u want us to help u only with ur last query ??? just asking so we know - where to concentrate !
0
 
LVL 11

Accepted Solution

by:
hart earned 2000 total points
ID: 9769198
why are two datasources used woodmenzip and gchart..
The tables are not in a single database ??

if they are not in the same database then this should do the trick

<CFIF CompareNoCase(form.Mkt_Area,'All') NEQ 0>            
     <CFQUERY name="getzip" datasource="woodmenzip">
      select       ZipCode from ZipCode
      where       State       = '#ListGetAt(form.Mkt_Area,2)#'
            and Division = '#ListGetAt(form.Mkt_Area,3)#'
     </CFQUERY>
<CFELSEIF CompareNoCase(form.Mkt_Area,'All') EQ 0>
     <CFQUERY name="getzip" datasource="woodmenzip">
      select ZipCode from ZipCode
      </CFQUERY>
</CFIF>
<cfquery name="getall" datasource="gchart">
    select * from GC_members
    where (flag = 0 or flag = 1)
      and Member_Zip in (#ValueList(getzip.zipcode)#)
      <CFIF CompareNoCase(form.Member_AgeRange,'all_age') NEQ 0 And CompareNoCase(form.Member_AgeRange,'no_age') NEQ 0>
      and Member_AgeRange = '#form.Member_AgeRange#'
      <CFELSEIF CompareNoCase(form.Member_AgeRange,'no_age') EQ 0>
      and Member_AgeRange = ''
      </CFIF>
      <CFIF CompareNoCase(form.Member_Status,'all_mem') NEQ 0>
      and Member_Status = '#form.Member_Status#'
      </CFIF>      
</cfquery>
------------------------------------------------------------------------------------------------------
else if they are in a single database then a single query should work..

<cfquery name="getall" datasource="gchart">
select       GC.*,ZC.ZipCode
      from       GC_members GC,
                  ZipCode ZC                  
      where       (GC.flag = 0 or GC.flag = 1)                  
            <CFIF CompareNoCase(form.Mkt_Area,'All') NEQ 0>
            and ZC.State = '#ListGetAt(form.Mkt_Area,2)#'
            and ZC.Division = '#ListGetAt(form.Mkt_Area,3)#'      
            </CFIF>
            <CFIF CompareNoCase(form.Member_AgeRange,'all_age') NEQ 0 And CompareNoCase(form.Member_AgeRange,'no_age') NEQ 0>
            and Member_AgeRange = '#form.Member_AgeRange#'
            <CFELSEIF CompareNoCase(form.Member_AgeRange,'no_age') EQ 0>
            and Member_AgeRange = ''
            </CFIF>
            <CFIF CompareNoCase(form.Member_Status,'all_mem') NEQ 0>
            and Member_Status = '#form.Member_Status#'
            </CFIF>
            and GC.Member_Zip = ZC.ZipCode
</cfquery>

Regards
Hart
0
 
LVL 11

Expert Comment

by:hart
ID: 9769215
also i hope all ur fields are of type text/char/varchar etc..

because u had used ' in each and every comparison
and try to use <cfqueryparem, it is a better oding practice

for numeric datatype
<CFQUERYPARAM CFSQLTYPE="cf_sql_numeric" VALUE="#ur variable name#">

for text data type
<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#ur variable name#">

usage for eg:
and Member_Status = <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#form.Member_Status#">


Regards
Hart

0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9770541
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (224:11) to (224:48).


which query is on this line?

0
 
LVL 11

Author Comment

by:adonis1976
ID: 9771085
Hart:

Thanks a million for your help. The first code worked like a champ. Yeah they are in different databases. The DB is messed up 'cos I didn't create it..hehe.. one more question.. when the user selects "ALL"(all marketing areas), the query actually goes through something like 99000 records and the webserver times out... is there a way to optimize this?

thanks for your help again.
0
 
LVL 11

Expert Comment

by:hart
ID: 9776362
:-)

create appropriate indexes on columns so that the retrieval is faster.

also at atime get a max of 200 records.. i don't think u will be showing the complete 99,000 records  at a time to the users.

so keep a limit to show or create a paging functionality.
let the user click next and previous to view records.

also optimize ur database if possible so that the retrieval doesn't take so much time..
also just to let u know in an in statement the maximum number arguments allowed is 1000.
so if there are more than 1000 records[zip codes] then the in clause will give an error.
but then i can't optimize the query because they are in both different databases. :-(

Regards
Hart
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

650 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