Retrieving values stored in a signle MySQL cell

PHP
1

Hi all,

My site shows items of clothing depending on the category (drersses, tops etc) Within each category, I also have a refine bar that filters items of clothing further into different sizes - if you press 6, only items of sizes 6 or less than 6 should appear, if you press only 10 items of size 10 would then appear and so on. In MySQL, I store all available sizes in a single cell per item. For example - prod_type: red blowse; prod_size: 6,8,12,14. When I use the refine bar, it only fetches the first size, ignoring all the rest. So the red blouse in this example would only appear if size 6 is pressed, but not if 8, 12 or 14 are pressed, although these numbers are in the same cell. Here’s the code I’m using so far to retrieve mySLQ data based on sizes:


switch ($size){
case "6":
$query = "SELECT * FROM products JOIN categories ON products.prod_cat_name = categories.cat_name WHERE products.prod_cat_name = '$cat_name' 
 AND products.prod_size <=6 OR products.prod_parent_name = '$cat_name'  AND products.prod_size <=6 LIMIT $offset, $rowsperpage" ;
break;
case "8":
$query = "SELECT * FROM products JOIN categories ON products.prod_cat_name = categories.cat_name WHERE products.prod_cat_name = '$cat_name' 
 AND products.prod_size = '8' OR products.prod_parent_name = '$cat_name' AND products.prod_size = '8' LIMIT $offset, $rowsperpage" ;
break;
case "10":
$query = "SELECT * FROM products JOIN categories ON products.prod_cat_name = categories.cat_name WHERE products.prod_cat_name = '$cat_name' 
 AND products.prod_size = 10 OR products.prod_parent_name = '$cat_name'  AND products.prod_size = 10 LIMIT $offset, $rowsperpage" ;
break;
case "12":
$query = "SELECT * FROM products JOIN categories ON products.prod_cat_name = categories.cat_name WHERE products.prod_cat_name = '$cat_name' 
 AND products.prod_size LIKE '12' OR products.prod_parent_name = '$cat_name'  AND products.prod_size LIKE '12' LIMIT $offset, $rowsperpage" ;
break;
case "14":
$query = "SELECT * FROM products JOIN categories ON products.prod_cat_name = categories.cat_name WHERE products.prod_cat_name = '$cat_name' 
 AND FIND_IN_SET('14', 'products.prod_size') > 0 OR products.prod_parent_name = '$cat_name'  AND FIND_IN_SET('14', 'products.prod_size') > 0 LIMIT $offset, $rowsperpage" ;
break;
case "16":
$query = "SELECT * FROM products JOIN categories ON products.prod_cat_name = categories.cat_name WHERE products.prod_cat_name = '$cat_name' 
 AND products.prod_size >= 16 OR products.prod_parent_name = '$cat_name'  AND products.prod_size >= 16 LIMIT $offset, $rowsperpage" ;
break;}

$result = mysql_query($query);

How do I make it fetch all the sizes stored in the size cell so that , as in our example above, the red blouse would appear if sizes 6, 8, 12 or 14 are pressed, not just size 6.

Anyone have any ideas? Thanks for your help in advance!

2

Thanks! And in this table, do I have to then list each product individually and in each size? For example, id: 1 - product: red blouse - size: 6. id: 2 - product: red blouse - size: 8 and so on for each product in each available size?

3

Thanks for your advice. If I created a separate table for product sizes, what would that table contain, what would be the structure of it?

4

the most important thing you can do to resolve your situation is create a separate product_sizes table

storing multiple values in a single column breaks the first normal form rule, and consequently you will have troubles (as you have discovered)

yes, there is a way to pull out the right sizes from the column, but i’m not going to show it to you, because it will not scale, i.e. the query will get slower and slower the more rows you have, because it is forced to do a table scan

5

But only the product id and the size. The product name (red blouse) remains in the products table.

6 
CREATE TABLE product_sizes
( product_id INTEGER NOT NULL 
, CONSTRAINT prodsize_product_fk 
    FOREIGN KEY ( product_id ) REFERENCES products.id
, size VARCHAR(9) NOT NULL [COLOR="Blue"]-- allows 'XXL'[/COLOR]
[COLOR="Red"]-- size foreign key would be overkill[/COLOR]
, PRIMARY KEY ( product_id, size )
);
7

Thank you very much, everything works now.