`$wpdb->prepare()` With an Array Param Returns Empty on WordPress 7.0

I was building out a listing feature on a multi-listing site, and one query that should have been trivial bit me hard: pull a set of rows from a custom table by a list of IDs. The classic IN (...). I had an array of IDs, I handed it to $wpdb->prepare(), then called get_col(). The result? An empty array. No error, no warning, no exception. Just empty. And I knew for a fact the rows existed, because I'd just run the same SELECT by hand in a MySQL client and watched every row come back.

Here's roughly the first thing I wrote:

$ids = array( 12, 47, 103 );
 
$sql = $wpdb->prepare(
    "SELECT id FROM $t WHERE pid IN (%s)",
    $ids
);
 
$rows = $wpdb->get_col( $sql );
// $rows => empty array(), even though pid 12, 47, 103 clearly exist

I tried a few variations: swapped %s for %d, passed $ids as a single argument, fiddled with the parentheses. Still empty. The maddening part was the total absence of red flags. PHP stayed quiet, WordPress stayed quiet, MySQL stayed quiet. The query ran fine, nothing just matched.

Why this happens

The root cause: $wpdb->prepare() doesn't know how to treat an array for a single placeholder. My assumption was wrong from the start. I figured that if I gave it %s and an array, it would be smart enough to expand the array into 12, 47, 103. That is not how it works.

What actually happens is that prepare() sees one %s placeholder and one array. It tries to substitute that array into the placeholder position, and the placeholder ends up malformed — not a tidy list of values, but something that never matches any real pid in the table. The SQL it produces is technically valid, so MySQL has no complaint. It simply returns zero rows, because no pid equals the garbage that came out of the substitution.

This is the real trap: a silent empty result. If prepare() had thrown an error, or even a warning, I'd have known immediately the placeholder was broken. But because it returns a valid SQL string and get_col() dutifully returns an empty array, the symptom is almost indistinguishable from "the data genuinely isn't there." I chased the database connection, the table name, even a stale cache before I realized the failure lived in the prepare() step.

The conceptual lesson that matters: prepare() is a one-to-one placeholder function. One placeholder binds one scalar value. An array is not a scalar value. For an IN clause, you have to handle the expansion yourself.

The fix

There are two approaches I use, and both produce safe SQL.

The first is to build the IN list manually while escaping each value with esc_sql():

$ids = array( 12, 47, 103 );
 
$in = "'" . implode( "','", array_map( 'esc_sql', $ids ) ) . "'";
 
$sql  = "SELECT id FROM $t WHERE pid IN ($in)";
$rows = $wpdb->get_col( $sql );

Here each ID is passed through esc_sql() and then joined with ',', so the final shape is '12','47','103'. Because every value is escaped, the clause is safe from injection even though I'm not routing this part through prepare().

The second approach, and the one I usually prefer when the IDs are guaranteed integers, is to generate exactly as many placeholders as the array has elements, then spread the array as the arguments:

$ids = array( 12, 47, 103 );
 
$ph  = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
// $ph => "%d,%d,%d"
 
$sql = $wpdb->prepare(
    "SELECT id FROM $t WHERE pid IN ($ph)",
    $ids
);
 
$rows = $wpdb->get_col( $sql );

The key is array_fill( 0, count( $ids ), '%d' ): it emits one %d per ID, so prepare() sees three placeholders and three values — the one-to-one pairing it actually understands. The $ids array is spread as the arguments, and each element binds to one placeholder. Once the placeholder count matches the value count, prepare() works exactly as expected and get_col() returns all three rows.

Pick %d when the IDs are guaranteed numeric (safest, and it forces the type), or %s when they can be strings. The one rule that can't bend: the number of placeholders must match the number of elements exactly.

The takeaway

$wpdb->prepare() does not natively take an array for a single placeholder. For an IN clause you have two paths: esc_sql() + implode() to assemble the list yourself, or build a matching run of %d/%s placeholders and spread the array as the arguments. Never concatenate raw IDs straight into SQL without one of those two safeguards.

And the deeper debugging lesson: a silent empty result is far sneakier than a noisy error. An error points at the problem; an empty result leaves you guessing. Next time a query returns zero rows when you're certain the data is there, don't suspect the data first — dump the final SQL that prepare() produced. Nine times out of ten, the answer is sitting right there in that malformed string.