20

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?

MPelletier
  • 16,256
  • 15
  • 86
  • 137
user2566387
  • 211
  • 1
  • 2
  • 7

4 Answers4

18

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%'
JJJ
  • 32,902
  • 20
  • 89
  • 102
  • 2
    I wasn't aware of "OR" previously. This should work perfectly. I appreciate it – user2566387 Aug 24 '13 at 06:52
  • hey @skv, could you please explain about the performance comparison between "match()" and "LIKE + OR"? – exAres Jul 17 '14 at 06:44
  • I am sorry I did not understand your question and if this would be a specific query post it as a question so that I or others can reply – skv Jul 17 '14 at 08:17
11

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.

Dave
  • 386
  • 2
  • 8
  • 2
    Produces "Can't find FULLTEXT index matching the column list " – Fanky Jul 16 '16 at 08:02
  • 1
    A link to the documentation might be useful as well: https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html – Kalko Aug 16 '16 at 10:40
  • From https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html: `MATCH (col1,col2,...) AGAINST (expr [search_modifier])`, where **search_modifier** is **IN BOOLEAN MODE**, i.e. it must be inside the braces of **AGAINST** – dofamin Jan 05 '22 at 14:50
9

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%'
Pikamander2
  • 7,332
  • 3
  • 48
  • 69
esdiweb
  • 91
  • 1
  • 2
  • Just to clarify in case someone misses the smiley: this is a joke answer. It would match false positives and concatenating columns is very slow. – JJJ Oct 18 '18 at 13:49
  • 1
    It was not a joke ... but you're right, it could match false positives, and probably slower... Anyway it works, for most small requets. Depending on what you are searching and what data is in your columns to choose if this "shortcut" is acceptable or not ;) – esdiweb Nov 28 '18 at 16:00
3

this will not show duplicate rows anymore. 

SELECT * FROM shoutbox 
WHERE (name LIKE '%$search%' 
    OR foo LIKE '%$search%' 
    OR bar LIKE '%$search%'  
    OR baz LIKE '%$search%')
Harsha
  • 377
  • 1
  • 8
  • 21