This content has been marked as final. Show 4 replies
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 ">
<cfset keystring = "jobs.job_description LIKE '%#form.searchphrase#%' AND ">
<cfset keystring = "">
<cfif form.job_category gt 0>
<cfset catstring = "job_cats.cat_id = #form.job_category# AND ">
<cfset catstring = "">
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)>
do same for order by clause if you want to sort your results on a
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"
Hopefully you are not new to SQL. The query below has a dynamically built WHERE clause and should give you an idea how to proceed.
Originally posted by: emanboi
Hello I'm new here and I'm also new with coldfusion. I need help because Im building a job search website and I need help on what query or any tips on how to create a multiple keyword search on a table. like on any other jobsites....
e.g from my search form there are categories like keyword, job category, date post, location etc. then not all are required so the query will depend on what categories are selected.
all replies will be very appreciated.
My hint is to start with a normalized database design. If you don't know what that means, there are tutorials on the internet. Google phrases like "normalized database" or "data modelling tutorial" to find them.
To be a little more specific, if you were planning to store your keywords in a single record, like
don't. Your data will become unuseable.
thanks guys i appreciated all ur replies.