0

I have a product table under which i have create field name : product_attributes under which I have stored all product attributes in json data as show below:

{"color":["Red","Green","yellow"],"size":["XL","M","XXL","L"],"Brand":["Nike","Fila","Addidus","Lotto"],"Gender":["Male","Female"]}

Every product have there own attributes in the same format as given above. some product have same attributes and some have different for example :

{"color":["Green","Blue"],"size":["XXL","L"],"Brand":["Nike"],"Gender":["Male","Female"]}

Now I want to search products based on up mentioned data : user have option to search product based on attributes as we did on eCommerce websites

Example :

-- Color : ( Red or Green ) AND Size = ( XXL or XL ) AND Gender = ( Male )

-- Size : ( XL ) AND Color : ( Yellow ) AND Brand : ( Nike or Lotto )

so based on the above example user can search any thing as per his requirement so, I need a MySQL query which return me data based on selected attributes if found under any products

Please have a look on product table structure :

CREATE TABLE `products` ( `id` int(11) NOT NULL,`product` varchar(255) NOT NULL,`product_attributes` longtext NOT NULL,`price` int(11) NOT NULL)ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table products

INSERT INTO `products` (`id`, `product`, `product_attributes`, `price`) VALUES(1, 'product 1', '{\"Color\":[\"White\",\"Red\"],\"Size\":[\"Large\",\"XL\"],\"Brand\":[\"Nike\",\"Lotto\"],\"Gender\":[\"Female\",\"Male\"]}', 12),(2, 'product 2', '{\"Color\":[\"White\"],\"Size\":[\"XL\"],\"Brand\":[\"Fila\"],\"Gender\":[\"Female\"]}', 21),(3, 'Product 3', '{\"Color\":[\"White\",\"Red\",\"Black\"],\"Size\":[\"Large\",\"XL\"],\"Brand\":[\"Fila\",\"Addidus\"],\"Gender\":[\"Female\"]}', 15),(4, 'Product 4', '{\"Color\":[\"White\",\"Red\"],\"Size\":[\"Large\"],\"Brand\":[\"Fila\"],\"Gender\":[\"Male\"]}', 18);

Thanks in advance.

Er Parwinder Hirkewal
  • 306
  • 5
  • 14
  • 2
    mysql version? Background info [eav](https://dev.mysql.com/doc/refman/8.0/en/select.html). Do you have a table structure? Edit the question to include `SHOW CREATE TABLE {tablename}` for clarity. – danblack Jun 29 '20 at 08:43
  • *i have create field name : product_attributes under which I have stored all product attributes in json data* This is very bad structure for your task... – Akina Jun 29 '20 at 08:58
  • My Server version: 10.4.11-MariaDB – Er Parwinder Hirkewal Jun 29 '20 at 09:03
  • @Akina could you please suggest me the best way to manage it ? In future we have to create Single and variation products as well. Thanks in advance – Er Parwinder Hirkewal Jun 29 '20 at 09:13
  • See the comment provided by @danblack - it answers to this question. [EAV](https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model) pattern is safe for your task. – Akina Jun 29 '20 at 09:19
  • I mean to provide [this eav link](http://mysql.rjweb.org/doc.php/eav) which has a reference to [mariadb's dynamic columns](https://mariadb.com/kb/en/dynamic-columns/) supported in your version. – danblack Jun 29 '20 at 09:22

0 Answers0