asked on

what am i doing wrong in this code?

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')>

       
            <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>



             

      

            <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>

            

            <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...

adonis1976

ASKER

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

James Rodgers

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>

adonis1976

ASKER

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).

anandkp

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 !

ASKER CERTIFIED SOLUTION

hart

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hart

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

James Rodgers

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?

adonis1976

ASKER

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.

hart

:-)

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