Dynamically create MySQL table columns

Question

I have the following MySQL table which is structured like that:

| id | bonus0 |

Now I want to add the following data set:

| id | bonus0 | bonus1 | bonus2 |  bonus3 |
| 10 |   4582 |  2552  |  8945  |   7564  |

As you can see the columns bonus1 - bonus3 aren´t created yet.

How would a php script/ query look like which checks if enough columns are already available and if not which will create the missing ones with consecutive numbers at the end of the word "bonus"?

So in the example the columns bonus1 - bonus3 would be created automatically by the script.

A schema like this is a pretty clear violation of the Zero, One or Infinity Rule of database normalization. If possible try and restructure this into a one-to-many relational structure as that will make your query almost trivial. You do not want a schema you have to constantly alter to add new data. You do want a schema that allows you to trivially add new rows to add new data. — tadman
– tadman, Commented Mar 12, 2021 at 0:08
If you change your table structure to something like this, id , bonus_id, bonus_value, your data instead would be like 10, 0, 4582 for bonus0, 10, 1, 2552 for bonus1 and so forth. Then you don't have to worry about creating column when there's a new bonusX — FanoFN
– FanoFN, Commented Mar 12, 2021 at 0:12
Any time you find yourself manually numbering variables/columns/identifiers like this it's a strong indication that there's a flaw in your design and you should step back and have a fresh look. As others have already suggested, the best approach for this particular issue is to normalize your database schema. — Sammitch
– Sammitch, Commented Mar 12, 2021 at 0:20
It's possible to create a JSON field to store arbitrary values, and that's one way this could be resolved, but Zak's answer is the best approach — Matthew Daly
– Matthew Daly, Commented Mar 12, 2021 at 0:23

Zak · Accepted Answer · 2021-03-12 00:37:36Z

In reality (I mean a normalized relational database) you should have 3 tables. Lets call them people, bonuses and bonus_to_person

people looks like:

+-----------------+------------+
|    person_id    |    name    |
+_________________+____________+
|       1         |    john    |
+-----------------+------------+
|       2         |    frank   |
+-----------------+------------+

bonuses Looks like

+----------------+--------------+
|    bonus_id    |    amount    |
+________________+______________+
|       1        |    1000      |
+----------------+--------------+
|       2        |    1150      |
+----------------+--------------+
|       3        |    1200      |
+----------------+--------------+
|       4        |     900      |
+----------------+--------------+
|       5        |     150      |
+----------------+--------------+
|       6        |     200      |
+----------------+--------------+

bonus_to_person Looks like

+----------------+-----------------+
|    bonus_id    |    person_id    |
+________________+_________________+
|       1        |       1         |
+----------------+-----------------+
|       2        |       2         |
+----------------+-----------------+
|       3        |       2         |
+----------------+-----------------+
|       4        |       1         |
+----------------+-----------------+
|       5        |       1         |
+----------------+-----------------+
|       6        |       1         |
+----------------+-----------------+

This way, any ONE person can have unlimited bonuses simply by INSERTING into bonuses with the amount, and INSERTING into bonus_to_person with the bonus_id and person_id

The retrieval of this data would look like

SELECT a.name, c.amount from people a
   LEFT JOIN bonus_to_people b
   ON a.person_id = b.person_id
   
   LEFT JOIN bonuses c
   ON c.bonus_id = b.bonus_id

   WHERE a.person.id = 1;

Your result from something like this would look like

+------------+----+-------+
|    name    |    amount  |
+____________+____________+
|    john    |    1000    |
+------------+------------+
|    john    |    900     |
+------------+------------+
|    john    |    150     |
+------------+------------+
|    john    |    200     |
+------------+------------+

You should be using this normalized approach for any database that will continue growing -- Growing "deeper" than "wider" is better in your case ..

ev0tix · Accepted Answer · 2021-03-12 00:09:34Z

-2

// Get existing columns of the table
// $queryResult = run SQL query using PDO/mysqli/your favorite thing: SHOW COLUMNS FROM `table`

// Specify wanted columns
$search = ['bonus0', 'bonus1', 'bonus2', 'bonus3'];

// Get just the field names from the resultset
$fields = array_column($queryResult, 'Field');

// Find what's missing
$missing = array_diff($search, $fields);

// Add missing columns to the table
foreach ($missing as $field) {
  // Run SQL query: ALTER TABLE `table` ADD COLUMN $field INT
}

answered Mar 12, 2021 at 0:09

ev0tix

172 bronze badges

Collectives™ on Stack Overflow

Dynamically create MySQL table columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related