4 Replies Latest reply on Apr 2, 2007 11:33 PM by emanboi

    Search with multiple keywords

    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
        • 1. Re: Search with multiple keywords
          Level 7
          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
          • 2. Search with multiple keywords
            jdeline Level 1
            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.
            • 3. Re: Search with multiple keywords
              Dan Bracuk Level 5
              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.
              • 4. Re: Search with multiple keywords
                emanboi Level 1
                thanks guys i appreciated all ur replies.