basic concept in a nutshell:
you have a search form with fields for users to select/type
search terms in
the form posts to the action page (serach results page)
on the action page:
you define a basic sql statement that joins all db tables
that could
possible be involved in the search (i.e. tables for all
search criteria
in your form): e.g. <cfset searchstring = "SELECT ... FROM
... INNER
JOIN (... INNER JOIN ... ON ...) ON... and so on">
you check which form fields have been selected by user and
build a
dynamic WHERE clause statement for your query:
e.g. <cfset wherestring = "WHERE ">
<cfif len(trim(form.searchphrase))>
<cfset keystring = "jobs.job_description LIKE
'%#form.searchphrase#%' AND ">
<cfelse>
<cfset keystring = "">
</cfif>
<cfif form.job_category gt 0>
<cfset catstring = "job_cats.cat_id = #form.job_category#
AND ">
<cfelse>
<cfset catstring = "">
</cfif>
etc etc...
you trim your dynamic where clause to exclude the last
AND/OR. i.e.
<cfset wherestring = wherestring & keystring &
catstring & ...>
<cfif right(wherestring, 5) eq " AND ">
<cfset wherestring = left(wherestring, len(wherestring) -
5)>
</cfif>
do same for order by clause if you want to sort your results
on a
particular field
you combine you sql string with your where clause string and
orrder by
clause string and run it a query, e.g. <cfset sql_qry =
searchstring &
wherestring & orderbystring><cfquery
name="yourqryname"
datasource="yourdsn">#sql_qry#</cfquery>
--
Azadi Saryev
Sabai-dee.com
Vientiane, Laos
http://www.sabai-dee.com
4
Replies
AdChoices