0

I have a problem when using commas separating distinct values contained in a column. I call a query as follows:

$findd = KL123,OTHER123

$query = "SELECT * 
            FROM test1 
           WHERE concat_ws(' ', column1, column2) LIKE '%".$findd."%' 
              OR concat_ws(' ', column2, column1) LIKE '%".$findd."%'";

So, I would like to search for words separated by commas with the following value $findd= KL123,OTHER123 in a table that contains the following data:

ID  name  column1    column2
--  ----  ---------  -------
 1  dan   KL123  
 2  lolo  ASI22      OTHER123,KL145
 3  lala  TA123
 4  ttoo  OTHER123    

But no results "found". I would like to "find" the IDs 1,2,4.

But if I search without commas the value $findd= OTHER123 works with the same data shown above, because I "get" the IDs 2,4

The question

How can I solve this task with commas?

My level in MySQL is very basic and I find it difficult to solve this.

1
WHERE FIND_IN_SET("LK123", CONCAT_WS(",", ...))

It does require the use of "comma" as the separator, and you cannot have commas in the strings.

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set

Or... FULLTEXT

ADD FULLTEXT(column1, column2)

WHERE MATCH(column1, column2) AGAINST("+KL123 +OTHER123" IN BOOLEAN MODE)

Yes, your app will need to construct this syntax.

  • please update the question – Elias ayala Sep 4 '17 at 0:40
  • 1
    @Eliasayala - Perhaps you meant that you updated the question, and you'd like Rick to see that for further info? – RDFozz Sep 5 '17 at 19:46
  • 1
    made another stab – Rick James Sep 5 '17 at 20:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.