Support » Plugins » Executing more then one SQL from plugin function

  • Hi,
    I was playing with WP RC1 candidate and figured out that the Noteworthy plugin will not work with it because the database tables have changed in R3

    So while trying to make modifications to this plugin, I was faced with this issue.

    If you see the following code, only the first tabel is getting created and not the second one.

    Am I doing it right??

    function nw_setNoteworthyCategory() {
    	global $wpdb, $nw_options;
    
    	//$cat_name= wp_specialchars($nw_options['noteworthy_cat']);
    	//$id_result = $wpdb->get_row("SHOW TABLE STATUS LIKE '$wpdb->categories'");
    	$fav_db_version = "1.0";
    	global $fav_db_version;
    	 $table_name = $wpdb->prefix . "Favourite";
    	 $table_new = $wpdb->prefix . "FavPost2cat";
    	if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {
    		$sqlone = "CREATE TABLE " . $table_name . " (
    
    		'cat_ID' bigint(20) NOT NULL auto_increment,
    		'cat_name' varchar(55) NOT NULL default '',
    		 'category_nicename' varchar(200) NOT NULL default '',
    		 'category_description' longtext NOT NULL,
    		 'category_parent' bigint(20) NOT NULL default '0',
    		 'category_count' bigint(20) NOT NULL default '0',
    		 'link_count' bigint(20) NOT NULL default '0',
    		 'posts_private' tinyint(1) NOT NULL default '0',
    		 'links_private' tinyint(1) NOT NULL default '0',
    		  PRIMARY KEY  ('cat_ID'),
    		  KEY 'category_nicename' ('category_nicename')
    		  );";
    
    		  $sqltwo = "CREATE TABLE" . $table_new . " (
    			  'rel_id' bigint(20) NOT NULL auto_increment,
    			  'post_id' bigint(20) NOT NULL default '0',
    			  'category_id' bigint(20) NOT NULL default '0',
    			  PRIMARY KEY  ('rel_id'),
    			  KEY 'post_id' ('post_id','category_id')
    		  );";
    
    		  require_once(ABSPATH . 'wp-admin/upgrade-functions.php');
    		   dbdelta($sqlone);
    		  dbdelta($sqltwo);
    
    		//mysql_connect( DB_HOST, DB_USER, DB_PASSWORD );
    		//mysql_select_db( $table_prefix . DB_NAME );
    		//mysql_query( $sql );
    
    		  add_option("fav_db_version", $fav_db_version);
    	}
    
    	//$cat_ID = $id_result->Auto_increment;
    	//$category_nicename = sanitize_title($cat_name, $cat_ID);
    	//$category_description = $nw_options["cat_description"];
    
    	//$wpdb->query("INSERT INTO $wpdb->categories (cat_ID, cat_name, category_nicename, category_description, category_parent) VALUES ('0', '$cat_name', '$category_nicename', '$category_description', '0')");
    
    	return;
    }

Viewing 3 replies - 1 through 3 (of 3 total)
  • Hi ssdesign,

    create an array with the 2 SQL statements and give this to the dbDelte function.

    $sql= array($sqlone,$sqltwo);

    $changes = dbDelta($sql);

    var_dump($changes);

    Use var_dump to see what dbDelta had done or if there were any errors.

    Thread Starter ssdesign

    (@ssdesign)

    Thanks for your feedback.

    I tried it, oven then, only one table is getting created, the first one.

    Is there any error in this code?

    $sqltwo = "CREATE TABLE" . $table_new . " (
    			  'rel_id' bigint(20) NOT NULL auto_increment,
    			  'post_id' bigint(20) NOT NULL default '0',
    			  'category_id' bigint(20) NOT NULL default '0',
    			  PRIMARY KEY  ('rel_id'),
    			  KEY 'post_id' ('post_id','category_id')
    		  );";

    Hi,

    the dbDelta function is very strict about the SQL syntax. Please check wp-admin/includes/schema.php for a valid example. var_dumping what dbDelta did should give you more information what went wrong.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Executing more then one SQL from plugin function’ is closed to new replies.