title: How can I 'SELECT INTO' with unknown columns?
body: <p>MySQL always throws an error when doing something like:</p>
  
  <pre><code>INSERT INTO `newtable` 
  (`a`, `b`, `c`, `d`) 
  SELECT 
  `a`, `b`, `c` , `d` FROM `oldtable`
  </code></pre>
  
  <p>when field a and c does not exist in 'newtable'. Of course, I understand this is a legitimate error. What I want to know is, is there any way to build something like a case statement for handling this. The query is dynamically built based on a dynamic table, so there is no way for me to know up front what fields exist.</p>
  
  <p>What are your savvy ideas?</p>
  
  <p>Note that the newtable is defined dynamically as mentioned above:</p>
  
  <pre><code>public function updateTableSchema($table, $fields)
  {
      // Drop the temporary table if exists
      $sql = "
          DROP TABLE IF EXISTS `temp_{$table}`
      ";
      if (!$this-&gt;db()-&gt;query($sql)) return FALSE;
  
      // Make a backup of the original table and select the old values into it
      $sql = "
          CREATE TABLE `temp_{$table}`
          SELECT * FROM `$table`
      ";
      if (!$this-&gt;db()-&gt;query($sql)) return FALSE;
  
      // Drop the old table
      if (!$this-&gt;db()-&gt;query("DROP TABLE `$table`")) return FALSE;
  
      // Recreate the table with the new fields
      $sql = "
          CREATE TABLE IF NOT EXISTS `$table`
          (\n";
  
          $sql .= "`id` int(11) NOT NULL AUTO_INCREMENT,\n";
          foreach ($fields as $field)
          {
              $sql .= html_entity_decode("`$field` varchar(255) NOT NULL,\n");
          }
          $sql .= "PRIMARY KEY (`id`)\n";
  
       $sql .= "
          ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
      ";
      if (!$this-&gt;db()-&gt;query($sql)) return FALSE;
  
      CREATE TABLE new_tbl SELECT * FROM orig_tbl;
  
      // Insert the temporary records into the new table
      $sql = "INSERT INTO `$table` (";
      foreach ($fields as $field)
      {
          $sql .= html_entity_decode("`$field`, ");
      }
      $sql = rtrim($sql, ', ') . ') SELECT ';
      foreach ($fields as $field)
      {
          $sql .= html_entity_decode("`$field`, ");
      }
      $sql = rtrim($sql, ', ') . " FROM `temp_{$table}`"; 
      print $sql;
      if (!$this-&gt;db()-&gt;query($sql)) return FALSE;
  
      // Drop the temporary table
      $sql = "DROP TABLE `temp_{$table}`";
      return $this-&gt;db()-&gt;query($sql);
  }
  </code></pre>
  
  <p>The original table here is updated based on the form fields which exist on a given form that may be added to, deleted from or renamed at any time. The new table needs to also be in accordance with these changes.</p>
  
  <p><strong>UPDATE:</strong>
  Working solution follows:</p>
  
  <pre><code>public function updateTableSchema($table, $fields)
  {
      // Drop the temporary table if exists
      $sql = "
          DROP TABLE IF EXISTS `temp_{$table}`
      ";
      if (!$this-&gt;db()-&gt;query($sql)) return FALSE;
  
      // Make a backup of the original table and select the old values into it
      $sql = "
          CREATE TABLE `temp_{$table}`
          SELECT * FROM `$table`
      ";
      if (!$this-&gt;db()-&gt;query($sql)) return FALSE;
  
      // Drop the old table
      if (!$this-&gt;db()-&gt;query("DROP TABLE `$table`")) return FALSE;
  
      // Recreate the table with the new fields
      $sql = "
          CREATE TABLE IF NOT EXISTS `$table`
          (\n";
  
          $sql .= "`id` int(11) NOT NULL AUTO_INCREMENT,\n";
          foreach ($fields as $field)
          {
              $sql .= html_entity_decode("`$field` varchar(255) NOT NULL,\n");
          }
          $sql .= "PRIMARY KEY (`id`)\n";
  
       $sql .= "
          ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
      ";
      if (!$this-&gt;db()-&gt;query($sql)) return FALSE;
  
      // Insert the temporary records into the new table
      $sql = "INSERT INTO `$table` (";
      foreach ($fields as $field)
      {
          $sql .= html_entity_decode("`$field`, ");
      }
      $sql = rtrim($sql, ', ') . ') SELECT ';
      foreach ($fields as $field)
      {
          $sql .= html_entity_decode("`$field`, ");
      }
      $sql = rtrim($sql, ', ') . " FROM `temp_{$table}`";
  
      try
      {
          $this-&gt;db()-&gt;query($sql);
      }
      catch (error $e)
      {
          if (preg_match('/Unknown column/', $e-&gt;getMessage()))
          {
              $new_field = utility::getStringBetween($e-&gt;getMessage(), "'", "'");
              if (!$new_field) return FALSE;
  
              $this-&gt;db()-&gt;query("TRUNCATE TABLE `$table`");
              $key = array_search($new_field, $fields);
              $key--;
  
              // Check if adding after
              if ($key &gt; 0)
              {
                  $sql = "
                      ALTER TABLE `temp_{$table}`
                      ADD `$new_field` VARCHAR( 255 ) NOT NULL AFTER `{$fields[$key]}` 
                  ";
                  if (!$this-&gt;db()-&gt;query($sql)) return FALSE;
              }
              // Check if adding before
              else
              {
                  $sql = "
                      ALTER TABLE `temp_{$table}`
                      ADD `$new_field` VARCHAR( 255 ) NOT NULL FIRST
                  ";
                  if (!$this-&gt;db()-&gt;query($sql)) return FALSE;
              }
  
              // Insert the temporary records into the new table
              $sql = "INSERT INTO `$table` (";
              foreach ($fields as $field)
              {
                  $sql .= html_entity_decode("`$field`, ");
              }
              $sql = rtrim($sql, ', ') . ') SELECT ';
              foreach ($fields as $field)
              {
                  $sql .= html_entity_decode("`$field`, ");
              }
              $sql = rtrim($sql, ', ') . " FROM `temp_{$table}`";
              if (!$this-&gt;db()-&gt;query($sql)) return FALSE;
          }
      }
  
      // Drop the temporary table
      $sql = "DROP TABLE `temp_{$table}`";
      return $this-&gt;db()-&gt;query($sql);
  }
  </code></pre>