How to use full text search with multiple words?

[waqasahmed996]

Hi
Recently i came to know about full text search in Mysql. It is like this

SELECT * FROM tablename WHERE MATCH(name) AGAINST ('john') 
 

it will show results where it find word 'john' in field 'name'.

I have very simple question. Is that possible that it also fetch result where data is like this

johnwaugh
waugh
strauss
Mr john
 

Now i want to fetch result not only Mr john but also johnwaugh. In short only beginning of word should enough and it should give same results of this query

SELECT * FROM tablename WHERE name Like 'john%' 
 

Is that possible in full text search? Or i have to use Like for this?
Please give me some suggestion.

[JKing]

The MATCH AGAINST functions do not accept wildcards. However you could use an OR in your where clause to perform both lookups.

SELECT * FROM tablename WHERE MATCH(name) AGAINST ('john') OR name LIKE '%john%'
 

Correction: Fulltext searches can contain certain wild cards. You must do a search IN BOOLEAN MODE. The wildcard can only be appended to the phrase. This means it will find johnson or johnny but not the_john.

[waqasahmed996]

Thanks a lot for your reply. Yes we use different options in Boolean. Is there anything that i can use 'IN BOOLEAN MODE' which check just word beginning and give same result as

SELECT * FROM tablename WHERE name LIKE 'john%'
 

OR i have to use LIKE

[JKing]

SELECT * FROM tablename WHERE MATCH(name) AGAINST ('john*' IN BOOLEAN MODE)
 

[waqasahmed996]

Yes thats what i am asking. Thanks a lottt. You really help me a lot. Thanks again