2

So first of all I know there is many post about "How to store arrays in MySql database?" and yes I went through most of them and these solutions seems to just create separate tables and joins them. I do not think this is the case. At least I could not come up with anything.

Alright so I have following tables:

  • product: |id|name|link|color_list|size_list|cloth_type|
  • size_indicators |id|name|link|
  • color_indicator |id|name|link|
  • cloth_indicator |id|name|link|

What i want to do: On the product page I have a filter with cloth_type, color, size. Same goes for the colors Those are checkboxes.

I want to be able to check those boxes and press the "Filter" button or any other button to filter the query. I did not post already existing query because they are joining with other tables regarding other functions than this.

I was thinking maybe it is possible to have something like this: size_indicators table have string/array: 1,4,3 (something similar for other tables) so array would be $size_array([0] => 1, [1] => 4, [2] => 3) and query would be like: $stmt = $pdo->prepare("SELECT products.p_id, products.name, products.size_list, FROM products WHERE size_list = $size_array;

I know this query wont work, that was just an idea if i could somehow put array into product table on size_list column and then compare it with array of sizes selected.

Help me to get general idea how I should do this for one list(size_list) and I will do the rest. I have been searching for this but cant really find anything that would help. Also mby I'm doing everything wrong and someone has other idea how I should construct my tables and do this.

Ilya Chumakov
  • 23,161
  • 9
  • 86
  • 114
Royal
  • 69
  • 5
  • 3
    "*..solution seems to just create seperate table and join them*" And That's Correct. Learn About Normalization. – Nana Partykar Aug 04 '16 at 12:55
  • 1
    If you don't normalize then it will be impossible for your system to grow beyond some homebrew project. The is zero reason to attempt what you are proposing and the reason you cannot find any help with this online is because no one is going to write a blog which advocates such poor practice. – MonkeyZeus Aug 04 '16 at 12:58
  • So MonkeyZeus, would you have suggestion how to normalize my db? Cuz what i need is dynamic ammount of sizes, colors, types. For each product. – Royal Aug 04 '16 at 13:04
  • Shameless plug for [Junction Tables](http://stackoverflow.com/a/32620163) . If you don't do that, I would not want to be in your shoes. – Drew Aug 04 '16 at 13:58

3 Answers3

1

You can use FIND_IN_SET for these kind of Query

SELECT products.p_id, products.name, products.size_list FROM products WHERE FIND_IN_SET(size_list,'1,4,3');
Dhaval Bhavsar
  • 495
  • 5
  • 17
1

You can use a metadata table to store different informations linked to a single row in a table like Wordpress do: https://codex.wordpress.org/Database_Description#Table:_wp_usermeta

You simply use the name of the attribute (key) with the id of your product to get all variants of your product with id 4.

Example to get all colours of your product: SQL SELECT ci.`name` AS color FROM `product_meta` AS pm JOIN `color_indicator` AS ci ON ci.`id` = pm.`value` WHERE pm.`key` LIKE 'color' AND pm.`id_product` = 4;

If you instead want to find all red Products you can use: SQL SELECT * FROM `product` AS p WHERE p.`id` IN ( SELECT pm.`id_product` FROM `product_meta` AS pm JOIN `color_indicator` AS ci ON ci.`id` = pm.`value` WHERE pm.`key` LIKE `color` AND ci.`name` LIKE 'red' );

Dege
  • 308
  • 2
  • 11
1

A table with a single column and using that column as a KEY (the natural key of that table) IS an array.

The solution would be to have a many-to-many relationship between your product table and your support tables. Support tables are tables that contains only one or two columns (id and name or just name for example). Repeat the pattern for Size and Cloth:

Products: Id, Name, Description

Types: Id

ProductTypes: ProductId, TypeId

Each checked value is an entry in ProductTypes and tells that the product have that characteristic. The table Types is used to display all possible filter values in your web page for the user to filter. Once the user selected values and pressed "Filter", you have to construct a SQL query like the one below.

The query could change based on your business logic. For example, if you want to display products that have any of the selected types/size/cloth or exactly all checked values. In both cases, the SQL query will be pretty complex. This is an rough, untested and oversimplified example:

$stmt = "SELECT * FROM Products p " +
            "INNER JOIN ProductTypes pt ON p.Id = pt.ProductId " +
        "WHERE pt.TypeId IN (" + $sqlEscapedCommaSeparatedTypeList + ")";

As I said, this is untested PHP and the query is a bit rough. It will be slow for huge product sets. Above all don't forget to escape inputs to avoid SQL injection.

I would recommend to create a stored procedure and use a temporary table filled with user selections and join on it to optimise this to some extend.

As I said, this is not a simple query and your business case may not be the one I assumed. Since you are querying on three support tables, the stored procedure would be the best solution or you. This is especially true if you want to keep it simple and avoid a single behemoth unreadable SQL query.

formixian
  • 1,549
  • 16
  • 25