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 ( version_compare( $installed_ver, $this->version, '!=' ) ) {

            $sql = "CREATE TABLE {$wpdb->prefix}awesome_plugin (
                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 );
        }
    }
}

$awesome_plugin = new Awesome_Plugin();

You can read more at the WordPress Codex

Answer 2

Another example:

/**
 * Register new database table
 */
add_action( 'init', 'register_litho_quiz_table', 1 );
add_action( 'switch_blog', 'register_litho_quiz_table' );
function register_litho_quiz_table() {
    global $wpdb;
    $wpdb->litho_quiz_results = "{$wpdb->prefix}quiz_results";
    $wpdb->litho_quiz_questions = "{$wpdb->prefix}quiz_questions";
    $wpdb->litho_quiz_choices = "{$wpdb->prefix}quiz_choices";
}

/**
 * Create new database table
 */
function create_quiz_tables() {
    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    global $wpdb;
    /*
     * The global $charset_collate contains the character set and
     * collation used by the native WordPress tables. Loosely, these
     * define the encodings of characters and how they are compared -
     * given that WordPress is used in many different languages it's
     * important to use the correct collation for your table.
     */
    global $charset_collate;
    // Call this manually as we may have missed the init hook
    register_litho_quiz_table();


    /*
     * log_id - the log ID.
     user_id - the user ID for whom the log corresponds.
     activity - the activity that occurred.
     object_id - the ID of the object (e.g. post ID, user ID, comment ID etc) that was the subject of the user's activity.
     object_type - the type of object (e.g. 'post', 'user', 'comment' etc).
     activity_date - the datetime of the activity.

    table
        --key--     --img(255)--     --string(255)--
        resultsid   resultsimg  resultsdescription
        resultsid   resultsimg  resultsdescription
    table
        --key-      --string--
        questionid  question
        questionid  question
        questionid  question
    table
    --key--    --foreign-- --foreign-- --img(255)-- --string(20)--
    choiceid    questionid  resultid    choiceimg   choicetitle
    choiceid    questionid  resultid    choiceimg   choicetitle
    choiceid    questionid  resultid    choiceimg   choicetitle
    choiceid    questionid  resultid    choiceimg   choicetitle
    choiceid    questionid  resultid    choiceimg   choicetitle
    choiceid    questionid  resultid    choiceimg   choicetitle
    choiceid    questionid  resultid    choiceimg   choicetitle
     */
    $sql_create_results = "CREATE TABLE {$wpdb->litho_quiz_results} (
        results_id int(2) unsigned NOT NULL auto_increment,
        results_img longblob NOT NULL,
        results_description varchar,
        PRIMARY KEY  (results_id)
    ) $charset_collate; ";
    dbDelta( $sql_create_results );

    $sql_create_questions = "CREATE TABLE {$wpdb->litho_quiz_questions} (
        question_id int(2) unsigned NOT NULL auto_increment,
        question_description varchar,
        PRIMARY KEY  (question_id)
    ) $charset_collate; ";
    dbDelta( $sql_create_questions );

    $sql_create_choices = "CREATE TABLE {$wpdb->litho_quiz_choices} (
        choice_id int(2) unsigned NOT NULL auto_increment,
        choice_img longblob,
        choice_description varchar,
        question_id int(2) NOT NULL,
        results_id int(2) NOT NULL,
        results_description varchar NOT NULL,
        PRIMARY KEY  (choice_id)
        FOREIGN KEY (question_id) REFERENCES items(question_id),
        FOREIGN KEY (results_id) REFERENCES items(results_id)
    ) $charset_collate; ";
    dbDelta( $sql_create_choices );
}
// Create tables on plugin activation
register_activation_hook( __FILE__, 'create_quiz_tables' );