Wordpress database insert() and update() - using NULL values

Question

Wordpress ships with the wpdb class which handles CRUD operations. The two methods of this class that I'm interested in are the insert() (the C in CRUD) and update() (the U in CRUD).

A problem arises when I want to insert a NULL into a mysql database column - the wpdb class escapes PHP null variables to empty strings. How can I tell Wordpress to use an actual MySQL NULL instead of a MySQL string?

Answer 1

If you want it to compatible you would have to SHOW COLUMN and determine ahead if NULL is allowed. If it was allowed then if the value was empty($v) use val = NULL in the query.

$foo = null;
$metakey = "Harriet's Adages";
$metavalue = "WordPress' database interface is like Sunday Morning: Easy.";

if ($foo == null) {
$wpdb->query( $wpdb->prepare( "
    INSERT INTO $wpdb->postmeta
    ( post_id, meta_key, meta_value, field_with_null )
    VALUES ( %d, %s, %s, NULL )", 
        10, $metakey, $metavalue ) );
} else {
$wpdb->query( $wpdb->prepare( "
    INSERT INTO $wpdb->postmeta
    ( post_id, meta_key, meta_value, field_with_null )
    VALUES ( %d, %s, %s, %s)", 
        10, $metakey, $metavalue, $foo ) );
}