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 it is just for searching then you may be able to use CONCATENATE_WS. This would allow wild card searching. There may be performance issues depending on the size of the table.

SELECT *  FROM pages  WHERE CONCAT_WS('', column1, column2, column3) LIKE '%keyword%' 

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.