0

I have been given a project where there are multiple categories stored in a row in a comma delimited list (e.g. 1,2,3). Then I have a categories table that has categories that looks like this:

ID  ShowcaseCategory        DisplayOrder 
1   Member's Work           0 
2   Eastern Resorts         1 
3   WesternResorts          2 
    
4   Products                4 
5   Nordic Skiing           3

I want to be able to pull the ShowcaseCategory (from above) from the table based on more than one value in the comma delimited list. For example, if the category contained 1,5 I would get Member’s Work and Nordic Skiing.

I think I need to get it in an array, possibly explode it and be able to do some join on the ID to get the (literal) ShowcaseCategory. Or, is there an easier way to do this?

In the end I want to dynamically build a list of checkboxes of Categories so they can be updated.

All help is appreciated

GMB
  • 216,147
  • 25
  • 84
  • 135
Peter Hines
  • 1
  • This is already answered here: https://stackoverflow.com/questions/650455/c-sharp-parameterized-query-mysql-with-in-clause (see the answer with FIND_IN_SET especially) – socasanta Jul 15 '20 at 02:06
  • Does this answer your question? [C# Parameterized Query MySQL with \`in\` clause](https://stackoverflow.com/questions/650455/c-sharp-parameterized-query-mysql-with-in-clause) – Adel Khayata Jul 15 '20 at 04:34
  • Can you give the project back? – Strawberry Jul 15 '20 at 06:39

1 Answers1

0

One option uses find_in_set(). Assuming that parameter :categories_list holds the csv list of categories ids, you would go:

select ShowcaseCategory
from mytable
where find_in_set(id, :categories_list)
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Sorry for the delay in getting back to this thread. I know how to used find_in_set. What I want to do is get the comma separated list from the row and then be able to use EACH one of the values to join with the literal. For example, if the category contained 1,5 I would get Member’s Work and Nordic Skiing. Thanks in advance. – Peter Hines Jul 22 '20 at 00:43