Add new column to wordpress database

问题:

I trying update my plugin. So I must upgrade mysql_table. But when trying new column, program get exception.

This is my current table :

$sql = "CREATE TABLE  {$table_name} (
        say_id             int(11)   not null AUTO_INCREMENT,
        customer_mail      text      not null,
        customer_name  text      not null,
        customer_messagge      text      not null,
        messagge_date_time  datetime  not null,

        PRIMARY KEY (say_id)
        )ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";

        require_once(ABSPATH . "wp-admin/includes/upgrade.php");
        dbDelta($sql);

Now I am add colum more one table. I try Alter table, this working one time and add a column but one more refresh I get this error.

This is mycode

$wpdb->query("ALTER TABLE wp_customer_say ADD say_state INT(1) NOT NULL DEFAULT 1");

And this is my error

WordPress database error: [Duplicate column name 'say_state'] ALTER TABLE wp_customer_say ADD say_state INT(1) NOT NULL DEFAULT 1

I see this error and ı try this;

$query          = $wpdb->query("select * from wp_customer_say");
        $respond        = mysql_num_fields( $query );
        $column_array   = array();

        for($i = 0; $i < $respond ; $i++):
            $column_array[]     = mysql_field_name($query,$i);
        endfor;

        if( !in_array("say_state",$column_array) ):
            $wpdb->query("ALTER TABLE wp_customer_say ADD say_state INT(1) NOT NULL DEFAULT 1");
        endif;

and I get this error.

Warning: mysql_num_fields() expects parameter 1 to be resource, integer given in

Help please. Thank you. Sorry bad english.

回答1:

Use this query. I use only mysql-standred for get field name by fast query and this will solve your problem:

$row = $wpdb->get_results(  "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'wp_customer_say' AND column_name = 'say_state'"  );

if(empty($row)){
   $wpdb->query("ALTER TABLE wp_customer_say ADD say_state INT(1) NOT NULL DEFAULT 1");
}

回答2:

You can check column name exists in WordPress using below way,

$myCustomer = $wpdb->get_row("SELECT * FROM wp_customer_say");
//Add column if not present.
if(!isset($myCustomer->say_state)){
    $wpdb->query("ALTER TABLE wp_customer_say ADD say_state INT(1) NOT NULL DEFAULT 1");
}

回答3:

Just wanted to add that there's a slightly better way to do this than @Amandeep Wadhawan's answer.

register_activation_hook(__FILE__, 'install_tables');
function install_tables()
{
    update_options('my_plugins_current_db_version', 0);  // Replace 0 with whatever your final database version is
    // Code here to create the final version of your database tables
}

add_action('plugins_loaded', 'update_databases');
public function update_databases()
{
    global $wpdb;
    $prefix = $wpdb->prefix;
    $a_table_to_update = $prefix . $a_table_to_update;

    $current_version = get_option('my_plugins_current_db_version', 0);

    switch($current_version)
    {
        // First update
        case 0:
            // first update code goes here (alter, new table, whatever)
            $current_version++;
        case 1:
            // next update code goes here
            $current_version++;
    }

    update_option('my_plugins_current_db_version', $current_version);

}

You just have to make sure that your install_tables() function will always create the tables that reflect the final version number and sets the my_plugins_current_db_version option to the final version number.

Then in the update_databases() function you just have to make sure to increment $current_version before each subsequent case.

With this set-up you can blindly update without having to unnecessarily query to find out if columns or tables exist first - and less queries are always a good thing....especially if your update code is firing on the plugins_loaded hook.

It is also much cleaner, shows a clear upgrade path, and only executes necessary updates - so it is more efficient.

回答4:

I really like Rikesh's option (even upvoted!), but I think to prevent hardcoding information which could change, such as $table_prefix from the wp-config.php file, this option is a safer bet.

// Replace `table_name` with the actual table name
$table = $table_prefix.'table_name';

// And use sprintf to pass the table name
// Alternatively, you can use $table instead of sprintf, 
//     if you use double quotes such as shown here
$myCustomer = $wpdb->get_row( sprintf("SELECT * FROM %s LIMIT 1", $table) );

// If you prefer not using sprintf, make sure you use double quotes
// $myCustomer = $wpdb->get_row( "SELECT * FROM $table LIMIT 1" );

// Replace "missing_field" by the column you wish to add
if(!isset($myCustomer->missing_field)) {
    $wpdb->query( sprintf( "ALTER TABLE %s ADD phone2 VARCHAR(255) NOT NULL", $table) );

    // Alternate example using variable
    // $wpdb->query( "ALTER TABLE $table ADD phone2 VARCHAR(255) NOT NULL") );
}