Search with multiple keywords

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.

ThanksSearch with multiple keywords
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. %26lt;cfset searchstring = ''SELECT ... FROM
... INNER

JOIN (... INNER JOIN ... ON ...) ON... and so on''%26gt;

you check which form fields have been selected by user and
build a

dynamic WHERE clause statement for your query:

e.g. %26lt;cfset wherestring = ''WHERE ''%26gt;

%26lt;cfif len(trim(form.searchphrase))%26gt;

%26lt;cfset keystring = ''jobs.job_description LIKE
'%#form.searchphrase#%' AND ''%26gt;

%26lt;cfelse%26gt;

%26lt;cfset keystring = ''''%26gt;

%26lt;/cfif%26gt;

%26lt;cfif form.job_category gt 0%26gt;

%26lt;cfset catstring = ''job_cats.cat_id = #form.job_category#
AND ''%26gt;

%26lt;cfelse%26gt;

%26lt;cfset catstring = ''''%26gt;

%26lt;/cfif%26gt;

etc etc...

you trim your dynamic where clause to exclude the last
AND/OR. i.e.

%26lt;cfset wherestring = wherestring %26amp; keystring %26amp;
catstring %26amp; ...%26gt;

%26lt;cfif right(wherestring, 5) eq '' AND ''%26gt;

%26lt;cfset wherestring = left(wherestring, len(wherestring) -
5)%26gt;

%26lt;/cfif%26gt;

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. %26lt;cfset sql_qry =
searchstring %26amp;

wherestring %26amp; orderbystring%26gt;%26lt;cfquery
name=''yourqryname''

datasource=''yourdsn''%26gt;#sql_qry#%26lt;/cfquery%26gt;



--

Azadi Saryev

Sabai-dee.com

Vientiane, Laos


http://www.sabai-dee.com

Search with multiple keywords
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.

quote:

---

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.

Thanks

---

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



userId keywords

2 word1,word2,etc



don't. Your data will become unuseable.


thanks guys i appreciated all ur replies.