For a couple of weeks I was trying to figure out why the database tables in my plugin 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 dbDelta function for WordPress. After doing a few searches on Google I came across this article which explains the the dbDelta function in detail.
Come to find out I was missing a space between a ‘“‘ and a ‘(‘ as seen below.
$sql_create_table = "CREATE TABLE " . $wp_table_name . "( " . $sql . " );";
Here is how it should have looked:
$sql_create_table = "CREATE TABLE " . $wp_table_name . " ( " . $sql . " ) ;";
Notice the spaces highlighted in green? That was the killer. So for a while, every time I added a new field to a table in database install file. For a while I was using a function (seen below) to alter the table and add the new fields.
function add_column_if_not_exist($db, $column, $column_attr = "VARCHAR( 255 ) NULL" ){ global $wpdb; $exists = false; $columns = $wpdb->query("show columns from $db"); while($c = $wpdb->get_row($columns)){ if($c['Field'] == $column){ $exists = true; break; } } if(!$exists){ if (!$wpdb->query("ALTER TABLE `$db` ADD `$column` $column_attr")){ $error = 'There was a problem adding columns to the database.'; } } return $error; }
So, if you are having trouble with the dbDelta function when writing a Wrodpress plugin. Be aware of extra spaces 
Here is more information about the dbDelta function and creating tables with plugins:
http://codex.wordpress.org/Creating_Tables_with_Plugins
http://wordpress.org/tags/dbdelta-1
http://hungred.com/how-to/wordpress-dbdelta-function/
http://designoplasty.com/2009/05/15/not-using-dbdelta-with-wordpress/
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.
