Solved

what am i doing wrong in this code?

Posted on 2003-11-17
9
267 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
  • 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 11

Accepted Solution

by:
hart earned 500 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now