wpdb - CREATE TABLE with dbDelta does not create table

admin管理员组

文章数量:1327979

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');

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');

• wpdb
• sql
• dbdelta

Share Improve this question edited Jul 19, 2020 at 12:33 mozboz 2,62811 gold badge1212 silver badges2323 bronze badges asked Jul 19, 2020 at 10:32 FCDFCD 2344 bronze badges 4

• How do you know if the query ran successfully and there was no error? – mozboz Commented Jul 19, 2020 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 Commented Jul 19, 2020 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 Commented Jul 19, 2020 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/tKytesgF – FCD Commented Jul 19, 2020 at 12:19

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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/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' );

本文标签： wpdbCREATE TABLE with dbDelta does not create table