Bug fix: wpdb insert & update with null values

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.www.ads-software.com/
I’ve created a ticket here: #15158
Now we just need a patch for the improvements ??