Update table and drop column - mdhemalakhand1999/WordPressPluginDevelopment GitHub Wiki

Let’s create a option for store our plugin version

add_option("dbdemo_db_version", WPDEMO_DB_VERSION);

create a database row with column

$sql = "CREATE TABLE {$table_name} (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(250),
    email VARCHAR(250),
    PRIMARY KEY(id)
)";
require_once (ABSPATH. 'wp-admin/includes/upgrade.php');
dbDelta($sql);

If version not match then create same column but with extra “age” field

// add custom option for manage version of our plugin
add_option("dbdemo_db_version", WPDEMO_DB_VERSION);
// if option is update then add extra field into table
if(get_option('dbdemo_db_version') != WPDEMO_DB_VERSION) {
    $sql = "CREATE TABLE {$table_name} (
        id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(250),
        email VARCHAR(250),
        age INT,
        PRIMARY KEY(id)
    )";
    require_once (ABSPATH. 'wp-admin/includes/upgrade.php');
    dbDelta($sql);
    update_option('dbdemo_db_version', WPDEMO_DB_VERSION);
}

For every load, if plugin version changed, then remove this “age” column

// drop db if version not match
function wpdb_plugins_loaded() {
    global $wpdb;
    $table_name = $wpdb->prefix. "persons";
    if(get_post_meta("dbdemo_db_version") != WPDEMO_DB_VERSION) {
        $sql = "ALTER TABLE {$table_name} DROP COLUMN age";
        $wpdb->query($sql); 
    }
    update_option("dbdemo_db_version", WPDEMO_DB_VERSION);
}
add_action("plugins_loaded", "wpdb_plugins_loaded");

Here is full code

define("WPDEMO_DB_VERSION", "1.3");

function dbdemo_init() {
    // for deal with db we need first get $wpdb variable
    global $wpdb;
    // get wp table prefix
    $table_name = $wpdb->prefix. "persons";
    // sql query for create table and field
    $sql = "CREATE TABLE {$table_name} (
        id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(250),
        email VARCHAR(250),
        PRIMARY KEY(id)
    )";
    require_once (ABSPATH. 'wp-admin/includes/upgrade.php');
    dbDelta($sql);
    // add custom option for manage version of our plugin
    add_option("dbdemo_db_version", WPDEMO_DB_VERSION);
    // if option is update then add extra field into table
    if(get_option('dbdemo_db_version') != WPDEMO_DB_VERSION) {
        $sql = "CREATE TABLE {$table_name} (
            id INT NOT NULL AUTO_INCREMENT,
            name VARCHAR(250),
            email VARCHAR(250),
            age INT,
            PRIMARY KEY(id)
        )";
        require_once (ABSPATH. 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        update_option('dbdemo_db_version', WPDEMO_DB_VERSION);
    }
}
register_activation_hook(__FILE__, 'dbdemo_init');



// drop db if version not match
function wpdb_plugins_loaded() {
    global $wpdb;
    $table_name = $wpdb->prefix. "persons";
    if(get_post_meta("dbdemo_db_version") != WPDEMO_DB_VERSION) {
        $sql = "ALTER TABLE {$table_name} DROP COLUMN age";
        $wpdb->query($sql); 
    }
    update_option("dbdemo_db_version", WPDEMO_DB_VERSION);
}
add_action("plugins_loaded", "wpdb_plugins_loaded");
⚠️ **GitHub.com Fallback** ⚠️