49

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?

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
George
  • 509
  • 1
  • 4
  • 4

6 Answers6

63

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%'
bishop
  • 37,830
  • 11
  • 104
  • 139
Terra Walker
  • 639
  • 5
  • 4
  • 3
    Be careful when joining on CONCAT -https://www.percona.com/blog/2007/10/16/be-careful-when-joining-on-concat/ – londox Aug 10 '17 at 09:43
  • 1
    This will yield wrong results when columns don't contain "keyword" in them but the concatenated string does, e.g. for `column1 = 'key'` and `column2 = 'word'`. – cl0ne Jun 30 '21 at 10:46
58

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.

akamike
  • 2,148
  • 13
  • 16
  • What is the params are the same value? Say, you are searching whether 'Steven' is in a first OR last name of a user? (Given that users is a table, last_name and first_name are columns.) – onebree Jun 18 '15 at 15:49
  • 1
    @HunterStevens In that case, see [this other answer to this question](http://stackoverflow.com/a/30365428/2908724). – bishop Aug 18 '15 at 20:34
20

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.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 2
    This is good, but it only works on exact matches, a search for dogs won't return a page titled dog. – George Mar 25 '10 at 10:26
  • 2
    Nope, you can use wildcard 'dog*' to match 'dogs' and 'dog'. though you can not use '*dog' to get 'adog'. Only prepending wildcards is supported. – David Lin Jun 17 '14 at 00:56
7

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
Parth Patel
  • 799
  • 1
  • 13
  • 20
6

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

Ravi Khatri
  • 141
  • 2
  • 4
0
SELECT * FROM persons WHERE (`LastName` LIKE 'r%') OR (`FirstName` LIKE 'a%');

Please try with above query.

Mayank Vadiya
  • 1,437
  • 2
  • 19
  • 32
user3781751
  • 11