Hi all,

I have a page where a user fills out a service request form. One of the form fields is the description field where they describe the nature and details of their request.

What I would like to do is when they come to the confirmation page after submitting the form to have CF query a tutorials database using keywords in the form.description variable. Then it would list the tutorials that match those keywords.

Does anyone have any idea how I can take two or three sentences from form.description and extract the keywords?

specify that keywords and exact phrases must be separated by commas then in the query, list loop the form value;

-------- cfquery --------

<cfquery name="tutorials" datasource="#variables.datasource#" username="#variables.username#" password="#variables.password#">
select id,field_1,field_2,date_added
from table
where <cfloop index="variables.keyword" list="#form.keywords#">lower(field_1) like "%#lcase(trim(variables.keyword))#%" or </cfloop>
<cfloop index="variables.keyword" list="#form.keywords#">lower(field_2) like "%#lcase(trim(variables.keyword))#%" or </cfloop> id = 0
order by date_added desc
</cfquery>

--------

id = 0: is just to finish the or condition
date_added: is always a good idea to add this field to db records, in this case you will be able to return the tutorials in order of most recent

-------- cfoutput --------

<cfoutput query="tutorials">
name: <a href="tutorials.cfm?id=#tutorials.id#">#tutorials.field_1#</a> date: #tutorials.date_added#<br /><br />
</cfoutput>

--------

then on the tutorials.cfm page just query for the tutorial by the url.id


Hope this helps :)

The problem, however, is they are not performing a search. They are filling out a service request form. In the service request form they submit a technical problem they are experiencing on their computer. What I need to do is analyse the text in the service request and return tutorials that are related to the main topics mentioned in the service request.

For example, if the user wrote:I am having problems installing a new printer on my computer. Can you please come to my office and install this new printer for me?then the submission page will not only email the service request to a service technician, it will also display a link to the tutorial on how to add a printer. If I use your code, it will return tutorials that contain the words "a", "on", "this", etc. That will be virtually every tutorial.

A list loop has the default delimiter as a comma, in the example code I posted the loop will not return "a", "on", "this" unless someone has "a,on,this" separated with commas.

It is just not possible to do what you ask unless you know what keywords you are looking for in advance. This is why I suggested to have a search form and "specify that keywords and exact phrases must be separated by commas".

If you know what words you are looking for ( specific product names, names of company service, printer/s... etc. ) then you can simple do something like:

-------------------- set the list of keywords ---------------

<cfset variables.search_list = "printer,keyboard,monitor">

---------

------------ sql -----------

where <cfloop index="variables.keyword" list="#variables.search_list#"><cfif find(lcase(variables.keyword), lcase(form.description))>lower(field_1) like "%#lcase(trim(variables.keyword))#%" or </cfif></cfloop> id = 0

----------------

The best solution IMO would be to do something like in my first post and add a keywords field to the tutorials db table. Then have two sections to the form one for the search and one for the message, form might look something like this:

Subject: [ select menu of topics ]

If other please use exact phrases and keywords separated by commas. [ input box ]

Message: [ textarea ]


Hope this helps :)

It is just not possible to do what you ask unless you know what keywords you are looking for in advance. That's what I figured.