Full-Text Search (SQL Help)

[Newsgroup_User]

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

[Dan_Bracuk]

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.

[Newsgroup_User]

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

[Newsgroup_User]

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.

[drforbin1970]

Wait, are you talking about Full-Text Indexing in SQL Server? That is a whole different ballgame but not difficult to set up.

[Dan_Bracuk]

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, "'")#)

[Newsgroup_User]

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.

[paross1]

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

[Newsgroup_User]

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

[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

[alexeiramone]

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.....

[Newsgroup_User]

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.

[drforbin1970]

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).

[Newsgroup_User]

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.

[Newsgroup_User]

Oh by the way, it is a MySQL 5 database, not MS SQL