Using $wpdb prepare and IN argument

The problem is that the IN phrase is taking a variable number of parameters and the prepare statement is not matching the array to it.

In your example you are presenting the list of three post ID numbers in code, you are sourcing it yourself, in this case you could just code it yourself in a string and incorporate it into prepare as one argument. BUT I suspect that your real use sources the ID numbers from potentially unsafe sources, hence the need for prepare. IF this is the case you I would loop through the array and assemble the list like this:`
$alist = ”;
$sep = ”;
foreach( $i in $array) {
$alist .= $wpdb->prepare(‘%s %d’, $sep, $i );
$sep = ‘,’;
}
$id = $wpdb->get_var(
$wpdb->prepare(”
SELECT ID
FROM {$wpdb->prefix}posts
LEFT JOIN {$wpdb->prefix}postmeta
ON {$wpdb->prefix}postmeta.post_id={$wpdb->prefix}posts.ID
WHERE ID IN ($alist)
AND meta_key = %s
AND meta_value = %d
“,
$key, $value
)
);`
You need to sort out what is expected if the list is empty.

You went to the trouble to make the placeholders for the array, but then used the array_list in the prepare statement. Prepare wants a variable for each placeholder.
https://developer.www.ads-software.com/reference/classes/wpdb/prepare/

All placeholders MUST be left unquoted in the query string. A corresponding argument MUST be passed for each placeholder.

@rossmitchell tried your suggestion but the ID is no longer being retrieved and still getting an error. when I look at debug.log the content of $alist looks like this:-
”,’ 5′,’ 9′,’ 19′

@joyously if I’ve understood you correctly, you’re saying that instead of $array_list in the prepare statement, I should have a unique variable returning each of the IDs in $array meaning my prepare statement should look like this?

$id = $wpdb->get_var( 
   	$wpdb->prepare("
   		SELECT ID
   		FROM {$wpdb->prefix}posts
   		LEFT JOIN {$wpdb->prefix}postmeta
                ON {$wpdb->prefix}postmeta.post_id={$wpdb->prefix}posts.ID
   		WHERE ID IN ($placeholder_list)
   		AND meta_key = %s
   		AND meta_value = %d
   		",
   		$id1, $id2, $id3, $key, $value
   	)
);

If so, how do I create the required number of unique variables? In my example, I’ve hard-coded the values in the array. In practice, the array is dynamically created and could contain more or fewer and different IDs.

Curious that the numbers are being quoted. Suggest minor change to:

$alist = '';
 $sep = '%d';
 foreach( $i in $array ) {
  $alist .= $wpdb->prepare($sep, $i );
  $sep = ', %d';
}

• This reply was modified 3 years, 10 months ago by RossMitchell.

Your task is much simpler than you are making it. You don’t ever need to pass integer data through $wpdb->prepare()! All that’s needed is to cast the integer data as integers, and the integer data is fully sanitized. So your code can be reduced to the following, and it’s completely safe:

$array = array('5', '9', '15');
$array_list = implode(',', array_map('intval', $array));
$key = 'custom-field-name';
$value = (int) 'custom field value';

$sql = "SELECT p.ID FROM {$wpdb->prefix}posts p
	LEFT JOIN {$wpdb->prefix}postmeta m ON (m.post_id = p.ID)
	WHERE p.ID IN ($array_list)
	AND m.meta_key = %s
	AND m.meta_value = $value";

$id = $wpdb->get_var($wpdb->prepare($sql, $key));

Having said this, I don’t think your query will be returning the data you expect, but that’s not what you asked. ??

For some reason, even though I checked the “notify me” box when I replied, I didn’t then get notified.

@diondesigns
I’m very pleased to report your code executes without error and returns the expected value so thank you very much for your input. You’ve helped solve something that’s been bugging me for a while now.

@rossmitchell
thank you for your suggested tweak although it hasn’t changed anything. One thing I forgot to mention in my last response is that foreach($i in $array) produces an unexpected T_STRING error. Changing it to foreach($array as $i) removes the T_STRING error but still doesn’t produce the expected result overall. The code @diondesigns provided works so I’ll close this now although I’m still puzzled as to why it’s not achievable using my original format – maybe I’ll never know!