user2566387 user2566387 - 1 year ago 43
MySQL Question

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

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 Source

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.