Problem with Database Search

[ifritshorn]

Hi,

I am trying to repair a website's directory search but can't pinpoint the
problem(s). Basically, when I input a person's full name, the search yields no
results. If I input a single name, results appear, which is fine. Also,
another problem is the keyword search. It doesn't work at all. Can someone
help me to figure this out? Here is the code for the query:

<!-- display search results -->
<CFQUERY NAME="GetMatches" DATASOURCE="#application.dsn#">
SELECT *
FROM MemberData
WHERE 1=1
<CFIF Country NEQ "All">
AND Country = '#Country#'
</CFIF>
<CFIF State NEQ "All">
AND State = '#State#'
</CFIF>
<CFIF form.Searchname NEQ "">
AND (Firstname LIKE '%#form.searchname#%' OR Lastname LIKE
'%#form.searchname#%' OR Company LIKE '%#form.searchname#%')
</CFIF>
<CFIF form.searchKeywords NEQ "">
AND CONTAINS (MemberData.*, '#form.searchKeywords#')
</CFIF>
ORDER BY LastName
</CFQUERY>

Here is a link to the website as well:

[url]http://www.pngdealers.com/public/dealerDirectory.cfm[/url]

Thanks,
Christine

[Chugglethwaite]

Hi There, The way the query is set up it searches the firstname and surname
fields separately. So if you entered a person's full name it wouldn't match
either of these fields and hence no records were returned. You could try
modifying the bit that searches the name and company to the following,
assuming that someone would enter a space between the irst and last names.
Cheers Andy

<CFIF form.Searchname NEQ "">
AND (Firstname LIKE '%#form.searchname#%' OR Lastname LIKE
'%#form.searchname#%' OR Firstname + ' ' + Lastname LIKE '%#form.searchname#%'
OR Company LIKE '%#form.searchname#%')
</CFIF>