0

I based this code from the codex on the docs. I placed it in my main plugin file but it's not creating the database table. Have I missed something?



    // Database setup and hooks
    
    function core_createdb() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'core_logs';
        $wpdb_collate = $wpdb->collate;
        $sql = 
            "CREATE TABLE {$table_name} (
            timestamp DATE NOT NULL,
            logid INT NOT NULL AUTO_INCREMENT,
            userid INT DEFAULT NULL,
            actiontype TINYTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'undefined',
            userip VARCHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'unknown',
            actioncontent VARCHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
            botactioncomment VARCHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
            botactionmade BOOLEAN DEFAULT '0',
            flaglevel TINYINT DEFAULT '0',
            PRIMARY KEY  (logid),
            KEY useridkey (userid)
            )
            COLLATE {$wpdb_collate}";
    
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    
        $success = empty($wpdb->last_error);
    
        return $success;
    }
    
    // Create Database
    
    add_action('init', 'core_createdb');

4
  • How do you know if the query ran successfully and there was no error? – mozboz Jul 19 '20 at 12:05
  • It does not show up in the database in phpmyadmin. I just enabled error logging and it says there is an error in my SQL. I went into phpmyadmin and created my query there and copied the generated SQL into my dbDelta, but I still get SQL syntax error. – FCD Jul 19 '20 at 12:14
  • Sounds like there's an SQL syntax error then. It's helpful to share specifically what the error is in your questoin. – mozboz Jul 19 '20 at 12:16
  • Here is the current SQL I'm using: $sql = "CREATE TABLE wp.wpcore_logs` ( logid INT NOT NULL AUTO_INCREMENT , userid INT NULL DEFAULT NULL , actiontype TINYTEXT NULL DEFAULT NULL , actioncontent VARCHAR NOT NULL , botactionmade BOOLEAN NOT NULL DEFAULT FALSE , botactioncomment VARCHAR NOT NULL , flaglevel INT NOT NULL , timestamp TIMESTAMP NOT NULL , userip VARCHAR NOT NULL , iplocation VARCHAR NOT NULL , PRIMARY KEY (logid))";` Error is here: pastebin.com/tKytesgF – FCD Jul 19 '20 at 12:19
0

Found a couple of things and am including what I believe will work to correct your issue. (As an aside, you should try and simplify your initial attempts so you can isolate what works and what doesn't. This is really complex for an initial attempt.)

One thing, you'll struggle to have a field named timestamp, because timestamp is an SQL Field Type. So when you have timestamp DATE NOT NULL it's actually confusing it by throwing two SQL field types at it instead of an field heading/name. I actually noticed this because of the colour formatting in SUBLIME TEXT 3 - both timestamp and DATE were the same colour.

Additionally, as per the codex:

Field types must be all lowercase (https://codex.wordpress.org/Creating_Tables_with_Plugins)

Here's my re-write attempt of your code:

function core_createdb() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'core_logs';
    $wpdb_collate = $wpdb->get_charset_collate();
    $sql = 
        "CREATE TABLE $table_name (
        time_stamp date NOT NULL,
        logid int NOT NULL AUTO_INCREMENT,
        userid int DEFAULT NULL,
        actiontype tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'undefined',
        userip varchar CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'unknown',
        actioncontent varchar CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
        botactioncomment varchar CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
        botactionmade boolean DEFAULT 0 NOT NULL,
        flaglevel tinyint(4) DEFAULT 0 NOT NULL,
        PRIMARY KEY  (logid),
        KEY useridkey (userid)
        ) $wpdb_collate;";   
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);
    $success = empty($wpdb->last_error);
    return $success;
}
// Create Database
add_action( 'init', 'core_createdb' );
1
  • Your suggestions seemed to fix it. Thanks! – FCD Jul 23 '20 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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