0

I have this function to create two tables when I activate the plugin. Only the wp_wpt_stats table are being created and not the wp_wpt_list. I know the dbdelta is picky, but I can't see what I'm doing wrong.

global $wpt_db_version;
$wpt_db_version = "1.0";

function wpt_install() {
    global $wpdb;
    global $wpt_db_version;

    $table_add_toplist = $wpdb->prefix . 'wpt_list';
    $table_add_wptstats = $wpdb->prefix . 'wpt_stats';
    $charset_collate = $wpdb->get_charset_collate();

    //-----------------------table_one-----------------------------------
        if($wpdb->get_var('SHOW TABLES LIKE ' . $table_add_toplist) != $table_add_toplist){
      $sql_one = 'CREATE TABLE ' . $table_add_toplist . '(
          id INT(11) UNSIGNED AUTO_INCREMENT,
          link_id INT(11),
          visitor_ip VARCHAR(15)
          click_at DATETIME NOT NULL,
          site_votes INT(11),
          on_page VARCHAR(255),
          UNIQUE KEY id (id)
    ) '. $charset_collate .';';


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

    //-----------------------table_two-----------------------------------
        if($wpdb->get_var('SHOW TABLES LIKE ' . $table_add_wptstats) != $table_add_wptstats){
      $sql_two = 'CREATE TABLE ' . $table_add_wptstats . '(
          id INT(11) UNSIGNED AUTO_INCREMENT,
          link_id INT(11),
          impressions INT(11),
          inclick INT(11),
          outclick INT(11),
          PRIMARY KEY  (id)
    ) '. $charset_collate .';';

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

    add_option("wpt_db_version", $wpt_db_version );
}
register_activation_hook(__FILE__,'wpt_install');
2
  • And you've confirmed dbdelta is executed? Replace it with an echo statement or something similar to verify it's not the if statement that's at fault. Also, please indent your code correctly, your editor should be auto-indenting for you
    – Tom J Nowell
    Commented Mar 3, 2015 at 14:18
  • Replaced with echo and got this on activation> The plugin generated 264 characters of unexpected output during activation. Commented Mar 3, 2015 at 14:20

1 Answer 1

0

dbdelta demands that:

  • You must put each field on its own line in your SQL statement.
  • You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
  • You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.
  • You must not use any apostrophes or backticks around field names.
  • Field types must be all lowercase.
  • SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.

If we look at your call:

$sql_one = 'CREATE TABLE ' . $table_add_toplist . '(
      id INT(11) UNSIGNED AUTO_INCREMENT,
      link_id INT(11),
      visitor_ip VARCHAR(15)
      click_at DATETIME NOT NULL,
      site_votes INT(11),
      on_page VARCHAR(255),
      UNIQUE KEY id (id)
) '. $charset_collate .';';

We can see that you do not append a comma to the end of every line:

      link_id INT(11),
      visitor_ip VARCHAR(15)
      click_at DATETIME NOT NULL,

So you have invalid SQL, as visitor_ip lacks a comma at the end

1
  • wow, can't believe I didn't see that :D thanks Commented Mar 3, 2015 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.