14 Replies Latest reply on Jul 18, 2006 9:50 PM by Newsgroup_User

    Full-Text Search (SQL Help)

    Level 7
      Can anyone help me write the SQL for a Full-Text Search for keywords in a
      database? I have a simple search engine on my site that allows customers to
      include searchable keywords that are stored in a database as 'keyword'.

      The first 2 options of the search area are required and can never be blank.

      The keywords are optional.

      I have this as my SQL statement but it doesn't work if any keywords are
      entered (even if they exist)

      Select company, name, phone, service, city, state, username FROM members
      WHERE service = #arguments.service# AND state = '#arguments.state#'
      <cfif len(trim(form.keywords)) GT 0>
      AND keywords = '#arguments.keywords#'
      </cfif>
      ORDER BY company ASC

      I know it doesn't work and I should probably user LIKE and wildcards, but I
      have searched high and low for the answer and just cannot find it. It is the
      last piece of a client's web site before it can launch and I am going nuts.

      Thanks for any help or code you can give me.

      --
      Wally Kolcz
      Developer / Support


        • 1. Re: Full-Text Search (SQL Help)
          Dan Bracuk Level 5
          Point number 1 - you are using the arguments scope for two variables and form for another. That combination looks suspicious.

          Point Number 2 - This:
          AND keywords = '#arguments.keywords#'
          will work for one word but not two. Look up the listqualify function.

          Point Number 3 - Where are you storing the keywords? Your query only mentions one table.
          • 2. Re: Full-Text Search (SQL Help)
            Level 7
            Point 1:
            I changed the 3rd variable into a argument. Thanks for that tid bit...didn't
            even notice that.

            Point 2:
            Thanks I did not know that won't work. I wll look up the listqualify
            function.

            Point 3:

            I have the keyword in the 'members' table along with service and state.

            Select company, name, phone, service, city, state, username FROM members
            WHERE service = #arguments.service# AND state = '#arguments.state#'
            <cfif len(trim(arguments.keywords)) GT 0>
            AND keywords = '#arguments.keywords#'
            </cfif>
            ORDER BY company ASC


            • 3. Re: Full-Text Search (SQL Help)
              Level 7
              Wow, how do I use the listqualify function to send something to a SQL
              statement?

              Do I first set the Form.keywords to a variables using
              <cfset values = valueList(Form.Keywords, ",")>

              And then send the valueList the function as an argument and then to the SQL?

              I am sorry I am retarded on this, but I am trying to grasp this.


              • 4. Re: Full-Text Search (SQL Help)
                drforbin1970 Level 1
                Wait, are you talking about Full-Text Indexing in SQL Server? That is a whole different ballgame but not difficult to set up.
                • 5. Re: Full-Text Search (SQL Help)
                  Dan Bracuk Level 5
                  Depending on what drforbin1970 says about full text indexing (not my area of expertise), you probably want to set up a one to many relationship between members and keywords. Otherwise, if you are storing word1, word2 in your field, and the user enters word2, word1, things won't work that well.

                  Assuming you have done that, you don't need the list qualify function. You do have to take the user's input and make it a comma delimted list though. Let's say you have done that and the variable is named Keywords. Then, what goes into your query is:

                  and TheKeyWordField in (#ListQualify(keywords, "'")#)
                  • 6. Re: Full-Text Search (SQL Help)
                    Level 7
                    No. I just dont know what to call it. All I need to create the SQL to allow
                    a user to enter one or more keywords and check against a keywords column in
                    a table.

                    This project is now getting way behind because I just cannot find the
                    answer.

                    I wrote this, but, as most of you that know how to do it know, it doesnt
                    work.

                    Select company, name, phone, service, city, state, username FROM members
                    WHERE service = #arguments.service# AND state = '#arguments.state#'
                    <cfif len(trim(arguments.keywords)) GT 0>
                    AND keywords = '#arguments.keywords#'
                    </cfif>
                    ORDER BY company ASC

                    I just need it to work.


                    • 7. Re: Full-Text Search (SQL Help)
                      paross1 Level 2
                      How are your keywords actually stored in the keywords column of your table? Are they in a comma delimited list, or one word per column per row? If they are in a list, then you have a normalization issue with your database, as this is not a good thing to do. Also, do you want your query to return a row if all of the keywords match, or just one or more?

                      Phil
                      • 8. Re: Full-Text Search (SQL Help)
                        Level 7
                        No there is a general 'keywords' column set to LongText and is open to how
                        the member enters it. I would like to have them seperate all keywords with
                        commas.

                        I would like it to query the variable against that column


                        • 9. Re: Full-Text Search (SQL Help)
                          Jwahl
                          Select company, name, phone, service, city, state, username FROM members
                          WHERE service = #arguments.service# AND state = '#arguments.state#'
                          <cfif len(trim(arguments.keywords)) GT 0>
                          AND keywords like '%#arguments.keywords#%'
                          </cfif>
                          ORDER BY company ASC

                          Run the query in a queryAnalyser first to make sure your have results(with out the keywords filter)
                          then add the and keywords like '%#args.keyword#%' and see what results you get back.
                          then dump it into the cf page with any cfifs, hard code a query that should return results.
                          then ad dthe cfif

                          if the query returns records in the QueryAnyliser but not in the code.. then something else is going on.

                          ~J
                          • 10. Re: Full-Text Search (SQL Help)
                            alexeiramone Level 1
                            I use this technique for years.... this is good because it will seek 2 fields (u can remove code to make it search in one field) and it CAN find separate words, like: Search for "Ramones Punk" will math when the contents of a column is "Ramones is the best punk rock band" and, if u want to, will match if DATABASEFIELDNAME1 has "Ramones is good" and DATABASEFIELDNAME2 has "Punk bands are cool".

                            It basically lets coldfusion thinks hes dealing with an ordinary list (space separated) so it can loop trough words. Hope it helps.....
                            • 11. Re: Full-Text Search (SQL Help)
                              Level 7
                              Wally Kolcz wrote:
                              > Can anyone help me write the SQL for a Full-Text Search for keywords in a
                              > database? I have a simple search engine on my site that allows customers to


                              i guess first off, do you actually have full text indexing setup for that table
                              on the "keywords" column? and is it updated? if you do then:

                              SELECT company, name, phone, service, city, state, username
                              FROM members
                              WHERE service = #arguments.service# AND state = '#arguments.state#'
                              <cfif len(trim(arguments.keywords)) GT 0>
                              AND CONTAINS(keywords,'#arguments.keywords#')
                              </cfif>
                              ORDER BY company ASC

                              or if you want "meaning" instead of exact wording:

                              SELECT company, name, phone, service, city, state, username
                              FROM members
                              WHERE service = #arguments.service# AND state = '#arguments.state#'
                              <cfif len(trim(arguments.keywords)) GT 0>
                              AND FREETEXT(keywords,'#arguments.keywords#')
                              </cfif>
                              ORDER BY company ASC

                              you'd use the CONTAINSTABLE or FREETEXTTABLE if you needed to join the results
                              back to something as well as get a "rank" for the search results.

                              if the keywords var is a list then you want to build your WHERE clause a bit
                              differently:

                              - qualify them w/double quotes
                              - separate them by ORs
                              - wrap the whole mess in single quotes ie WHERE CONTAINS(Description, '"sea*" OR
                              "bread*"')

                              your data sounds messed up, i don't think you want to actually store the commas
                              as they imply being part of the text & i guess you mean them to be delimiters to
                              separate values, which won't have any meaning in full-text indexing. but if
                              that's what you actually want, then take paross's advice & move those to another
                              table, one keyword phrase per row.

                              btw this is all in sql server's books-on-line.
                              • 12. Re: Full-Text Search (SQL Help)
                                drforbin1970 Level 1
                                Select company, name, phone, service, city, state, username FROM members
                                WHERE service = #arguments.service# AND state = '#arguments.state#'
                                <cfif len(trim(arguments.keywords)) GT 0>
                                AND keywords LIKE '%#Trim(arguments.keywords)#%'
                                </cfif>
                                ORDER BY company ASC

                                You realize the SQL statement will default to false if no keywords are found. You stated keywords are optional, I'm assuming you still want to return results even if no keywords found? You might want to change the following line to OR:
                                OR keywords LIKE '%#Trim(arguments.keywords)#%'

                                Also, I really hope you are using <CFTRY>/<CFCATCH> and also checking the input for malicious code. Someone could enter SQL code in your search field and compromise your database(s).


                                • 13. Re: Full-Text Search (SQL Help)
                                  Level 7
                                  Ok, this is what I have set up and you get the idea on what I am trying to
                                  accomplish.

                                  I have a table of my database called 'members'

                                  Amongst other items I have columns called:

                                  state: varchar(2)
                                  service: TinyInt
                                  keywords: LongText

                                  When people sign up they choose their service from a list, choose their
                                  state from a list and enter keywords from a textbox which is all inserted
                                  into the table. They can enter it any way they want.

                                  When people go to search, they have to choose a state and a service (not an
                                  option not to). The keywords is optional and is a textbox. They may just put
                                  random words in there or seperate them with comma or such.

                                  I need a SQL code written that will take the state and the service which are
                                  always chosen and add the option of keywords.

                                  I would like this put into a CFC to use on the site.

                                  How much would anyone / everyone charge me to do this for me? I am at my
                                  wits end and don't have the time to keep this request up.

                                  I will probably learn better from seeing it completed then to have people
                                  send me to places.

                                  If you need any other information, please ask.

                                  Listed below is that I have currently. Everything works fine except for the
                                  keywords section. It works if one word is entered, but bombs if you enter
                                  more than one.

                                  <cfcomponent>
                                  <cffunction name="ServiceSearch" access="remote" returntype="query">
                                  <cfargument name="service" type="string" required="true">
                                  <cfargument name="state" type="string" required="true">
                                  <cfargument name="keywords" type="string" required="false">
                                  <cfquery name="ServiceResults" datasource="#Request.MainDSN#">
                                  Select company, name, phone, service, city, state, username FROM members
                                  WHERE service = #arguments.service# AND state = '#arguments.state#'
                                  <cfif len(trim(arguments.keywords)) GT 0>
                                  AND keywords LIKE '%#Trim(arguments.keywords)#%'
                                  </cfif>
                                  ORDER BY company ASC
                                  </cfquery>
                                  <cfreturn ServiceResults>
                                  </cffunction>
                                  </cfcomponent>

                                  Please let me know.


                                  • 14. Re: Full-Text Search (SQL Help)
                                    Level 7
                                    Oh by the way, it is a MySQL 5 database, not MS SQL