Using FullText search on single column

[spook007]

We created a table using following query:
CREATE TABLE TEST(
ID INT UNSIGNED NOT NULL PRIMARY KEY,
NAME VARCHAR(50),
DESCRIPTION TEXT,
FULLTEXT(NAME));

when I run
SELECT * FROM TEST WHERE MATCH(NAME) AGAINST ('APPLE');

I get no results... any suggestions...
(the name field is populated with the word 'apple')

Thanks

 

[KarveR]

Select * from table where name like '%apple%';
for anthing like apple (dapple applepie app apple etc etc)

select * for table where name like app___ ; returns anythin like app + 3 chars

% = anything and any number of it
_ (undersore) = any sigle character.

hope tiiis helps ***************************************
Party on, dudes!

 

[spook007]

KarveR;

I'm avoiding using LIKE statements to retrieve my records... I guess I'm trying to make use of the fulltext searching index. That's why I'm trying to use the MATCH() and AGAINST() commands.

 

[KarveR]

Doh missed your point, you are not telling the wuery where to select FROM,
SELECT * FROM TEST WHERE MATCH(NAME) AGAINST ('APPLE' IN BOOLEAN MODE);

*note : if more than half of your table is populated by this word then the query will return nothing as its too common and cannot be rated correctly because if you have say 200,000 records you don't wanna return over 100,000 of them ina search.
If you test table is only one line, make it 10 and populate 'appl' into like 3 or 4 max. ***************************************
Party on, dudes!

 

[spook007]

KarveR;

Thank you for your advice. The following statement did do the trick. I appreciate it. Thanks!

SELECT * FROM TEST WHERE MATCH(NAME) AGAINST('APPLE' IN BOOLEAN MODE);