WPDB Insert automatically changing string format to number

The third parameter to wpdb::insert() is an array of formats that defaults to %s unless they are overridden in wpdb::$field_types. This is last part is very important. When WordPress boots, it statically sets that list to fields that should always be numbers unless someone specifies an explicit format as the third parameter. Currently there these 34 in the file (I was tripped up by object_id which my system is storing as a string).

    'post_author'      => '%d',
    'post_parent'      => '%d',
    'menu_order'       => '%d',
    'term_id'          => '%d',
    'term_group'       => '%d',
    'term_taxonomy_id' => '%d',
    'parent'           => '%d',
    'count'            => '%d',
    'object_id'        => '%d',
    'term_order'       => '%d',
    'ID'               => '%d',
    'comment_ID'       => '%d',
    'comment_post_ID'  => '%d',
    'comment_parent'   => '%d',
    'user_id'          => '%d',
    'link_id'          => '%d',
    'link_owner'       => '%d',
    'link_rating'      => '%d',
    'option_id'        => '%d',
    'blog_id'          => '%d',
    'meta_id'          => '%d',
    'post_id'          => '%d',
    'user_status'      => '%d',
    'umeta_id'         => '%d',
    'comment_karma'    => '%d',
    'comment_count'    => '%d',
    // multisite:
    'active'           => '%d',
    'cat_id'           => '%d',
    'deleted'          => '%d',
    'lang_id'          => '%d',
    'mature'           => '%d',
    'public'           => '%d',
    'site_id'          => '%d',
    'spam'             => '%d',

You might be tempted to do something like wpdb->insert('table', $values, ['object_id' => '%s']) but that won’t work, either. The logic for looking up a format by columns is only used for the core fields, which you can see here.

If everything to be inserted is a string (or should be inserted as a string) then you can just pass %s or [%s] as the third parameter (the former gets converted to the latter) and WordPress will use it for items.

Unfortunately, if anything else needs to be inserted in a non-string way, you need to explicitly set each placeholder.

(Okay, technically, if your first parameter is a string, you only need to provide formats up till the last non-string, because the logic for missing placeholders is to use the first provided one, but that’s just begging to either break or get yourself punched, possibly by your future self.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.