Unable to create new database table upon plugin activation using dbDelta

Here is an updated version of installer.php which does create the table when activating the plugin.

A check has been added to see if the custom table exists before proceeding with the creation of the new table.

A version number has been added too. It’s stored in the options table. This will help in the future if the database needs to be modified during an update.

<?php
    global $wpdb;
    $table_name = $wpdb->prefix . "xenonresult";
    $xenonresult_db_version = '1.0.0';
    $charset_collate = $wpdb->get_charset_collate();

    if ( $wpdb->get_var( "SHOW TABLES LIKE '{$table_name}'" ) != $table_name ) {

        $sql = "CREATE TABLE $table_name (
                id mediumint(9) NOT NULL AUTO_INCREMENT,
                `student-id` mediumint(9) NOT NULL,
                `student-name` text NOT NULL,
                `marks-obtained` int(9) NOT NULL,
                result text NOT NULL,
                PRIMARY KEY  (id)
        )    $charset_collate;";

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

There were a few issues with the original code:

if(!isset($table_name)){

This was always going to return true because $table_name was already set.

This line is missing a comma at the end:

id mediumint(9) NOT NULL AUTO_INCREMENT

Certain characters in table names require the table name to be enclosed in backticks, this includes hyphens:

student-id mediumint(9) NOT NULL,
student-name text NOT NULL,
marks-obtained int(9) NOT NULL,

To help with debugging, it’s a good idea to check the PHP error log when experiencing problems like this.

Related Posts: