asked on

Search in query

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.

James Rodgers

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?

usky1

ASKER

I want to apply it no matter what.

James Rodgers

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>

usky1

ASKER

I am getting an error,
Invalid column name 'a'. I put 'a' in the "filter" field.

James Rodgers

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

usky1

ASKER

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.

ASKER CERTIFIED SOLUTION

James Rodgers

membership

This solution is only available to members.

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

Start Free Trial

usky1

ASKER

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?

James Rodgers

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'

usky1

ASKER

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>

usky1

ASKER

It looks like all is working well now. Thanks for your help.

James Rodgers

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>

??

James Rodgers

glad i could help,

thanks for the points