?
Solved

Search in query

Posted on 2007-10-12
13
Medium Priority
?
184 Views
Last Modified: 2013-12-24
I am trying to do a search from my form on the selected criteria below,
---------------------------------------------------------------------------------------------------------
Filter products by </font>
            <input name="Filter" type="Text" class="textblacknormal" id="Filter" size="10" maxlength="50">
            <FONT size =2 FACE="Arial">in</font>
            <CFSET LOOPCOUNT=1>
            <SELECT NAME="FilterBy" SIZE="2" class="textblacknormal" id="FilterBy">
              <CFLOOP INDEX="Field" List="#ProductFieldList#">
                <CFSET Fielddesc=ListGetat(ProductFieldList,Loopcount)>
                <cfoutput>
                  <OPTION VALUE="#Field#"<CFIF #FIELD# IS "Description">SELECTED</cfif>>#Fielddesc#
                  </cfoutput>
                          <CFSET LOOPCOUNT=LOOPCOUNT+1>
                </cfloop>
              </SELECT>       
            </span>
<cfoutput>
---------------------------------------------------------------------------------------------------------
This is the query on my results page. ********  Specifies the search statement.
-------------------------------------------------------------------------------------------------
<cfquery name="GetProds" datasource="#request.dataSource#">
      SELECT ProdMatrix.ProductID,Cats.Caption,Cats.CategoryID,
            <CFLOOP index="field" list="#editfields#">
                  Products.#Field#,
            </CFLOOP>ProdMatrix.InStock
      FROM Products, ProdMatrix, Cats
      WHERE Products.ProductID = dbo.ProdMatrix.ProductID
            AND      Cats.CategoryID = Products.CategoryID
            <cfif IsDefined("Category_ID")AND Category_ID GT 0>
                  AND Cats.CategoryID = '#form.Category_ID#'
            </cfif>
            <cfif isDefined("action") AND "#action#" EQ "outstock">
                  AND ProdMatrix.InStock = 0
            <cfelseif isDefined("action") AND "#action#" EQ "range">
                  AND ProdMatrix.InStock BETWEEN #LowQty# AND #HighQty#
********     <cfelseif IsDefined("filter") AND #filter# NEQ "">
********             AND #filter# IN (#Fielddesc#)
            </cfif>
      </cfquery>
------------------------------------------------------------------------------------------------------
I am having trouble defining the ************ statements above.
0
Comment
Question by:usky1
  • 7
  • 6
13 Comments
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20066458
so you cant appy a filter if teh item is OOS or it is in the range given ....or you want to be able to apply teh filter no matter what?
0
 

Author Comment

by:usky1
ID: 20066516
I want to apply it no matter what.
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20066606
try this
WHERE Products.ProductID = dbo.ProdMatrix.ProductID
            AND      Cats.CategoryID = Products.CategoryID
            <cfif IsDefined("Category_ID")AND Category_ID GT 0>
                AND Cats.CategoryID = '#form.Category_ID#'
            </cfif>
            <cfif isDefined("action") AND "#action#" EQ "outstock">
                AND ProdMatrix.InStock = 0
            <cfelseif isDefined("action") AND "#action#" EQ "range">
                AND ProdMatrix.InStock BETWEEN #LowQty# AND #HighQty#
                  </cfif>
                <cfif IsDefined("filter") AND #filter# NEQ "">
                       AND #filter# IN (#Fielddesc#)
            </cfif>
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:usky1
ID: 20066668
I am getting an error,
Invalid column name 'a'.  I put 'a' in the "filter" field.
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20066795
if you do not have a column name \ed a in at least one of those tables then you will get an error

#filter # must be a valid column name and fielddesc must be one or more properly identified values, ie quoted if strings or not quoted if numeric, separated by a comma, that is why in that type of syntax i prefer to use cfqueryparam with list set to yes
AND #filter# IN (<cfqueryparam value="#Fielddesc#" list="yes">)
0
 

Author Comment

by:usky1
ID: 20067446
maybe I'm going at this wrong. What I want to do is have #filter# be a text field that you enter your search criteria into. #Fielddesc# is a drop list (that are valid column names) that I want to search through.
0
 
LVL 25

Accepted Solution

by:
James Rodgers earned 1600 total points
ID: 20067551
then you need to loop through fileddesc and create a statement to be appended to the where clause
<cfif IsDefined("filter") AND #filter# NEQ "">

and (
<cfloop list="#fileddesc#" index="idx">
 #idx# = <cfqueryparam value="#filter#">
<cfif idx NEQ listlast(fileddesc)>
or
</cfif>
</cfloop>
)
</cfif>
0
 

Author Comment

by:usky1
ID: 20067816
I used your new query above and I am now getting a Ambiguous column name error.
I am not familiar with this error. Any ideas of what to check?
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20067869
ambiguous colum name is where you are referencing a column, for example ID, teh a column of teh name ID appears in more than 1 table, so you have to specify which table has teh column ID that you are refering to

so ID is ambiguous but tableName.ID is not
in your code ProductID appears in boith products and dbo.ProdMatrix so if teh where clause referes to ProductID  without a table name you will get teh ' Ambiguous column name error'
0
 

Author Comment

by:usky1
ID: 20068146
Thanks for the definition above. I now understand it.

After I corrected that I noticed the proper field was not coming over so I also corrected that,
was-fileddesc, is now-filterby,
Changed #idx#  to  Products.#idx# for the error above.
Changed value="#filter#" to value="%#filter#%" to add wildcards. For example if a search is done on ProductID they can enter 15 and get, 31567, 12154, etc..

The problem now is that no matter what I put in, all the records are returned.

<cfif IsDefined("filter") AND #filter# NEQ "">
                  AND (
                  <cfloop index="idx" list="#filterby#">
                   Products.#idx# LIKE <cfqueryparam value="%#filter#%">
                  <cfif idx NEQ listlast(filterby)>
                  or
                  </cfif>
                  </cfloop>
                  )
                  </cfif>
0
 

Author Comment

by:usky1
ID: 20068752
It looks like all is working well now. Thanks for your help.
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20068813
what was happening here
The problem now is that no matter what I put in, all the records are returned.

<cfif IsDefined("filter") AND #filter# NEQ "">
                  AND (
                  <cfloop index="idx" list="#filterby#">
                   Products.#idx# LIKE <cfqueryparam value="%#filter#%">
                  <cfif idx NEQ listlast(filterby)>
                  or
                  </cfif>
                  </cfloop>
                  )
                  </cfif>

??
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20068820
glad i could help,

thanks for the points
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

850 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