How to get result properly from MySQL table

Question

This is my product table

and I want to get all product where product_sizes in (Xl, M)

I am trying into this code

SELECT `id`,`name`,`sell_price`,`product_sizes`,`product_colors`
FROM `view_product_listing` 
WHERE `product_sizes` in ('XL', 'M')

return to me productId 15 and 16 but I want to productId 4,14,15,16

Please stop here and re-think your approach of storing serialized/comma separated data into the db - This approach will lead to many many issues. This is not scalable, nor maintainable. As you see even a single SELECT can be a huge pain. Read over [database normalization](https://www.studytonight.com/dbms/database-normalization.php)... — B001ᛦ, Apr 18 '19 at 07:44
Related topic - https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — u_mulder, Apr 18 '19 at 07:46

score 2 · Answer 1 · answered Apr 18 '19 at 07:53

You should seriously avoid storing the sizes as CSV (comma separated) data, see below for an alternative table design. As a workaround, we can use FIND_IN_SET:

SELECT id, name, sell_price, product_sizes, product_colors
FROM view_product_listing
WHERE FIND_IN_SET('M', product_sizes) > 0 OR FIND_IN_SET('XL', product_sizes) > 0;

But note that a much better database design would be to have a separate table for products, sizes, and colors: (colors omitted)

products
id | name                                 | sell_price |
4  | Women Maxi White Dress               | 550.00     |
14 | Women Maxi Blue Dress                | 700.00     |
15 | Women Fit and Flare Multicolor Dress | 750.00     |
16 | Floral Print Straight Kurta          | 699.00     |

sizes
product_id | product_size
4          | XL
4          | M
14         | XL
14         | XXL
14         | L
14         | M
15         | XL
16         | M

Now we can use a straightforward join to find all products, and their metadata, which have either the medium or XL size:

SELECT
    p.id,
    p.name,
    p.sell_price,
    s.product_size
FROM products p
INNER JOIN sizes s
    ON p.id = s.product_id
WHERE
    s.product_size IN ('M', 'XL');

_a much better database design would be to have a separate table for products, sizes, and colors..._ +1 — B001ᛦ, Apr 18 '19 at 07:55
@B001ᛦ This is like the fourth CSV MySQL question I have seen in a few hours, and I got tired of just giving the workaround. So, it is better to explain what the OP should be doing, I think :-) — Tim Biegeleisen, Apr 18 '19 at 07:56

Rahul · Answer 2 · 2019-04-18T10:22:07.457

1

You can use FIND_IN_SET like this,

Version 1:

SELECT `id`,`name`,`sell_price`,`product_sizes`,`product_colors`
FROM `view_product_listing` 
WHERE FIND_IN_SET('XL',product_sizes) OR FIND_IN_SET('M',product_sizes)

EDIT 1

There is one more approach to achieve this,

Version 2:

SELECT `id`,`name`,`sell_price`,`product_sizes`,`product_colors`
FROM `view_product_listing` 
WHERE CONCAT(',',product_sizes,',') REGEXP ",(XL|M),"

Source link for second version.

EDIT 2

You product_sizes is having spaces after commas, which is not the behaviour find_in_set is expecting. To trim all spaces from that column,

UPDATE `table` SET `product_sizes` = REPLACE(`product_sizes`, ' ', '')

And now run any version query you want to try, it will work.

edited Apr 18 '19 at 10:22

answered Apr 18 '19 at 07:45

Rahul

18,271
7
41
60

it is working SELECT `id`,`name`,`sell_price`,`product_sizes`,`product_colors` FROM `view_product_listing` WHERE FIND_IN_SET('XL',product_sizes) OR FIND_IN_SET('M',product_sizes) – Santanu Adak Apr 18 '19 at 07:51
Okay, find_in_set splits a string into array so, XL,M is not exists in a string which splits into array. – Rahul Apr 18 '19 at 07:53
why it's not working SELECT `id`,`name`,`sell_price`,`product_sizes`,`product_colors` FROM `view_product_listing` WHERE FIND_IN_SET('XL,M', product_sizes) – Santanu Adak Apr 18 '19 at 07:53
It's like `explode(',' $str)` followed by `in_array` like in PHP. So, core functionalities, its build that way, can't do much. – Rahul Apr 18 '19 at 07:55
why not getting only 'M' using FIND_IN_SET("M",product_sizes) ? – Santanu Adak Apr 18 '19 at 10:13
OP, check my **EDIT 2** section of answer for your question! – Rahul Apr 18 '19 at 10:22

score 1 · Answer 3 · answered Apr 18 '19 at 07:46

1

use find_in_set()

SELECT `id`,`name`,`sell_price`,`product_sizes`,`product_colors`
FROM `view_product_listing` 
WHERE FIND_IN_SET(product_sizes,'XL,M')

answered Apr 18 '19 at 07:46

Fahmi

37,315
5
22
31

Daniel · Answer 4 · 2019-04-18T07:52:49.270

1

If you are only searching for one size you could use the built-in MySQL function FIND_IN_SET()

SELECT `id`,`name`,`sell_price`,`product_sizes`,`product_colors`
FROM `view_product_listing` 
WHERE FIND_IN_SET('XL', product_sizes)

However it only supports a single string (unless you add additional OR's, but that quickly becomes tedious).

The best approach would be to restructure your database so that the product sizes of items are in a seperate table with a reference to the item table and a size table.

That way you don't have to update your queries whenever you want to introduce new sizes and it will improve performance for your query.

A word of warning,

Do not attempt a LIKE clause, because search for LIKE '%XL%' will also match XXL

edited Apr 18 '19 at 07:52

answered Apr 18 '19 at 07:46

Daniel

10,641
12
47
85

But we could use `REGEXP` here, e.g. `WHERE product_sizes REGEXP '[[:<:]](XL|M)[[:>:]]'` – Tim Biegeleisen Apr 18 '19 at 07:55
@TimBiegeleisen True, but that has a serious impact on performance and also has to be updated whenever you want to introduce new sizes – Daniel Apr 18 '19 at 07:56

How to get result properly from MySQL table

4 Answers4