WordPress at Your Fingertips

$wpdb->update — array for $where pframeter

Specified value for $where field will turn into IN ( comma separated array values ) when queried.

You can't specify array for the parameter $where in $wpdb->update method, but it would be very convenient!

**Let me show you an example of what this is about.

Let's say we have an array with post IDs: [ 1, 5, 9 ] and we need all these posts update the field post_status, set there status draft.

Usually in WordPress this task is solved by $wpdb->query and writing a separate query:

$post_ids = [ 1, 5, 9 ];
$wpdb->query(
	"UPDATE $wpdb->posts SET post_status = 'draft'
	WHERE ID IN (". implode(',', array_map( 'intval', $post_ids ) ) .")"
);

As you can see from the example, the query is not very readable and you can easily make a mistake in it. And this is despite the fact that there is only one field to update, and the WHERE part is also only one field. Adding more fields makes the query even less readable.

It would be much more convenient if you could do it this way:

$post_ids = [ 1, 5, 9 ];
$wpdb->update( $wpdb->posts, [ 'post_status'=>'draft' ], [ 'ID'=>$post_ids ] );

I suggest a function wpdb_update()

The function completely replaces $wpdb->update() functionality and extend it. In it you can additionally specify an array as field values in the $where parameter.

I simplified the function: I removed format parameters from it. All passed data will be interpreted as strings. Numbers will automatically convert to numbers during SQL query. I haven't seen bugs with this approach yet, so I don't see a problem here.

/**
 * Update a row in the table
 *
 * Extends basic $wpdb->update to allow pass array in value of $where field array. Passed array become `IN ()` sql statement.
 *
 *     $wpdb->update( 'table', [ 'column' => 'foo', 'field' => 1337 ], [ 'ID' => [1,3,5] ] )
 *
 * @param string       $table        Table name
 * @param array        $data         Data to update (in column => value pairs).
 *                                   Both $data columns and $data values should be "raw" (neither should be SQL escaped).
 *                                   Sending a null value will cause the column to be set to NULL.
 * @param array        $where        A named array of WHERE clauses (column => value).
 *                                   value can be an array, it becomes `IN ()` sql statement in this case.
 *                                   Multiple clauses will be joined with ANDs.
 *                                   Both $where columns and $where values should be "raw".
 *                                   Sending a null value will create an IS NULL comparison.
 *
 * @return int|bool Number of rows affected/selected for all other queries. Boolean false on error.

 * @see wpdb::update() https://wp-kama.com/filecode/wp-includes/wp-db.php#L2214-2255
 *
 * @author Kama
 *
 * @ver 1.0
 */
function wpdb_update( $table, $data, $where ){
	global $wpdb;

	if ( ! is_array( $data ) || ! is_array( $where ) )
		return false;

	$SET = $WHERE = [];

	// SET
	foreach ( $data as $field => $value ) {
		$field = sanitize_key( $field );

		if ( is_null( $value ) ) {
			$SET[] = "`$field` = NULL";
			continue;
		}

		$SET[] = $wpdb->prepare( "`$field` = %s", $value );
	}

	// WHERE
	foreach ( $where as $field => $value ) {
		$field = sanitize_key( $field );

		if ( is_null( $value ) ) {
			$WHERE[] = "`$field` IS NULL";
			continue;
		}

		if( is_array($value) ){
			foreach( $value as & $val ){
				$val = $wpdb->prepare( "%s", $val );
			}
			unset( $val );

			$WHERE[] = "`$field` IN (". implode(',', $value) .")";
		}
		else
			$WHERE[] = $wpdb->prepare( "`$field` = %s", $value );
	}

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

	return $wpdb->query( $sql );
}

Now the problem described above can be solved with this code:

$post_ids = [ 1, 5, 9 ];
wpdb_update( $wpdb->posts, [ 'post_status'=>'draft' ], [ 'ID'=>$post_ids ] );

It's much more convenient and understandable.

No comments
    Log In