WordPress.org

Forums

Bug fix: wpdb insert & update with null values (7 posts)

  1. doginthehat
    Member
    Posted 5 years ago #

    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

  2. doginthehat
    Member
    Posted 5 years ago #

    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);
  3. calvin_42
    Member
    Posted 5 years ago #

    Thanks for sharing. Very useful.

    Should be included in core.

  4. calvin_42
    Member
    Posted 5 years ago #

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

  5. maximus2010
    Member
    Posted 4 years ago #

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

  6. doginthehat
    Member
    Posted 4 years ago #

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

    I've fixed the source file in my dropbox

  7. 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 :-)

Topic Closed

This topic has been closed to new replies.

About this Topic