WordPress.org

Support

Support » Plugins and Hacks » Bug fix: wpdb insert & update with null values

Bug fix: wpdb insert & update with null values

  • Hi,

    Some of you might have noticed but the wpdb’s insert & update methods don’t work well with null value (if you try to set null on a numeric field you’ll end up with 0)

    Here’s a fix for it I though I should share

    create a db.php file in your wp-content directory and drop this content in:

    EDIT:
    mmm, the code tag is really annoying in this forum thing..
    once you’ve pasted the code in db.php, replace all the code tags with a back tick.
    Alternatively you can download it from here:
    http://dl.dropbox.com/u/4012111/db.php

    <?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();
    		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."'";
    		}
    		$sql = "INSERT INTO <code>$table</code> (<code>&quot; . implode( '</code>,<code>', $fields ) . &quot;</code>) VALUES (" . implode( ",", $formatted_fields ) . ")";
    		return $this->query( $this->prepare( $sql, $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[] = "<code>$field</code> = 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[] = "<code>$field</code> = {$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[] = "<code>$field</code> = {$form}";
    		}
    
    		$sql = "UPDATE <code>$table</code> 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);

    Cheers

Viewing 6 replies - 1 through 6 (of 6 total)
  • mmm.. can’t seem to be able to update my own post.

    Just fixed a but in the fix (*roll eyes*).

    Updated the version in the dropbox link

    <?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 ) . ")";
    		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[] = "<code>$field</code> = 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[] = "<code>$field</code> = {$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[] = "<code>$field</code> = {$form}";
    		}
    
    		$sql = "UPDATE <code>$table</code> 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);

    Thanks for sharing. Very useful.

    Should be included in core.

    There is a small bug in your code, you forgot to do an array_shift in your block “$data[$field]===null”.

    Here is a the correct insert function. Do the same for the update function.

    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 ( !empty($format) )
    $form = ( $form = array_shift($formats) ) ? $form : $format[0];
    elseif ( isset($this->field_types[$field]) )
    $form = $this->field_types[$field];
    else
    $form = ‘%s’;

    if ($data[$field]===null) {
    $formatted_fields[] = ‘NULL’;
    }
    else {
    $formatted_fields[] = “‘”.$form.”‘”;
    $real_data[] = $data[$field];
    }
    }
    $sql = “INSERT INTO $table (" . implode( ',', $fields ) . ") VALUES (” . implode( “,”, $formatted_fields ) . “)”;
    return $this->query( $this->prepare( $sql, $real_data) );
    }

    Thanks you ! What a glaring oversight to begin with! Thank you!

    Super old.. I forgot about this post (shame WP forums cant send update emails)..

    I’ve fixed the source file in my dropbox

    The best way to report bugs like this is in trac – https://core.trac.wordpress.org/
    I’ve created a ticket here: #15158
    Now we just need a patch for the improvements 🙂

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Bug fix: wpdb insert & update with null values’ is closed to new replies.
Skip to toolbar