How to add a new table to database on plugin activation in WordPress

Question

I Tried to create table in database with the help of register_activation_hook() but its not working....

function my_plugin_activation(){
    global $wpdb , $table_prefix;

    $wp_emp = $table_prefix.'emp';

    $q = "CREATE TABLE IF NOT EXISTS` $wp_emp` (`id` INT NOT NULL , `name` VARCHAR(50) NOT NULL ,
    `Email` VARCHAR(100) NOT NULL , `status` VARCHAR NOT NULL ) ENGINE = InnoDB;";
    $wpdb -> query($q);
}

Answer 1

You need to add register_activation_hook in your plugin file to run any script during the activation of your plugin and call the dbDelta function to execute the sql command and include wp-admin/includes/upgrade.php. For example:

// Must include this line
require_once(ABSPATH.'wp-admin/includes/upgrade.php');

register_activation_hook(__FILE__, function() {
    global $wpdb;

    $charsetCollate = $wpdb->get_charset_collate();

    $table = $wpdb->prefix . 'emp';

    if ($wpdb->get_var("SHOW TABLES LIKE '$table'") != $table) {
        $sql = "CREATE TABLE $table (
            `id` INT(11) NOT NULL,
            `name` VARCHAR(50) NOT NULL,
            `Email` VARCHAR(100) NOT NULL,
            `status` VARCHAR(50) NOT NULL
        ) $charsetCollate;";
        dbDelta($sql);
    }
});