[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