CODEWITHSUNDEEP
phpmysql
kzs
kzs

Reputation: 1111

mysql query with WHERE IF and FIND_IN_SET() function

I have been using the following query:

 "SELECT * FROM mytable WHERE IF(FIND_IN_SET(column1,'$searchString') > 0,1,0) 
+ IF(FIND_IN_SET(column2, '$searchString') > 0, 1, 0) 
+ IF(FIND_IN_SET(column3, '$searchString') > 0, 1, 0)+ 
IF(FIND_IN_SET(column4, '$searchString') > 0, 1, 0) > 0";

So (in case) after 4 successful matches, does the query becomes the following??:

"SELECT * FROM mytable WHERE 1+1+1+1 > 0";

Please ensure me whether my assumption is right. thanks in advance.

Upvotes: 0

Views: 3263

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

Normally, you would do this type of comparison with or unless you want really want to count the matches:

SELECT *
FROM mytable
WHERE (FIND_IN_SET(column1, '$searchString') > 0 OR
       FIND_IN_SET(column2, '$searchString') > 0 OR 
       FIND_IN_SET(column3, '$searchString') > 0 OR
       FIND_IN_SET(column4, '$searchString') > 0
      );

Upvotes: 1

juergen d
juergen d

Reputation: 204884

Yes, but you can make this shorter

SELECT * FROM mytable 
WHERE 
(
    FIND_IN_SET(column1, '$searchString') > 0 +
    FIND_IN_SET(column2, '$searchString') > 0 + 
    FIND_IN_SET(column3, '$searchString') > 0 + 
    FIND_IN_SET(column4, '$searchString') > 0
) > 0

Upvotes: 0

Related Questions