pasaisea pasaisea - 8 months ago 38
SQL Question

Add dynamic column to existing MySQL table?

just wondering how to insert dynamic column to existing MySQL table? For example: I already have "

" and I want to make input fields that can add dynamic column to the existing
example: column1, column2, column3
. How to do that with dynamic numbering?


I would agree with @Barmar that your SQL table structure is wrong if you are trying to do this. What you are trying to do in this case is what's called a "one to many" relationship. This is usually achieved by doing something like the following.

Table 1: Contains columns for all the usual data (non-"dynamic" columns in your terms), and a unique ID column which all good database tables should have

Table 2: An ID column, and column that refers to the ID column on table one and a column for the data that goes in the dynamic column.

Now you can store your values that you would normally store in "dynamic columns" in individual rows on the second table.


//  sample:
// | id | name |
// dynamic_values:
// | id | sample_id | value |

// Selecting data

SELECT * FROM sample WHERE id = 1;
SELECT * FROM dynamic_values WHERE sample_id = 1;

// Querying on "dynamic columns"
SELECT * FROM sample s LEFT JOIN dynamic_values d ON d.sample_id = WHERE d.value = 'something';