Unable to create new database table upon plugin activation using dbDelta

Question

Unable to create new database table upon plugin activation using dbDelta

Himanshu

2017年2月8日 10:57

I'm trying to create a new database table when my plugin is activated using dbDelta() however, no new table seems to be creating. Since I'm new to WordPress development, please let me know where I'm going wrong.

?php
/*
Plugin Name: Xenon-Result
Plugin URI:  https://developer.wordpress.org/plugins/the-basics/
Description: Basic WordPress Plugin Header Comment
Version:     1.0
Author:      Himanshu Gupta
Author URI:  https://developer.wordpress.org/
License:     GPL2
License URI: https://www.gnu.org/licenses/gpl-2.0.html
*/
function installer(){
    include('installer.php');
}
register_activation_hook( __file__, 'installer' ); //executes installer php when installing plugin to create new database

add_action('admin_menu','result_menu'); //wordpress admin menu creation
function result_menu()
{
    add_menu_page('Result','Result','administrator','xenon-result');
    add_submenu_page( 'xenon-result', 'Manage Marks', ' Manage Marks', 'administrator', 'Manage-Xenon-Marks', 'Xenon_Marks' );
}
function Xenon_Marks()
{
    include('new/result-add-marks.php');
}
?

This is the installer.php file:

?php
global $wpdb;
$table_name = $wpdb-prefix . "xenonresult";

$charset_collate = $wpdb-get_charset_collate();
if(!isset($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 );
}
?

Topic dbdelta database plugins Wordpress

Category Web

jamietelin · Accepted Answer · 2017年2月8日 10:57

Do you get any errors? Feels like it is just a SQL issue you have. Have you tested your SQL-query manually and see if it works?

I use the following and it works, I also set and check table version for future updates:

public function createDb()
{
         global $wpdb;

         $charsetCollate = $wpdb->get_charset_collate();

         require_once(constant('ABSPATH') . 'wp-admin/includes/upgrade.php');

         $tableName = $wpdb->prefix . 'mypluginname';
         $sql = "CREATE TABLE $tableName (
             id bigint(20) NOT NULL AUTO_INCREMENT,
             word varchar(255) DEFAULT '' NOT NULL,
             length int(9) DEFAULT 0 NOT NULL,
             UNIQUE KEY id (id)
         ) $charsetCollate;";
         dbDelta($sql);

         update_option('myplugin_db_version', constant('MYPLUGIN_DB_VERSION'));
}

Also at my plugin startup I do check db version, and update if needed.

public function __construct()
{
   $installedDbVersion = get_option("myplugin_db_version");
   $pluginDbVersion = constant('MYPLUGIN_DB_VERSION');
   if ((float)$installedDbVersion !== constant('MYPLUGIN_DB_VERSION') &&
      $installedDbVersion !== false
   ) {
    $this->createDb();
    $message = "Updated plugin tables from version ";
    $message .= "{$installedDbVersion} to {$pluginDbVersion}";
    trigger_error($message, E_USER_NOTICE);
   }
}

Dave Romsey · Accepted Answer · 2017年2月6日 10:24

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.

Unable to create new database table upon plugin activation using dbDelta

About