0

I'm working on a custom wordpress plugin, but I can't seem to figure out how to create the database when installing the plugin. (Or when updating)

When searching around this is what I came up with, but this doesn't seem to do anything.

function to_install() {
    global $wpdb;
    $sql = "CREATE TABLE to_issues (
        id int(11) NOT NULL AUTO_INCREMENT,
        name varchar(125) NOT NULL
    );";

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);
    add_option('to_db_version', TO_VERSION);
}
function to_install_data() {
    global $wpdb;
    $welcome_name = "Hello";
    $welcom_text = "Install successfull";
}
register_activation_hook(__FILE__, 'to_install');
register_activation_hook(__FILE__, 'to_install_data');

On a side note, when updating the plugin, will this reinstall all the tables? Or will wordpress handle this correctly?

1
  • to_install() is being executed, whenever I try to output something there, it's returning me an error, so for some reason it's not creating the table.
    – woutr_be
    Jun 27, 2012 at 8:56

1 Answer 1

2

Your SQL command is invalid. The error says:

WordPress database error: [Incorrect table definition; there can be only one auto column and it must be defined as a key] CREATE TABLE to_issues ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(125) NOT NULL )

Try:

$sql ="CREATE TABLE {$wpdb->prefix}to_issues (
    id int(11) unsigned NOT NULL auto_increment,
    name varchar(125) NOT NULL,
    PRIMARY KEY  (id)
    );";

Note that dbDelta() is very particular about what is passed to it. For instance you need two spaces between the PRIMARY KEY and (id).

I have added $wpdb->prefix to the table - this adds the (user specified) WordPress prefix. It's good practise to use this, as some people have more than one WordPress install in their database.

dbDelta

The 'register activation' is hook only fired when the plug-in is activated - but not when the plug-in is upgraded. So when a user deactivates and activates the plug-in, dbDelta($sql) is fired - this is intended. dbDelta (see source) recognises 'CREATE' queries, and first checks if the table already exists - and if it doesn't, it creates the table. If the table does exists, it checks that each of the fields match that in the given $sql, and if not, updates them. If the field doesn't exist, but is specified in your $sql, it is created.

This way you can easily update a table by chaging $sql. However, on updating a plug-in, to_install won't called - so you need to manually call it on admin_init after checking that the current 'database version' (TO_VERSION) is an old one. See this post for a detailed explanation of activation/deactivation/uninstall/update procedures..

3
  • Thanks a lot, this helped, I executed the SQL too, but I didn't get any error at that time. I also added the prefix later on, didn't realize I had to do this myself.
    – woutr_be
    Jun 28, 2012 at 5:47
  • I var_dumped the response from dbDelta, which gave the error. Jun 28, 2012 at 13:03
  • Aha, I just executed the SQL in phpMyAdmin, thanks for the suggestion, it's good for future use.
    – woutr_be
    Jun 29, 2012 at 2:24

Your Answer

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

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