mysql full text search page problem...

[shehryarshafiq]

Hi,

I'm having problems with my sql on a search page, if anyone could help i would greatly appreciate it.

I would like to be able to do a fulltext search on all the users registered with my site.

The search page has two text fields for searching.

The first is location, which will match against the following columns (towncity,countystate,employmenthistory)

The second is user, which will match against the following columns (jobrole,mainsubject,employmenthistory,educationhistory,supportingstatement)

Also when a user registers they have the option to hide their details (dropdown, yes or no). If a user has hidden their details i obviously don't want it appearing in the recordset. (column is called showdetails)

On the results page my sql to create the recordset is

SELECT *
FROM tbl_reg_cand
WHERE (MATCH (towncity,countrystate,employmenthistory) AGAINST ('mm_location')) OR (MATCH (jobrole,mainsubject,employmenthistory,educationhistory,supportingstatement) AGAINST ('mm_teacher')) AND (showdetails = 'yes')

variables

mm_location % request.form("location&quot
mm_teacher % request.form("teacher&quot

i added my fulltext index after creating the table using

alter table tbl_reg_users add fulltext (towncity,countrystate,employmenthistory);

and

alter table tbl_reg_users add fulltext (jobrole,mainsubject,employmenthistory,supportingstatement);
Hoever when trying to use this search page, i never get any results. Can anyone see what is wrong with my code? Any help would be most appreciated.

thanks

Shehryar

 

[piti]

hi

i assume, you are not getting any errors, just the recordset is empty, right

matching against so many columns implicates a high probability of reaching the 50% treshold limit - when a fultext search produces more then 50% matching rows, the search ends with an empty result
if you are running version 4.0.1< then try adding IN BOOLEAN MODE to your query (for details look here

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Fulltext_Search

for details)

 

[shehryarshafiq]

Hi,

thanks for the response.

you are right, i am just not getting any results, there are no errors.

my host only supports version 3.23 mysql at the moment, so i can't use boolean.

i thought the problem might be to do with the 50% threshold factor.

i'll just have to split the search up a bit more.

thanks

Shehryar