0

Hopefully this makes sense to someone, I'm attempting to get Data from my database with a php query using WHERE, which works great for a standard category... WHERE category ='fashion'

However what if my column has multiple values separated like commas such as below:

+--------+----------------------------------+
|  Name  |             Category             |
+--------+----------------------------------+
| Shop A | Fashion, Food, Tech              |
| Shop B | Food                             |
| Shop C | Fashion, Tech, Travel, Insurance |
+--------+----------------------------------+

How could I return the data for Shop A and Shop C which would be: WHERE category = 'fashion'

Or equally I would like to change it to food: WHERE category = 'food' and it returns the data for Shop A and B

I've attempted searching for a solution and seen " FIND_IN_SET " , but i'm not too sure of the best approach / most efficient method

Thanks if anyone can help / point me in the right direction!

  • Ahh now you know why comma delimited lists in database columns are such a bad idea, whereas good normalisation is a great idea – RiggsFolly Mar 12 '20 at 19:25
  • 2
    `FIND_IN_SET()` is the solution with this schema, but it would be better if you normalized the data. See https://stackoverflow.com/questions/28639692/query-with-multiple-values-in-a-column/28639762#28639762 – Barmar Mar 12 '20 at 19:28
  • @RiggsFolly not always it depends with user requirements and use-case. – Omari Victor Omosa Mar 12 '20 at 19:54
  • The provided answer as duplicate does not answer the question asked.What Barmar has commented is the correct answer. – Omari Victor Omosa Mar 12 '20 at 20:27

1 Answers1

0

It is not probably best option but you can use SQL command within below as an alternative.

SELECT * FROM `example_db` WHERE category like '%Fashion%';
ridvanaltun
  • 2,595
  • 2
  • 15
  • 28