How to add columnName and its value dynamically into MYSQL Table

Question

I have a master table, For e.g Fruits_Details(Master Table) which contains column name as:
f_name | f_price | location
I want to present a form to the user which consist of the above fields but also contains an additional "+" sign from which he can add new details as key, value pair.

For e.g :
fruit_color - red
fruit_season - spring
And many more details like this. I want that these two details should be stored in a different table(Child Table-I will implement foreign key concept).
But I am confused that how will design a query which will dynamically add column name and its related value in my child table.

For what it's worth, WordPress implements this key/value approach in their wp_postmeta table for assigning attributes to posts. You can look at their design for an example. It's pretty good. — O. Jones
– O. Jones, Commented Sep 6, 2018 at 9:56

juergen d · Accepted Answer · 2018-09-06 09:34:28Z

1

The child table should have a attibute name and value there has to be nothing dynamic.

child table
-----------
fruit_id
attribute_name
attribute_value


insert into child (fruit_id, attribute_name, attribute_value)
values (1, 'color', 'red')

answered Sep 6, 2018 at 9:34

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

2 Comments

Faisal Jaffri Over a year ago

But i want something like this: insert into child(fruit_id,fruit_color,fruit_location,....) values(1,'color','India'). But these column name should be dynamic, i mean if the next user wants to add fruit_season column along with its value.It should also get appended in the the child table.

juergen d Over a year ago

Don't do it. Using dynamic structures is absolutely not recommended

Guy Louzon · Accepted Answer · 2018-09-06 09:48:07Z

0

Since the potential is endless, don't do it by columns, but by rows... You'll be able to extract the info one way, and display it another.

A table called fruits_attributes, that will contain the columns: attribute_id, f_id (foreign key), attribute_name, attribute_value

CREATE TABLE `fruits_attributes` (
  `attribure_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `f_id` mediumint(8) unsigned NOT NULL,
  `attribure_name` varchar(50) NOT NULL,
  `attribure_value` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `f_id` (`offer_id`),
  KEY `attribure_name` (`attribure_name`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
;

Then a SELECT query should be something like

SELECT f.*, fa.* FROM
Fruits_Details f
LEFT JOIN fruits_attributes fa ON fa.id = f_id

edited Sep 6, 2018 at 9:48

answered Sep 6, 2018 at 9:37

Guy Louzon

1,21310 silver badges19 bronze badges

2 Comments

Faisal Jaffri Over a year ago

But what if i want to add 3 more attributes than how will i execute it.

Guy Louzon Over a year ago

Anytime you add an attribute, the JOIN will add it. as long as the attribute belongs to the original master table fruit, of course

Collectives™ on Stack Overflow

How to add columnName and its value dynamically into MYSQL Table

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related