[thelist] CFQuery sub search...

rudy r937 at interlog.com
Thu Nov 1 15:43:48 CST 2001


hi susan

> Is it really that simple?

no, it's simpler   ;o)

>  If so, I can keep their original keyword in a hidden field,
> and then use WHERE (MyField LIKE OriginalKeyword)
> AND (MyField LIKE NewKeyword)

hidden field good

re-selecting based on original keywords not so good

bear with me for a sec

jeff's onto something here --

> ...also, how do you handle situations where they want to
> perform a search within these results that came from an
> existing search within.  you see where i'm headed with this?

yup, you could waste precious days coding a really complex syntax
constructor

rather than re-query with the first (and subsequent) logical conditions,
simply restrict the "within these results" search to look at just those
rows which the previous query returned

how?  by remembering their PKs

so if you have

    <cfquery name="a" datasource="db">
       select  thepk, thedata
          from thetable
      where [ condition ]
   </cfquery>

then before you display these results, just stuff the PKs, not the keywords
used to search, into a hidden field

   <input type="hidden" name="thepks"
        value="<cfoutput>#ValueList(a.thepk)#</cfoutput>"

then when the search form is submitted again and you detect that the Search
Within These Results option is checked, you simply stick the PKs into the
search query

   <cfquery name="a" datasource="db">
      select thepk, thedata
          from thetable
       where
       <cfif form.withintheseresults>
              thepk in (#thepks#) and
         </cfif>
               [ condition ]
   </cfquery>

(please excuse possible syntax error in the CFIF -- what it's supposed to
do is check whether the withintheseresults checkbox is, um, checked)

note that when you have a condition like

      thepk IN ( list )

the database optimizer can use the index and the search will fly


the beauty of this is that your user can repeat the "search within" as
often as desired -- it's basically the same code each time -- and
presumably each iteration runs faster

the only limitation i can think of is that there may be a character string
limit on the number of values in the IN list


helps?


rudy








More information about the thelist mailing list