Generating Dynamic Placeholders for $wpdb->prepare()

$wpdb->prepare() is often called with each un-sanitized value explicitly passed as an individual argument; for example:

$wpdb->prepare( "SELECT id FROM wp_posts WHERE id > %d AND `post_status` = %s", $min_id, $status )

The function will also accept an array of un-sanitized values, though, like this:

$wpdb->prepare( "SELECT id FROM wp_posts WHERE id > %d AND `post_status` = %s", array( $min_id, $status ) )

That can be useful in certain circumstances, like when you have a multi-dimensional array where each sub-array contains a different number of items, and so you need to build the placeholders dynamically:

foreach ( $new_status_post_id_map as $new_status => $wordcamp_ids ) {
	$wordcamp_id_placeholders = implode( ', ', array_fill( 0, count( $wordcamp_ids ), '%d' ) );
	$prepare_values           = array_merge( array( $new_status ), $wordcamp_ids );

	$wpdb->query( $wpdb->prepare( "
		UPDATE `$table_name`
		SET `post_status` = %s
		WHERE ID IN ( $wordcamp_id_placeholders )",
	) );

So if a sub-array has 2 items, then $wordcamp_id_placeholders will be '%d, %d', and if the next array has 4 items, then its placeholder string would be '%d, %d, %d, %d'.

Leave a Reply

Your email address will not be published. Required fields are marked *