title: Adding multiple columns to MySQL table using PHP loop
body: <p><strong>My objective</strong> </p>
  
  <p>To add a number of columns to a MySQL table. I have two concerns:</p>
  
  <p>1) How to do this </p>
  
  <p>2) Is this a bad idea, and if so why?</p>
  
  <p><strong>My reason for wanting to do this:</strong></p>
  
  <p>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)</p>
  
  <p>Rather than manually creating a column for each field name manually, it seemed obvious to loop the task.</p>
  
  <p>However, research <a href="http://forums.phpfreaks.com/topic/272434-while-loop-and-alter-mysql/" rel="nofollow noreferrer">here</a> and <a href="https://stackoverflow.com/questions/17844027/a-loop-for-adding-multiple-columns-into-a-table-in-mysql">here</a> seems tells me this approach illicits horror from people, and I found no actual solutions.</p>
  
  <p><strong>My attempt:</strong></p>
  
  <pre><code>$mysqli = new mysqli("localhost","user","password","db");
  if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
  }    
  
  $i = 1;
  while ($i &lt;= 14)
  {
      $column = 'field_'.$i;
      $sql= '"ALTER TABLE supply ADD '.$column.' VARCHAR(45)"';
      $mysqli-&gt;query($sql);
      printf("Affected rows (ALTER): %d\n", $mysqli-&gt;affected_rows);
  $i++;
  }
  </code></pre>
  
  <p>This seems to run without error, however the affected rows message gives me (per loop, so 14 of these):</p>
  
  <blockquote>
    <p>Affected rows (ALTER): -1</p>
  </blockquote>
  
  <p>and the columns are not being added.</p>
  
  <p>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.</p>