Subscribe to Shoultes.netNews FeedSubscribe to Shoultes.netComments

Browse > Home / My Plugins, MySQL, Open Source, PHP Code Samples, PHP Developer News, PHP Everywhere, Wordpress, Wordpress Plugins / Function for Creating Tables with Wordpress Plugins

Function for Creating Tables with WordPress Plugins

1

Here is a very useful function I have written to install/update the database tables in your custom WordPress plugin. Basically I have used the examples given on the “Creating Tables with Plugins” page at WordPress.org.

In your main plugin file (ex. my_plugin.php) I define my plugin version:

define("MY_PLUGIN_VERSION", "2.16" ); //Declare the plugin version. This way we know the tables are always up to date. I usually declare this in my main plugin file.
require_once("includes/functions.php");
require_once("includes/database_install.php");
register_activation_hook(__FILE__,'my_plugin_data_tables_install');

Then in my functions.php file:

function my_plugin_run_install ($table_name, $table_version, $sql) {
		   global $wpdb;
		   $wp_table_name = $wpdb->prefix . $table_name;
		   if($wpdb->get_var("SHOW TABLES LIKE '".$table_name."'") != $table_name) {
				$sql_create_table = "CREATE TABLE " . $wp_table_name . " ( " . $sql . " ) ;";
				require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
				dbDelta($sql_create_table);
 
			//create option for table version
				$option_name = $table_name.'_tbl_version';
				$newvalue = $table_version;
				  if ( get_option($option_name) ) {
					    update_option($option_name, $newvalue);
					  } else {
					    $deprecated=' ';
					    $autoload='no';
					    add_option($option_name, $newvalue, $deprecated, $autoload);
				  }
			//create option for table name
				$option_name = $table_name.'_tbl';
				$newvalue = $wp_table_name;
				  if ( get_option($option_name) ) {
					    update_option($option_name, $newvalue);
					  } else {
					    $deprecated=' ';
					    $autoload='no';
					    add_option($option_name, $newvalue, $deprecated, $autoload);
				  }
		}
 
	// Code here with new database upgrade info/table Must change version number to work.
	$installed_ver = get_option( $table_name.'_tbl_version' );
	     if( $installed_ver != $table_version ) {
		  $sql_create_table = "CREATE TABLE " . $wp_table_name . " ( " . $sql . " ) ;";
	      require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
	      dbDelta($sql_create_table);
	      update_option( $table_name.'_tbl_version', $table_version );
	      }
	    }

Using the function is quite simple and can save a few lines of code. especially if you need to install several new tables.

Here is an example of database_install.php:

function my_plugin_data_tables_install () {
$table_version = MY_PLUGIN_VERSION; //Call the plugin version.
//Install the first table
$table_name = "my_first_plugin_tbl";
$sql = "id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(100) NOT NULL,
	  UNIQUE KEY id (id)";
my_plugin_run_install  ($table_name, $table_version, $sql);
 
//Install the second table
$table_name = "my_second_plugin_tbl";
$sql = "id mediumint(9) NOT NULL AUTO_INCREMENT,
	   category_name VARCHAR(100) DEFAULT NULL,
	   category_identifier VARCHAR(45) DEFAULT NULL,
	   category_desc TEXT,
	   display_desc VARCHAR (4) DEFAULT NULL,
	  UNIQUE KEY id (id)";
my_plugin_run_install  ($table_name, $table_version, $sql);
}

I hope this helps some of the WordPress plugin authors out there.

Posted by Seth on Sunday, March 7, 2010 at 12:41 am 
Filed under My Plugins, MySQL, Open Source, PHP Code Samples, PHP Developer News, PHP Everywhere, Wordpress, Wordpress Plugins · Tagged with , , , , ,

Comments

One Response to “Function for Creating Tables with WordPress Plugins”

Trackbacks

Check out what others are saying about this post...
  1. Wordpress dbDelta Function Problems | Shoultes.net says:
    March 7, 2010 at 12:44 am

    [...] weren’t getting updated when the plugin was installed or activated. I had recently written a function (based on this example) to create tables in my WordPress plugin. I finally narrowed it down to the [...]



Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!

You must be logged in to post a comment.