How to search multiple columns in MySQL?

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?

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%'

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.

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.

1)

select *
from employee em
where CONCAT(em.firstname, ' ', em.lastname) like '%parth pa%';

2)

select *
from employee em
where CONCAT_ws('-', em.firstname, em.lastname) like '%parth-pa%';

First is usefull when we have data like : 'firstname lastname'.

e.g

• parth patel
• parth p
• patel parth

Second is usefull when we have data like : 'firstname-lastname'. In it you can also use special characters.

e.g

• parth-patel
• parth_p
• patel#parth

Here is a query which you can use to search for anything in from your database as a search result ,

SELECT * FROM tbl_customer 
    WHERE CustomerName LIKE '%".$search."%'
    OR Address LIKE '%".$search."%' 
    OR City LIKE '%".$search."%' 
    OR PostalCode LIKE '%".$search."%' 
    OR Country LIKE '%".$search."%'

Using this code will help you search in for multiple columns easily

SELECT * FROM persons WHERE (`LastName` LIKE 'r%') OR (`FirstName` LIKE 'a%');

Please try with above query.