How to create table with dynamic number of columns MYSQL

Question

I'm trying to create table in mysql. There is $column_str which stores the names of the column.

If we have 3 columns it will be $columns_str="123", 4 columns -> $columns_str="1234"

So, I need to create table using variable $columns_str.

This code creates table1 with 1 column: "123":

$columns_str="123"; $table_name = table1;
$connection->query("CREATE TABLE `kcup`.`$table_name` ( `$columns_str` TEXT NOT NULL ) ENGINE = InnoDB;")

I need table with 3 columns: "1","2","3".

Help pls and thank you!

Please note that I answered what is exposed in your title. How to create a table with dynamic number of columns. From the text of your answer it is possible to see that your question is different. It is something like: "How to dynamically create a mysql table from php" (standard table, not table with dynamic number of columns) — Davide Lorenzo MARINO
– Davide Lorenzo MARINO, Commented Apr 10, 2015 at 8:02

Stu · Accepted Answer · 2015-04-10 07:16:13Z

2

To create a table with three columns named 1, 2, and 3 when you have a variable $columns_str="123" you could use something like this;

<?php
$columns_str="123";
$table_name = 'table1';
$cols = str_split($columns_str);
$colQuery = '`id` int(11) NOT NULL AUTO_INCREMENT,';
foreach($cols as $col)
{
    $colQuery .= "
        `$col` TEXT NOT NULL,";
}
$colQuery .= "
PRIMARY KEY (`id`)";
$connection->query("CREATE TABLE `kcup`.`$table_name` ( $colQuery ) ENGINE = InnoDB;")

This would run the following SQL command;

CREATE TABLE `kcup`.`table1` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `1` TEXT NOT NULL,
    `2` TEXT NOT NULL,
    `3` TEXT NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

answered Apr 10, 2015 at 7:16

Stu

4,15026 silver badges43 bronze badges

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

Comments

Davide Lorenzo MARINO · Accepted Answer · 2015-04-10 07:09:08Z

1

You can't create a table with dynamics columns, but you can simulate it with a table like this one

ID   FIELD    VALUE
1    ID       4345
1    NAME     PAUL
1    SURNAME  SMITH
2    ID       4346
2    NAME     MARC
2    SURNAME  BROWN

The PK of that table is ID, FIELD Addyng a new field is equivalent to add a new row.

So adding the field EMAIL is equivalent to add two rows (one for PAUL and one for MARC) and you will have the following records

ID   FIELD    VALUE
1    ID       4345
1    NAME     PAUL
1    SURNAME  SMITH
2    ID       4346
2    NAME     MARC
2    SURNAME  BROWN
1    EMAIL    [email protected]
2    EMAIL    [email protected]

answered Apr 10, 2015 at 7:09

Davide Lorenzo MARINO

27k4 gold badges45 silver badges58 bronze badges

9 Comments

Deep Kakkar Over a year ago

Yes ,we can create dynamic columns in sql . We use PIVOT function to create dynamic coumns in sql.

Davide Lorenzo MARINO Over a year ago

How with standard sql?

Deep Kakkar Over a year ago

Please check this link sql2developers.com/2012/07/…

Davide Lorenzo MARINO Over a year ago

Is that a sort of view... or is it possible to insert delete update records from the pivot table?

Davide Lorenzo MARINO Over a year ago

This is not a dynamic table. This is a static table created with a dynamic php code. Basically the table once created is static.

|

Collectives™ on Stack Overflow

How to create table with dynamic number of columns MYSQL

2 Answers 2

Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related