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

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 c开发者_JAVA技巧lass escapes PHP null variables to empty strings. How can I tell Wordpress to use an actual MySQL NULL instead of a MySQL string?

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

Here's a solution to your problem. In "wp-content" folder, create a file named "db.php" and put this code in it:

<?php

// setup a dummy wpdb to prevent the default one from being instanciated
$wpdb = new stdclass();

// include the base wpdb class to inherit from
//include ABSPATH . WPINC . "/wp-db.php";


class wpdbfixed extends wpdb
{
    function insert($table, $data, $format = null) {
        $formats = $format = (array) $format;
        $fields = array_keys($data);
        $formatted_fields = array();
        $real_data = array();
        foreach ( $fields as $field ) {
            if ($data[$field]===null)
            {
                $formatted_fields[] = 'NULL';
                continue;
            }
            if ( !empty($format) )
                $form = ( $form = array_shift($formats) ) ? $form : $format[0];
            elseif ( isset($this->field_types[$field]) )
                $form = $this->field_types[$field];
            else
                $form = '%s';
            $formatted_fields[] = "'".$form."'";
            $real_data[] = $data[$field];
        }
        //$sql = "INSERT INTO <code>$table</code> (<code>&quot; . implode( '</code>,<code>', $fields ) . &quot;</code>) VALUES (" . implode( ",", $formatted_fields ) . ")";
        $sql = "INSERT INTO $table (" . implode( ',', $fields ) . ") VALUES (" . implode( ",", $formatted_fields ) . ")";
        return $this->query( $this->prepare( $sql, $real_data) );
    }

    function update($table, $data, $where, $format = null, $where_format = null)
    {
        if ( !is_array( $where ) )
            return false;

        $formats = $format = (array) $format;
        $bits = $wheres = array();
        $fields = (array) array_keys($data);
        $real_data = array();
        foreach ( $fields as $field ) {
            if ($data[$field]===null)
            {
                $bits[] = "$field = NULL";
                continue;
            }
            if ( !empty($format) )
                $form = ( $form = array_shift($formats) ) ? $form : $format[0];
            elseif ( isset($this->field_types[$field]) )
                $form = $this->field_types[$field];
            else
                $form = '%s';
            $bits[] = "$field = {$form}";

            $real_data[] = $data[$field];
        }

        $where_formats = $where_format = (array) $where_format;
        $fields = (array) array_keys($where);
        foreach ( $fields as $field ) {
            if ( !empty($where_format) )
                $form = ( $form = array_shift($where_formats) ) ? $form : $where_format[0];
            elseif ( isset($this->field_types[$field]) )
                $form = $this->field_types[$field];
            else
                $form = '%s';
            $wheres[] = "$field = {$form}";
        }

        $sql = "UPDATE $table SET " . implode( ', ', $bits ) . ' WHERE ' . implode( ' AND ', $wheres );

        return $this->query( $this->prepare( $sql, array_merge($real_data, array_values($where))) );
    }

}

$wpdb = new wpdbfixed(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
?>

In this way you can use null values with wpdb!

I find this on Wordpress StackExchange forum and it works very well for me.

// Add a filter to replace the 'NULL' string with NULL
add_filter( 'query', 'wp_db_null_value' );

global $wpdb;
$wpdb->update(
    'table',
    array( 
        'status' => 'NULL',
    ), 
    array( 'id' => 1 ) 
);

// Remove the filter again:
remove_filter( 'query', 'wp_db_null_value' );

and the function wp_db_null_value is:

/**
* Replace the 'NULL' string with NULL
* 
* @param  string $query
* @return string $query
*/

function wp_db_null_value( $query )
{
  return str_ireplace( "'NULL'", "NULL", $query ); 
}

Because in my case I cannot use $db->prepare() function...

wpdb insert() and update() works with NULL values, it was patched many years ago but never mentioned in the Codex.

In your case:

$wpdb->update(
    'table',
    array( 
        'status' => null,
    ), 
    array( 'id' => 1 ), 
    null, 
    '%d'
);

Ref: https://core.trac.wordpress.org/ticket/15158#no0

I tried to edit one of the other solutions listed here, because it resulted in the format array being misaligned with the data array, but failed.

Here is a solution that modifies the wpdb from the latest version of wordpress, in order to allow inserting and updating null values into SQL tables using insert() and update():

/*
 * Fix wpdb to allow inserting/updating of null values into tables
 */
class wpdbfixed extends wpdb
{
    function insert($table, $data, $format = null) {
        $type = 'INSERT';
        if ( ! in_array( strtoupper( $type ), array( 'REPLACE', 'INSERT' ) ) )
            return false;
        $this->insert_id = 0;
        $formats = $format = (array) $format;
        $fields = array_keys( $data );
        $formatted_fields = array();
        foreach ( $fields as $field ) {
            if ( !empty( $format ) )
                $form = ( $form = array_shift( $formats ) ) ? $form : $format[0];
            elseif ( isset( $this->field_types[$field] ) )
                $form = $this->field_types[$field];
            else
                $form = '%s';

            //***Steve Lee edit begin here***
            if ($data[$field]===null) {
                unset($data[$field]); //Remove this element from array, so we don't try to insert its value into the %s/%d/%f parts during prepare().  Without this, array would become shifted.
                $formatted_fields[] = 'NULL';
            } else {
                $formatted_fields[] = $form; //Original line of code
            }
            //***Steve Lee edit ends here***
        }
        $sql = "{$type} INTO `$table` (`" . implode( '`,`', $fields ) . "`) VALUES (" . implode( ",", $formatted_fields ) . ")";
        return $this->query( $this->prepare( $sql, $data ) );
    }

    function update($table, $data, $where, $format = null, $where_format = null)
    {
        if ( ! is_array( $data ) || ! is_array( $where ) )
            return false;

        $formats = $format = (array) $format;
        $bits = $wheres = array();
        foreach ( (array) array_keys( $data ) as $field ) {
            if ( !empty( $format ) )
                $form = ( $form = array_shift( $formats ) ) ? $form : $format[0];
            elseif ( isset($this->field_types[$field]) )
                $form = $this->field_types[$field];
            else
                $form = '%s';

            //***Steve Lee edit begin here***
            if ($data[$field]===null)
            {
                unset($data[$field]); //Remove this element from array, so we don't try to insert its value into the %s/%d/%f parts during prepare().  Without this, array would become shifted.
                $bits[] = "`$field` = NULL";
            } else {
                $bits[] = "`$field` = {$form}"; //Original line of code
            }
            //***Steve Lee edit ends here***
        }

        $where_formats = $where_format = (array) $where_format;
        foreach ( (array) array_keys( $where ) as $field ) {
            if ( !empty( $where_format ) )
                $form = ( $form = array_shift( $where_formats ) ) ? $form : $where_format[0];
            elseif ( isset( $this->field_types[$field] ) )
                $form = $this->field_types[$field];
            else
                $form = '%s';
            $wheres[] = "`$field` = {$form}";
        }

        $sql = "UPDATE `$table` SET " . implode( ', ', $bits ) . ' WHERE ' . implode( ' AND ', $wheres );
        return $this->query( $this->prepare( $sql, array_merge( array_values( $data ), array_values( $where ) ) ) );
    }

}
global $wpdb_allow_null;
$wpdb_allow_null = new wpdbfixed(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);

Insert this code into somewhere that always gets run, like your functions.php, and then use your new global $wpdb_allowed_null->insert() and ->update() as normal.

I preferred this method vs. overriding the default $wpdb, in order to preserve the DB behavior that the rest of Wordpress and other plugins will expect.