Last Updated:

$wpdb->update, where you can specify an array in the value of the $where parameter

 

The specified value for the $where field turn into when requested.IN ( array values separated by commas )

In WordPress, in the $wpdb->update method, you cannot specify an array in the field value in a parameter to become part of the query. And it's uncomfortable!$whereIN

I'll show you an example of what I'm talking about.

Let's say we have an array with the ID of the posts: and we need all these posts to update the field, set the status there.[ 1, 5, 9 ]post_statusdraft

Usually in WordPress, this task is solved through writing a separate query:$wpdb->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 particularly readable and you can easily make a mistake in it. And this is despite the fact that here you need to update only one field, and in the WHERE of the part also only one field is indicated. If you add more fields, the query becomes even less readable.

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

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

I offer a small function wpdb_update()

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

I've simplified the function by removing the format settings from there. All transmitted data is interpreted as strings. Numbers will automatically turn into numbers during a SQL query. I haven't seen any 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://yourdomain/filecode/wp-includes/wp-db.php#L2214-2255 * * @author codee * * @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 task described above can be solved with the following code:

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

So much more convenient and understandable.