Create table in database when activating plugin

Question

I am building a plugin and I am trying to create a table in the database when the plugin is activated.

I followed this guide and came up with this code:

function gg_create_table(){
    global $wpdb;

    $table_name = $wpdb->prefix . 'plugin';

    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $table_name (
      id mediumint(9) NOT NULL AUTO_INCREMENT,
      product_id mediumint(9) NOT NULL,
      product_name tinytext NOT NULL,
      product_description text NOT NULL,
      product_details text NOT NULL,
      product_url varchar(500) DEFAULT '' NOT NULL,
      product_category varchar(30) NOT NULL,
      product_categoy_url varchar(500) NOT NULL

      PRIMARY KEY  (id)
    ) $charset_collate;";

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

    add_option( 'plugin_db_version', '1.0' );
}

register_activation_hook( __FILE__, 'gg_create_table');

This code is found inside plugin.php.

But when I am activating the plugin, I am going to phpMyAdmin and I don't see the table, which means the table was not created.

Do I something in my code which is not right and might make the function throw an error?

How can I see the errors I am getting (if I am getting any)?

Thanks!

Answer 1

I think you're missing a comma after product_categoy_url varchar(500) NOT NULL

then if still not working, be aware that register_activation_hook( __FILE__, 'gg_create_table'); will work only if placed in the main plugin file, not in an included file.

To execute the activation function from a different file you've to define in the main file: define('MY_PLUGIN_PATH',__FILE__);

than you can use the constant in any point you want to run an activation function: register_activation_hook( MY_PLUGIN_PATH, 'gg_create_table');

I've elaborated your function to log errors and show notification in case of dbDelta failures:

function gg_create_table(){
  global $wpdb;
  $table_name = $wpdb->prefix . 'plugin';
  $charset_collate = $wpdb->get_charset_collate();

    $sql[] = "CREATE TABLE $table_name (
      id mediumint(9) NOT NULL AUTO_INCREMENT,
      product_id mediumint(9) NOT NULL,
      product_name tinytext NOT NULL,
      product_description text NOT NULL,
      product_details text NOT NULL,
      product_url varchar(500) DEFAULT '' NOT NULL,
      product_category varchar(30) NOT NULL,
      product_categoy_url varchar(500) NOT NULL 
      PRIMARY KEY  (id)
    ) $charset_collate;";
   //I've left the missing comma error after 'product_categoy_url varchar(500) NOT NULL' to test the failure

    //more queries here sql[]="......"

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

    $file=WPsCRM_DIR."/dbDeltaLog.txt";
    $messages=array();
    $hasErrors=false;
    foreach($sql as $q){
      $messages[]="Output printed on : ". date("Y-m-d h:i:sa")."\n";
      $messages[]=dbDelta( $q );// execute the query
      if($wpdb->last_error !== '') {
        $messages[]=$wpdb->last_error;
        $hasErrors=true;
      }
      else{
        $messages[]=$wpdb->last_query;
      }
    }
    ob_start();
    echo "XXXXXXX DB DELTA RESULT XXXXXX\n";
    foreach($messages as $message){
      if(is_array($message))
        foreach($message as $m)
          echo $m."\n";
      else
        echo $message."\n";
    }
    echo "XXXXXXXXXXXXXXXXXXXXX\n\n";
    file_put_contents ($file,ob_get_clean(),FILE_APPEND ); //log file wit all the information

    if($hasErrors==false)// add the option only if no error were thrown
      add_option( 'plugin_db_version', '1.0' ); 
    else{
      set_transient( 'myPluginFailed', true, 5 );
      // set a transient for the error notification once page reloaded 
      // it shouldn't happen of course
    }
}
register_activation_hook( WPsCRM_PATH, 'gg_create_table');   
add_action( 'admin_notices', 'myPluginActivationFailed' );

function myPluginActivationFailed(){
  if( get_transient( 'myPluginFailed' ) ){
        ?>
        <div class="notice notice-error">
           <p>MY Plugin installation failed, please contact the plugin author!</p>
        </div>
        <?php
        /* Delete transient, only display this notice once. */
        delete_transient( 'myPluginFailed' );
    }
}