How to query multipe values in 1 column using Mysql

Question

I have the table fabrics such as:

id | fabric_texture
123 | 70:cotton,20:polyeste,10:cmere

How should I create a mysql select to search by percent cotton,polyeste.. I tried FIND_IN_SET but can't use the percent value.

Update: Search fabrics have over 50% cotton or lower 20% polyeste..

Important: cotton, polyeste.. is dynamic

Do you want to search by the presence of cotton or polyeste, or do you need to specifically search for the percent values? It would help if you edited your post to provide more sample rows together with a sample query rowset you would like to return. — Michael Berkowski
– Michael Berkowski, Commented Feb 4, 2016 at 21:11
I want make the Search like: over 50% cotton or lower.. etc.. — Anh
– Anh, Commented Feb 4, 2016 at 21:14
This is horrible database structure. The reason it's horrible is because queries like this are extremely difficult. You should have a table with columns id | fabric | percentage. — Barmar
– Barmar, Commented Feb 4, 2016 at 21:54

Barmar · Accepted Answer · 2016-02-04 22:13:57Z

3

You should move fabric_texture out to a new table where everything is in its own column.

CREATE TABLE fabric_texture (
    item_id INT,
    fabric VARCHAR(32),
    percentage INT,
    UNIQUE KEY (item_id, fabric)
);

Then you can do a query:

SELECT t1.item_id
FROM fabric_texture AS t1
JOIN fabric_texture AS t2 ON t1.item_id = t2.item_id
WHERE t1.fabric = 'cotton' AND t1.percentage > 50
AND t2.fabric = 'polyester' AND t2.percentage < 20

To get other details about the item, you can join this to your main items table.

The advantage of this schema over columns in the original table for cotton, polyester, etc. is that it doesn't require you to hard-code the fabric names into the table structure. If a new fabric comes out, you can just add new rows with it for those products.

edited Feb 4, 2016 at 22:13

answered Feb 4, 2016 at 21:59

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pjd Over a year ago

You might want the key to be item_id and fabric since it seems like you could have equal percentages of two different fabric types.

pjd Over a year ago

Oh, wait, you may have simply made a typo since your columns are fabric and percentage and there is no fabric_percentage.

Barmar Over a year ago

Yes, that was a typo, but you're right that I didn't need the percentage in the key.

cooooookie monster · Accepted Answer · 2016-02-04 21:45:23Z

0

I would recommend editing your table to have a column for each possible fabric type maybe then have and int associated with it for the percentage, then you can query

"select * from fabrics where cotton = 70"

or in your comment example

"select * from fabrics where cotton <= 50"

so your old table was

id | fabric_texture

your new table is

_id | cotton = INT | poly = INT | cmere = INT | etc = INT |

the int representing values 0-100 (0-100%)

from your example this would be

123 | 70 | 20 | 10 | 0 | 0 | .....

edited Feb 4, 2016 at 21:45

answered Feb 4, 2016 at 21:15

cooooookie monster

3345 silver badges16 bronze badges

4 Comments

Anh Over a year ago

yes, but the problem is this colum store: 70:cotton,20:polyeste,10:cmere

cooooookie monster Over a year ago

Anh Over a year ago

fabric_texture is the colum not the table

Anh Over a year ago

i know but its example, i want add more type and dynamic it :D

Collectives™ on Stack Overflow

How to query multipe values in 1 column using Mysql

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related