如何制作一个mysql表的副本，添加一些列并取出一些，更改顺序并保留与新表相关的任何数据？

Question

I want a wordpress user to be able to alter the structure of a database table, while retaining data in columns that are unchanged.

My strategy so far has been to:

1. Create a new table called table_temp with the new structure.
2. For each of the columns from the old table that exist in the new table, loop through the rows of the old table and insert values into the new table column.
3. Drop the old table and rename the new table as the old table.

Step 2 is where I go wrong so I have not bothered with step 3 yet. The first column works fine, but the values being inserted into the second retained column begin at the row following the last value inserted in the first.

function update_table($_POST) {
global $wpdb;

//collect the updated table's metadata from the $_POST


$slug = $_POST[slug].'_temp';
$WB_table = $_POST[slug];
unset($_POST[slug]);
unset($_POST[tblAppendGrid_rowOrder]);
unset($_POST[status]);

// create the new temp table
$wpdb->query("
CREATE TABLE $slug (
     id INT (6) NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (id)
     ) ENGINE=MyISAM;
");

//remove reference to the old meta in WB_table _meta
$wpdb->delete( 'WB_table_meta', array( 'WB_table' => $WB_table ) );

// add the new meta to WB_table _meta
// loop through the $_POST array
for($i=1;$i<=count($_POST)/5;$i++) {

// pull the array elemnts from $_POST
$name = $_POST['tblAppendGrid_name_'.$i];
$display = $_POST['tblAppendGrid_display_'.$i];
$type = $_POST['tblAppendGrid_type_'.$i];
$maxlength = $_POST['tblAppendGrid_maxlength_'.$i];
$width = $_POST['tblAppendGrid_width_'.$i];

// Add column names to the new table
$wpdb->query("ALTER TABLE ".$slug." ADD ".$name." text(160)");

// Add meta to WB_table_meta
$wpdb->insert('WB_table_meta',
               array(
                     'WB_table' => $WB_table,
                     'name' => $name,
                     'display' => $display,
                     'type' => $type,
                     'maxlength' => $maxlength,
                     'width' => $width),
               array('%s','%s','%s','%s','%s','%d','%d')
               );

//copy data to the new table from the old table if the column exists in the new table

if ($wpdb->query("SELECT ".$name." from ".$WB_table."", A_ARRAY) == true) {
     $rows = $wpdb->get_col("SELECT ".$name." from ".$WB_table."");

        foreach($rows as $row){
        $wpdb->query("INSERT INTO $slug ($name) VALUES ('$row')");
        $wpdb->show_errors();

        } //for each

} // if

} // for loop

} // function update_table

The new table created looks as shown below, however it should only have two rows.

14  kitchen NULL    NULL    NULL    NULL    NULL
15  kitchen NULL    NULL    NULL    NULL    NULL
16  NULL    laundry NULL    NULL    NULL    NULL
17  NULL    laundry NULL    NULL    NULL    NULL
18  NULL    NULL    office  NULL    NULL    NULL
19  NULL    NULL    office  NULL    NULL    NULL
20  NULL    NULL    NULL    8:00    NULL    NULL
21  NULL    NULL    NULL    8:00    NULL    NULL
22  NULL    NULL    NULL    NULL    8:10    NULL
23  NULL    NULL    NULL    NULL    8:10    NULL
24  NULL    NULL    NULL    NULL    NULL    8:15
25  NULL    NULL    NULL    NULL    NULL    8:15

Answer 1

Thanks VMai. Your suggestion worked great. Following code resulted in a new table with the correct fields.

// build the column string
if ($wpdb->query("SELECT ".$name." from ".$WB_table."", A_ARRAY) == true) {
     $cols .= $name.',';
     } // if
} // for loop


$col = substr($cols, 0, -1);

//copy data to the new table from the old table if the column exists in the new table
$wpdb->query("
     INSERT INTO ".$slug."
     (".$col.")
     SELECT ".$col."
     FROM ".$WB_table."
     ");
     $wpdb->show_errors();