How to search multiple columns in MySQL?

Question

I'm trying to make a search feature that will search multiple columns to find a keyword based match. This query:

SELECT title FROM pages LIKE %$query%;

works only for searching one column, I noticed separating column names with commas results in an error. So is it possible to search multiple columns in mysql?

Answer 1

If your table is MyISAM:

SELECT  *
FROM    pages
WHERE   MATCH(title, content) AGAINST ('keyword' IN BOOLEAN MODE)

This will be much faster if you create a FULLTEXT index on your columns:

CREATE FULLTEXT INDEX fx_pages_title_content ON pages (title, content)

, but will work even without the index.

Answer 2

You can use the AND or OR operators, depending on what you want the search to return.

SELECT title FROM pages WHERE my_col LIKE %$param1% AND another_col LIKE %$param2%;

Both clauses have to match for a record to be returned. Alternatively:

SELECT title FROM pages WHERE my_col LIKE %$param1% OR another_col LIKE %$param2%;

If either clause matches then the record will be returned.

For more about what you can do with MySQL SELECT queries, try the documentation.

Answer 3

I don't know what your scope is, but you might consider solr, lucene or some such ...