How to insert data to a database table when the field is not yet created?

Question

Supposing I have an existing table(already created in dB) that handles user-registration (a plugin) and insert data using the $wpdB->insert like this:

        $success = $wpdb->insert(
            $wpdb->mytable,
            array(
        'first_name' => $first_name,
        'last_name' => $last_name,
        'email' => $email,
        'activation_key' => $activation_key,
            ), array('%s', '%s', '%s', '%s')
    );

Now my problem is I want to add another field to my existing table in dB. This field should have also have its own data. What would be the best practice to handle this type of situation? Do $wpdB->insert class creates a column automatically if its not there in the dB? For example, supposing I would like to add a hobby field name in the insert:

        $success = $wpdb->insert(
            $wpdb->mytable,
            array(
        'first_name' => $first_name,
        'last_name' => $last_name,
        'email' => $email,
        'hobby' => $hobby,            
        'activation_key' => $activation_key,
            ), array('%s', '%s', '%s', '%s')
    );

Would the "hobby" data be automatically inserted to the dB with a new column? If not, what would be the simplest and recommended method in doing this? Thanks for any help.

Answer 1

No, the column must exist before you insert data into it. Otherwise the query will fail.

You should edit your table creation SQL query to accommodate the new column. Then, run it through dbDelta() again. dbDelta() will compare your query and the table structure and only create the missing columns.

The best way to tell if the database structure is up-to-date is to store the plugin version in an option and compare it against the current plugin version.

Example:

class Awesome_Plugin {

    /** current plugin version */
    public $version = 4.2;

    function __construct() {
        $this->create_table();
    }

    function create_table() {
        global $wpdb;
        $installed_version = get_option( 'awesome_plugin_version' );

        if ( $installed_ver !== $this->version ) {

            $sql = "CREATE TABLE $table_name (
                id mediumint(9) NOT NULL AUTO_INCREMENT,
                time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
                name tinytext NOT NULL,
                text text NOT NULL,
                url VARCHAR(100) DEFAULT '' NOT NULL,
                UNIQUE KEY id (id)
            );";

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

            update_option( 'awesome_plugin_version', $this->version );
        }
    }
}

$GLOBALS['awesome_plugin'] = new Awesome_Plugin;

You can read more at the WordPress Codex