Wordpress databse 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

I don't think wordpress intends for you to be micromanaging their database calls. They have a number of (more or less) convenient abstraction layers between the database and the application, and If you are using those and worrying about how they store the data then either there's a problem with the abstractions (where you should be doing all your work) or your familiarity with them. But their paradigm works pretty well for a lot of people the way it is.

Is there a particular problem you are trying to solve where their way of handling it (which might not have been yours or mine if we had designed it) doesn't work as they intended it to?

Answer 2

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