Mirza Jhanzaib Mirza Jhanzaib - 4 months ago 8
SQL Question

Not able to create multiple custom db tables on activation of wp Plugin

i am unable to create multiple custom tables in db on activation of my wordpress plugin it creates only the last table like mentioned in this code is "Bookmark" instead of creating all tables (user,field,visibility,notify) etc

function your_plugin_options_install() {
global $wpdb;
//global $your_db_name;
$uservar = $wpdb->prefix . 'user';
$fieldvar = $wpdb->prefix . 'field';
$visibilitytypevar = $wpdb->prefix . 'visibily';
$notificationvar = $wpdb->prefix . 'notification';
$jobtypevar = $wpdb->prefix . 'jobtype';
$bookmarkvar = $wpdb->prefix . 'bookmark';
// $profiledatavar = $wpdb->prefix . 'profiledata';
//$employeevar = $wpdb->prefix . 'employee';
//echo "some";
// create the ECPT metabox database table
//if($wpdb->get_var("SHOW TABLES LIKE '$yourtable'") != $yourtable)
//{
$sql = "CREATE TABLE IF NOT EXISTS " . $uservar . " (
`u_id` int(20) NOT NULL AUTO_INCREMENT,
`u_name` varchar(30) ,
`u_phonenumber` int(20) ,
`u_email` varchar(30) NOT NULL,
`u_password` varchar(50) NOT NULL,
PRIMARY KEY (u_id)
);";
$sql = "CREATE TABLE IF NOT EXISTS " . $fieldvar . " (
`f_id` int(20) NOT NULL AUTO_INCREMENT ,
`f_title` varchar(30) NOT NULL,
PRIMARY KEY (f_id)
);";

$sql = "CREATE TABLE IF NOT EXISTS " . $visibilitytypevar . " (
`v_id` int(20) NOT NULL AUTO_INCREMENT ,
`v_type` bool NOT NULL,
PRIMARY KEY (v_id)
);";

$sql = "CREATE TABLE IF NOT EXISTS " . $notificationvar . " (
`n_id` int(20) NOT NULL AUTO_INCREMENT ,
`u_id` int(20) NOT NULL,
PRIMARY KEY (n_id)
);";

$sql = "CREATE TABLE IF NOT EXISTS " . $jobtypevar . " (
`jt_id` int(20) NOT NULL AUTO_INCREMENT ,
`jt_title` varchar(30),
PRIMARY KEY (jt_id)
);";

$sql = "CREATE TABLE IF NOT EXISTS " . $bookmarkvar . " (
`bm_id` int(20) NOT NULL AUTO_INCREMENT ,
`u_id` int(20) NOT NULL,
`bm_title` varchar(30),
PRIMARY KEY (bm_id)
);";


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

//}
// run the install scripts upon plugin activation
register_activation_hook(__FILE__,'your_plugin_options_install');

Answer

you are overriding your $sql variable. Just use dbDelta function after every query. like below format.

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

// your first query
$sql = '...'; 
dbDelta($sql);

// your second query
$sql = '...'; 
dbDelta($sql);

.
.
.


// your nth query
$sql = '...'; 
dbDelta($sql);