CODEWITHSUNDEEP
phpmysqlfull-text-search
Hkachhia
Hkachhia

Reputation: 4539

How to use Mysql FULLTEXT in multiple field

I have to matching text in many field.Currently I have used Like in query.But Like give too many result.I have read about mysql FULLTEXT query and I have tried to apply in my query.But I don't know its working or not.

Old query:

select * from table where field1 like 'search text' or field2 like 'search text'

Fulltext query :

select * from table where MATCH(field1, field2) AGAINST ('search text' IN NATURAL LANGUAGE MODE )

My question is that,It is possible search in all fields with or condition using Fulltext match query ?

Upvotes: 0

Views: 609

Answers (2)

markus
markus

Reputation: 561

By default, mysql will search in both fields MATCH(field1, field2) like your old query.

All what you need is to create the fulltextindex for both fields.

CREATE FULLTEXT INDEX index_name
    ON tbl_name (field1, field2);

For Example:

SELECT * FROM tbl_name WHERE MATCH(field1, field2) AGAINST ('word1 word2' IN BOOLEAN MODE );

You will find the same like:

SELECT * FROM tbl_name 
WHERE field1 LIKE '%word1%' OR field2 LIKE '%word1%' OR
      field1 LIKE '%word2%' OR field2 LIKE '%word2%'

Upvotes: 1

GautamD31
GautamD31

Reputation: 28763

Try like this

where
name = 'search_key'
OR firstname ='search_key' 
OR lastname ='search_key'

may it will reach your question

Upvotes: 0

Related Questions