Using PHP to create two tables in the WordPress database when plugin is activated

Question

I am having a problem with my plugin that is suppose to create a table in the WordPress database when it is activated. My current code is as follows:

register_activation_hook(__FILE__, 'wp_table_install');

function wp_table_install(){
global $wpdb;
global $db_version;

$sql ="CREATE TABLE IF NOT EXISTS 'st_support_tickets'
('id' mediumint(8) unsigned NOT NULL auto_increment,
'ticket_id' varchar(36) NOT NULL,
'ticket_user_id' varchar(36) NOT NULL,
'ticket_description' TEXT default NULL,
'ticket_priority' varchar(255) default NULL,
'ticket_status' varchar(255) default NULL,
'ticket_type' varchar(255) default 'Private',
PRIMARY KEY ('id')) 
AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS 'st_support_priorities' 
('id' mediumint(8) unsigned NOT NULL auto_increment,
'ticket_user_id' varchar(36) NOT NULL,
PRIMARY KEY ('id'))
AUTO_INCREMENT=1;
";

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

What is stopping these tables from being created? Is it the sql query or the syntax or what?

Ahh yes I forgot to mention this error when the plugin is activated: "The plugin generated 619 characters of unexpected output during activation. If you notice “headers already sent” messages, problems with syndication feeds or other issues, try deactivating or removing this plugin." — mgrantnz
– mgrantnz, Commented May 1, 2015 at 4:49

mgrantnz · Accepted Answer · 2015-05-03 10:50:27Z

1

To solve this problem I remove all ' characters from the sql statements excluding around default values. Eg:

$sql1 ="CREATE TABLE IF NOT EXISTS  st_support_tickets 
(id  mediumint(8) unsigned NOT NULL auto_increment,
 ticket_user_id  varchar(36) NOT NULL,
 ticket_description TEXT,
 ticket_priority  varchar(255) default NULL,
 ticket_status  varchar(255) default NULL,
 ticket_type  varchar(255) default  'Private',
PRIMARY KEY ( id ))
AUTO_INCREMENT=1;
";

$sql2 = "CREATE TABLE IF NOT EXISTS st_support_priorities 
( id  mediumint(8) unsigned NOT NULL auto_increment,
 ticket_priority  varchar(36) NOT NULL,
PRIMARY KEY ( id ))
AUTO_INCREMENT=1;
";

Had no problems after this.

answered May 3, 2015 at 10:50

mgrantnz

3751 gold badge4 silver badges15 bronze badges

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

Comments

Prakash · Accepted Answer · 2015-05-01 04:51:03Z

0

register_activation_hook(__FILE__, 'wp_table_install');

function wp_table_install(){
global $wpdb;
global $db_version;

$sql ="CREATE TABLE IF NOT EXISTS 'st_support_tickets'
('id' mediumint(8) unsigned NOT NULL auto_increment,
'ticket_id' varchar(36) NOT NULL,
'ticket_user_id' varchar(36) NOT NULL,
'ticket_description' TEXT,
'ticket_priority' varchar(255) default NULL,
'ticket_status' varchar(255) default NULL,
'ticket_type' varchar(255) default 'Private',
PRIMARY KEY ('id')) 
AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS 'st_support_priorities' 
('id' mediumint(8) unsigned NOT NULL auto_increment,
'ticket_user_id' varchar(36) NOT NULL,
PRIMARY KEY ('id'))
AUTO_INCREMENT=1;
";

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

answered May 1, 2015 at 4:51

Prakash

1717 bronze badges

3 Comments

mgrantnz Over a year ago

It seems like all you did was remove the "default NULL" from the ticket_description field. This didn't do anything.

mgrantnz Over a year ago

this is the error: "The plugin generated 607 characters of unexpected output during activation. If you notice “headers already sent” messages, problems with syndication feeds or other issues, try deactivating or removing this plugin."

David Over a year ago

you have a echo somewhere in your activation hook or attached functions

Yatendra · Accepted Answer · 2015-05-01 04:55:54Z

0

Try with this

$sql1 ="CREATE TABLE IF NOT EXISTS 'st_support_tickets'
('id' mediumint(8) unsigned NOT NULL auto_increment,
'ticket_id' varchar(36) NOT NULL,
'ticket_user_id' varchar(36) NOT NULL,
'ticket_description' TEXT,
'ticket_priority' varchar(255) default NULL,
'ticket_status' varchar(255) default NULL,
'ticket_type' varchar(255) default 'Private',
PRIMARY KEY ('id')) 
AUTO_INCREMENT=1";

$sql2 ="CREATE TABLE IF NOT EXISTS 'st_support_priorities' 
('id' mediumint(8) unsigned NOT NULL auto_increment,
'ticket_user_id' varchar(36) NOT NULL,
PRIMARY KEY ('id'))
AUTO_INCREMENT=1;
";

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

answered May 1, 2015 at 4:55

Yatendra

1,3361 gold badge18 silver badges31 bronze badges

1 Comment

mgrantnz Over a year ago

I get this error: The plugin generated 318 characters of unexpected output during activation. If you notice “headers already sent” messages, problems with syndication feeds or other issues, try deactivating or removing this plugin.

Aman Maurya · Accepted Answer · 2015-05-30 19:35:32Z

Tested and works fine..

register_activation_hook(__FILE__, 'wp_table_install');

function wp_table_install(){
    global $wpdb;
    global $db_version;

    $charset_collate = $wpdb->get_charset_collate();

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

    $sql ="CREATE TABLE IF NOT EXISTS st_support_tickets (
          id mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
          ticket_id varchar(36) DEFAULT '' NOT NULL,
          ticket_user_id varchar(36) DEFAULT '' NOT NULL,
          ticket_description TEXT DEFAULT '' NOT NULL,
          ticket_priority varchar(255) DEFAULT '' NOT NULL,
          ticket_status varchar(255) DEFAULT '' NOT NULL,
          ticket_type varchar(255) DEFAULT 'Private' NOT NULL,
          PRIMARY KEY (id)) $charset_collate;";

    dbDelta( $sql );

    $sql="CREATE TABLE IF NOT EXISTS st_support_priorities ( 
         id mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
         ticket_user_id varchar(36) DEFAULT '' NOT NULL,
         PRIMARY KEY (id)) $charset_collate;";

    dbDelta($sql);

    add_option("db_version", $db_version);
}

Collectives™ on Stack Overflow

Using PHP to create two tables in the WordPress database when plugin is activated

4 Answers 4

Comments

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related