Page 1 of 1

[SOLVED] Partial text searches with FULLTEXT?

Posted: Tue Nov 28, 2006 6:14 am
by Skittlewidth
I've made a pretty reliable search script for a client using Fulltext on MySQL 4.1 which searches on practically all the columns in all the tables in his database etc etc, but he's just come back with a complaint::roll:
Can we not just search the whole directory based on the characters entered as above rather than whole words? eg "Rowenta" returns results but not "rowe".
Now back in the day when I used "like" for very very simple searches this was not a problem, but in my limited understanding, fulltext is not designed to work that way. I've read up on the "*" operator but it doesn't seem to work for me on the above example. Is this limited to MySQL 5?

How can I incorporate something like a "like = '$word%'" search without wrecking my Fulltext searching capabilities? I don't want to have to undo my previous hard work!!!

Posted: Tue Nov 28, 2006 6:27 am
by Skittlewidth
Solved my own problem again.... I forgot I needed the Boolean mode modifier as well. Hopefully thats the end of my prob!

Hi Skittlewidth

Posted: Tue Nov 28, 2006 7:35 am
by Jaxolotl
If you solve it, please post the solution so other people doesn't need to make a post for a similar question ;)

Posted: Tue Nov 28, 2006 8:21 am
by Skittlewidth
I did post the solution, I said I needed the * wild card and the Boolean mode modifier.....

But if you really want it made explicit:

Code: Select all

SELECT *, MATCH(company, address, profile, contact_name, postal_town) AGAINST('rowe*' IN BOOLEAN MODE ) 
				AS score 
				FROM directory 
			   WHERE MATCH(company, address, profile, contact_name, postal_town) 
			   	AGAINST('rowe*' IN BOOLEAN MODE ) 
			   ORDER BY score, enhanced DESC;
is the actual query.

Posted: Thu Nov 30, 2006 12:07 am
by dibyendrah
While using boolean mode, I have never used asterix '*' just like you have done 'rowe*'. I have just used operators like '+', '-' etc... I have to test this using '*' operator to see the performance.
Thanks for the information.