tijgerkaars tijgerkaars - 1 month ago 14
MySQL Question

SQL InnoDB Checking multiple columns for one LIKE value

I'm trying to find parts of a string (word,word,word,word) in one of the columns.

I tried

SELECT * FROM table WHERE LIKE '$string%' IN (col1, col2, col3, col4, etc)


but i get a fatal error telling me the syntax is wrong near LIK.

Is it possible to do this? and if so what am i messing up?

If I understood correctly an alternative would be to explode the string and do a foreach to check all of them separately?

$array = explode(',', $string);

foreach ($array as $string)
SELECT * FROM table WHERE MATCH (col1, col2, col3, col4, etc) AGAINST $string)

Answer

You can do a like for several columns using or condition

  SELECT * 
  FROM table 
  WHERE col1 LIKE concat($string.'%'  or col1 LIKE concat('%','$string','%')
  OR col2 LIKE concat($string.'%'  or col2 LIKE concat('%','$string','%')
  OR col3 LIKE concat($string.'%'  or col3 LIKE concat('%','$string','%')
  OR col4 LIKE concat($string.'%'  or col4 LIKE concat('%','$string','%')
  OR etc.... coln LIKE concat($string.'%'  or coln LIKE concat('%','$string','%')
Comments