Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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?

share|improve this question
    
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 '12 at 8:56
add comment

1 Answer 1

up vote 2 down vote accepted

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..

share|improve this answer
    
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 '12 at 5:47
    
I var_dumped the response from dbDelta, which gave the error. –  Stephen Harris Jun 28 '12 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 '12 at 2:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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