How to create a table in database using WordPress plugin

Question

I am trying to create a WordPress customize plugin and I'm stuck in creating a table in my database on activation of my plugin. I tried this code:

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

    $tblname = 'customer';
    $wp_track_table = $table_prefix . "$tblname ";

    #Check to see if the table exists already, if not, then create it

    if($wpdb->get_var( "show tables like '$wp_track_table'" ) != 
    $wp_track_table) 
    {

    $sql = "CREATE TABLE `". $wp_track_table . "` ( ";
    $sql .= "  `id`  int(11)   NOT NULL auto_increment, ";
    $sql .= "  `pincode`  int(128)   NOT NULL, ";
    $sql .= "  PRIMARY KEY `order_id` (`id`) "; 
    $sql .= ") ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; ";
    require_once( ABSPATH . '/wp-admin/includes/upgrade.php' );
    dbDelta($sql);
    }
}

register_activation_hook( __FILE__, 'create_plugin_database_table' );

When i tried to deactivate my plugin and activate again it won`t create a table in my database. — Leorah Sumarong
– Leorah Sumarong, Commented Jun 2, 2017 at 0:18

Sakura Kinomoto · Accepted Answer · 2017-06-02 00:35:17Z

3

First, I've changed a bit your creation table script. Read prefix from $wpdb, and do not check table, use CREATE IF NOT EXIST.

function create_plugin_database_table() {
  global $wpdb;

  $tblname = 'customer';
  $wp_track_table = $wpdb->prefix . "$tblname";

  $sql = "CREATE TABLE IF NOT EXISTS $wp_track_table ( ";
  $sql .= "  `id`  int(11)   NOT NULL auto_increment, ";
  $sql .= "  `pincode`  int(128)   NOT NULL, ";
  $sql .= "  PRIMARY KEY `order_id` (`id`) "; 
  $sql .= ") ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; ";
  require_once( ABSPATH . '/wp-admin/includes/upgrade.php' );
  dbDelta($sql);

}
register_activation_hook( __FILE__, 'create_plugin_database_table' );

Also, I've put the require out. If you need to insert some default values, do it AFTER create, not in the same request. If didn't work, please enable debug and check logs.

edited Jun 2, 2017 at 0:35

answered Jun 2, 2017 at 0:25

Sakura Kinomoto

1,8822 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Leorah Sumarong Over a year ago

There is an error: Plugin could not be activated because it triggered a fatal error. Fatal error: Call to undefined function dbDelta()

Leorah Sumarong Over a year ago

when i tried to change the require_once( ABSPATH . '/wp-admin/includes/upgrade.php' ); up to the dbDelta(sql); Activation success

Sakura Kinomoto Over a year ago

Sorry, I haven't readed correctly the require. It's correct to do the require before function dbDelta, because these function are defined on this file. Response updated.

Leorah Sumarong Over a year ago

HI Sakura will you assist me with my plugin?

Sakura Kinomoto Over a year ago

If you have another questions about how to do things, and always you have try, I can give any answer if I know how to help you. It's OK if you do more questions on SO

|

dvs.spy · Accepted Answer · 2021-12-30 07:25:58Z

There is the possibility of a conflict due to version compatibility. You can use the below snippet to check what version of the system has been used to avoid conflict.

function my_plugin_create_db() {

global $wpdb;
$version = get_option( 'my_plugin_version', '1.0' );
$charset_collate = $wpdb->get_charset_collate();
$table_name = $wpdb->prefix . 'my_analysis';

$sql = "CREATE TABLE $table_name (
    id mediumint(9) NOT NULL AUTO_INCREMENT,
    time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
    views smallint(5) NOT NULL,
    clicks smallint(5) NOT NULL,
    UNIQUE KEY id (id)
) $charset_collate;";

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

if ( version_compare( $version, '2.0' ) < 0 ) {
    $sql = "CREATE TABLE $table_name (
      id mediumint(9) NOT NULL AUTO_INCREMENT,
      time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
      views smallint(5) NOT NULL,
      clicks smallint(5) NOT NULL,
      blog_id smallint(5) NOT NULL,
      UNIQUE KEY id (id)
    ) $charset_collate;";
    dbDelta( $sql );

    update_option( 'my_plugin_version', '2.0' );        
}
}

Collectives™ on Stack Overflow

How to create a table in database using WordPress plugin

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related