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.

Answer 1

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) ; " ;