FullText Search on NULL or Empty String

[9727604]

Hi Guys,

I'm pretty new to MySql and I have a question regarding FullText Searching. I've implemented a search that searches for jobs based on keywords. I have a table called Jobs with columns such as JobTitle, Job Description, AdditionalInformation etc and I have created a full text index on these columns.

I am trying to now implement a search, basically when a user types keywords into an ASP.Net form text box then the results will be returned so I have something like this in my SQL

MATCH (j.JobTitle, j.JobDescription, j.AdditionalInformation) AGAINST (keywords IN BOOLEAN MODE)

keywords is a variable passed in to the stored procedure which contains a space delimited string of the keywords entered.

This works fine when I enter keywords in the box but if no keywords are entered I want all jobs returned but I get nothing. It seems that AGAINST doesn't work for NULL or Empty String values. Am I missing some operator to tell the fulltext search to accept nulls?

Thanks in advance.

 

[towerbase]

My understanding is that FULLTEXT searches ignore certain common words such as "the" and also words less than four characters in length. So a zero length word wouldn't match any records.

I guess your ASP code needs to determine that if no keywords are entered into the form then it SELECTs all the records otherwise it uses the MATCH clause.

It might be friendly to inform your user that any short words will not be used in the search.

Andrew
Hampshire, UK