I’m having an issue getting some indices created when I create my tables for a new WordPress plugin.
Here is the code:
global $wpdb;
$em_posts = $wpdb->prefix . "em_collab_posts";
$em_groups = $wpdb->prefix . "em_collab_groups";
$em_users = $wpdb->prefix . "em_collab_users";
if($wpdb->get_var("SHOW TABLES LIKE '$em_users'") != $em_users) {
$sql = "CREATE TABLE IF NOT EXISTS `$em_users` (
`cuID` bigint(20) NOT NULL,
`groupID` bigint(20) NOT NULL,
`userID` bigint(20) NOT NULL,
`cuType` int(11) NOT NULL,
`cuSettings` longtext NOT NULL,
`cuCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`cuUpdated` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;";
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
// Create the table
dbDelta($sql);
// Add the indices
dbDelta("ALTER TABLE `$em_users`
ADD PRIMARY KEY (`cuID`), ADD KEY `groupID` (`groupID`), ADD KEY `userID` (`userID`), ADD KEY `cuType` (`cuType`); ");
dbDelta("ALTER TABLE `$em_users`
MODIFY `cuID` bigint(20) NOT NULL AUTO_INCREMENT;");
}
if($wpdb->get_var("SHOW TABLES LIKE '$em_posts'") != $em_posts) {
$sql = "CREATE TABLE IF NOT EXISTS `$em_posts` (
`cpID` bigint(20) NOT NULL,
`groupID` bigint(20) NOT NULL,
`post_id` bigint(20) NOT NULL,
`cpSettings` longtext NOT NULL,
`cpCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`cpUpdated` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
// Create the table
dbDelta($sql);
// Add the indices
dbDelta("ALTER TABLE `$em_posts`
ADD PRIMARY KEY (`cpID`), ADD KEY `groupID` (`groupID`), ADD KEY `post_id` (`post_id`);");
dbDelta("ALTER TABLE `$em_posts`
MODIFY `cpID` bigint(20) NOT NULL AUTO_INCREMENT;");
}
if($wpdb->get_var("SHOW TABLES LIKE '$em_groups'") != $em_groups) {
$sql = "CREATE TABLE IF NOT EXISTS `$em_groups` (
`groupID` bigint(20) NOT NULL,
`gTitle` varchar(50) NOT NULL,
`gDescription` longtext NOT NULL,
`gSettings` longtext NOT NULL,
`gCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`gUpdated` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
// Create the table
dbDelta($sql);
// Add the indices
dbDelta("ALTER TABLE `$em_groups`
ADD PRIMARY KEY (`groupID`), ADD KEY `gTitle` (`gTitle`);");
dbDelta("ALTER TABLE `$em_groups`
MODIFY `groupID` bigint(20) NOT NULL AUTO_INCREMENT;");
}
I also tried it with the ALTER TABLE
statements in with the actual creation, the tables themselves get created, but the indices do not.
What am I doing wrong? Or is there a different way to do this?
Needed to change the initial query’s
Updated Code – Works