How can I 'SELECT INTO' with unknown columns?

MySQL always throws an error when doing something like:

INSERT INTO `newtable` 
(`a`, `b`, `c`, `d`) 
SELECT 
`a`, `b`, `c` , `d` FROM `oldtable`

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.

What are your savvy ideas?

Note that the newtable is defined dynamically as mentioned above:

public function updateTableSchema($table, $fields)
{
    // Drop the temporary table if exists
    $sql = "
        DROP TABLE IF EXISTS `temp_{$table}`
    ";
    if (!$this->db()->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->db()->query($sql)) return FALSE;

    // Drop the old table
    if (!$this->db()->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->db()->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->db()->query($sql)) return FALSE;

    // Drop the temporary table
    $sql = "DROP TABLE `temp_{$table}`";
    return $this->db()->query($sql);
}

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 n开发者_高级运维ew table needs to also be in accordance with these changes.

UPDATE: Working solution follows:

public function updateTableSchema($table, $fields)
{
    // Drop the temporary table if exists
    $sql = "
        DROP TABLE IF EXISTS `temp_{$table}`
    ";
    if (!$this->db()->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->db()->query($sql)) return FALSE;

    // Drop the old table
    if (!$this->db()->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->db()->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->db()->query($sql);
    }
    catch (error $e)
    {
        if (preg_match('/Unknown column/', $e->getMessage()))
        {
            $new_field = utility::getStringBetween($e->getMessage(), "'", "'");
            if (!$new_field) return FALSE;

            $this->db()->query("TRUNCATE TABLE `$table`");
            $key = array_search($new_field, $fields);
            $key--;

            // Check if adding after
            if ($key > 0)
            {
                $sql = "
                    ALTER TABLE `temp_{$table}`
                    ADD `$new_field` VARCHAR( 255 ) NOT NULL AFTER `{$fields[$key]}` 
                ";
                if (!$this->db()->query($sql)) return FALSE;
            }
            // Check if adding before
            else
            {
                $sql = "
                    ALTER TABLE `temp_{$table}`
                    ADD `$new_field` VARCHAR( 255 ) NOT NULL FIRST
                ";
                if (!$this->db()->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->db()->query($sql)) return FALSE;
        }
    }

    // Drop the temporary table
    $sql = "DROP TABLE `temp_{$table}`";
    return $this->db()->query($sql);
}

---

Use Create Table...Select here is some reference

http://dev.mysql.com/doc/refman/5.0/en/create-table-select.html

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

CREATE TABLE new_tbl SELECT * FROM orig_tbl;