Silver Silver - 4 months ago 25
PHP Question

Store array in MYSQL?

So first of all i know there is many post about "How to store array in MySql database?" and yes i have went throu most of them and solution seems to just create seperate table and join them. I do not think this is the case. Atleast i could no 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 filter with cloth_type, color, size.
Same goes for the colors
Those are checkboxes.

I want to be able to check those boxes and pres button Filter other button. and it would filter query.
I did not post already existing query because it is joining with other tables ragrding other functions than this.

I was thinking mby 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 im doing everything wrong and someone has other idea how i should construct my tables and do this.

Answer

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' );

Comments