MySQL - Is it possible to use LIKE on all columns in a table?

Question

I'm trying to make a simple search bar that searches through my database for certain words. It is possible to use the LIKE attribute without using WHERE? I want it to search all columns for the keywords, not just one. Currently I have this:

mysql_query("SELECT * FROM shoutbox WHERE name LIKE '%$search%' ")

Which obviously only searches for names with the search input. I tried both of these:

mysql_query("SELECT * FROM shoutbox LIKE '%$search%' ")
mysql_query("SELECT * FROM shoutbox WHERE * LIKE '%$search%' ")

and neither worked. Is this something that is possible or is there another way to go about it?

Answer 1

There's no shortcut. You need to specify each column separately.

SELECT * FROM shoutbox 
    WHERE name LIKE '%$search%' 
        OR foo LIKE '%$search%' 
        OR bar LIKE '%$search%'  
        OR baz LIKE '%$search%' 

Answer 2

You might want to look at the MATCH() function as well eg:

SELECT * FROM shoutbox 
WHERE MATCH(`name`, `foo`, `bar`) AGAINST ('$search')

You can also add boolean mode to this:

SELECT * FROM shoutbox 
WHERE MATCH(`name`, `foo`, `bar`) AGAINST ('$search') IN BOOLEAN MODE

You can also get the relevance scores and add FULLTEXT keys to speed up the queries.

Answer 3

One option is to use CONCAT to glue the columns together into a single string.

SELECT * FROM shoutbox 
WHERE CONCAT(name, foo, bar, baz) LIKE '%$search%' 

However, this method can occasionally produce false positives due to the columns getting glued together with no spacing. You can mitigate that problem by adding some kind of spacing (' '), delimiter ('-'), or null byte character ("\0") that you know won't occur in your search term.

For example, if the search term doesn't contain any spaces, then you can just delimit the columns with spaces:

SELECT * FROM shoutbox 
WHERE CONCAT(name, ' ', foo, ' ', bar, ' ', baz) LIKE '%$search%'

Whereas, if the search term does contain spaces, then a null byte would work better:

SELECT * FROM shoutbox 
WHERE CONCAT(name, "\0", foo, "\0", bar, "\0", baz) LIKE '%$search term%'