Adding multiple columns to MySQL table using PHP loop

Question

My objective

To add a number of columns to a MySQL table. I have two concerns:

1) How to do this

2) Is this a bad idea, and if so why?

My reason for wanting to do this:

I have a form with a systematically named set of fields (e.g. field_1 field_2 etc). I want to post that data to a page and have it store it the mysql table, and I would like the fields to enter columns of a corresponding name (i.e. columns are named field_1 field_2 etc)

Rather than manually creating a column for each field name manually, it seemed obvious to loop the task.

However, research here and here seems tells me this approach illicits horror from people, and I found no actual solutions.

My attempt:

$mysqli = new mysqli("localhost","user","password","db");
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}    

$i = 1;
while ($i <= 14)
{
    $column = 'field_'.$i;
    $sql= '"ALTER TABLE supply ADD '.$column.' VARCHAR(45)"';
    $mysqli->query($sql);
    printf("Affected rows (ALTER): %d\n", $mysqli->affected_rows);
$i++;
}

This seems to run without error, however the affected rows message gives me (per loop, so 14 of these):

Affected rows (ALTER): -1

and the columns are not being added.

Can anyone advise me of my error, or a better way to debug what is going wrong? Additionally if this is an unwise thing to be doing could could you explain why and what I should consider doing instead? Thank you.

why you used both single and double quotes? '"ALTER TABLE supply ADD '.$column.' VARCHAR(45)"'? — user1646111
– user1646111, Commented Sep 10, 2013 at 17:31
Becuase the double quotes are (I think) a part of the query syntax, and the single quotes are bounding the definition of the variable. I was attempting to emulate this syntax as seen on php.net: $mysqli->query("ALTER TABLE Language ADD Status int default 0"); — Gideon
– Gideon, Commented Sep 10, 2013 at 17:38

sybear · Accepted Answer · 2013-09-10 17:37:47Z

3

Thats because affected_rows are set when you use only statements:

INSERT
UPDATE
REPLACE
DELETE

which work with rows, with data inside your table.

When you use ALTER your result should be true or false, because you do not work with data, you just edit the structure of your table.

$result = $mysqli->query($sql) ;
if ($result){
  echo "Table is updated. New column {$column} added" ;
}

Also, the correct SQL here would be:

$sql = "ALTER TABLE supply ADD {$column} VARCHAR(45) ; " ;

edited Sep 10, 2013 at 17:37

answered Sep 10, 2013 at 17:32

sybear

7,7921 gold badge25 silver badges38 bronze badges

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

4 Comments

Gideon Over a year ago

Ah ha, ok great thank you, I've made that adjustment and am getting no return now. My full syntax now looks like: ideone.com/1sHOzt Is this what you'd expect?

sybear Over a year ago

Well, I personally always use semicolons inside sql. What error do you get?

Gideon Over a year ago

Sorry - just deleted my comment as realised I had just typod something - your solution works perfectly, all columns now created. Thank you very much!

sybear Over a year ago

No prob, glad you figured that out!

Collectives™ on Stack Overflow

Adding multiple columns to MySQL table using PHP loop

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related