WordPress – How to use IN in WPDB PREPARE SQL statements

In an SQL statement, the IN operator allows you to multiple values in a WHERE clause, for example:

SELECT * FROM 'table' WHERE 'field_id' = 1 AND 'field' IN ( 'value1', 'value2' )

When it comes to using this in WordPress things get a little more complicated when you need to pass it through the WPDB PREPARE method.

If the values for IN are known you can write them literally (as shown above), or if the number and type (string/digit) are known you can simply add them in as normal, for example:

$wpdb->prepare( "SELECT * FROM 'table' WHERE 'field_id' = %d AND 'field' IN ( %s, %s )", $field_id, 'value1', 'value2' );

But this is not often the case. Normally IN will be used to cross check against another set of data where the length is unknown.

The examples below show how to use the IN operator when using WPDB PREPARE.

Using the IN operator with digits (numbers)

Using the IN operator with numbers is a lot simplier because numbers are written without quotes.

For example ‘field’ IN ( 1, 2, 3 )

This means you can nest a second SQL statement and let SQL do it’s magic. For example:

$sql = $wpdb->prepare(
            " SELECT * FROM 'table'
                                WHERE 'field_id' = %d AND 'field' IN (
                                    SELECT 'field' FROM 'table_2'
                                )", $field_id
        );

Using the IN operator with strings (text)

Text is a little more complicated because each value needs quotes to show the start and end of the string.

For example, field IN ( ‘value1’, ‘value2’, ‘value 3’ )

To do this you need to start with your values in an array, for example

$values = array( 'value1', 'value2', 'value 3' );

then create a string with an %s for each value

$in_str_arr = array_fill( 0, count( $values ), '%s' );

and then use join to turn each %s into a comma separated string, for example

$in_str = join( ',', $in_str_arr );

This will make $in_str literally “%s, %s, %s”

And finally, add $in_str and your values into the WPDB PREPARE statement.

$sql = $wpdb->prepare(
            " SELECT * FROM 'table'
                                WHERE 'field_id' = %d AND 'field' IN (
                                   $in_str
                                )", $field_id, $values
        );

 

Full example:

$values = array( 'value1', 'value2', 'value 3' ); // start with the values in an array
$in_str_arr = array_fill( 0, count( $values ), '%s' ); // create a string of %s - one for each array value. This creates array( '%s', '%s', '%s' )
$in_str = join( ',', $in_str_arr ); // now turn it into a comma separated string. This creates "%s,%s,%s"
// now add into the SQL statement - BOTH where the each %s is meant to be an as a parameter to PREPARE
$sql = $wpdb->prepare(
            " SELECT * FROM 'table'
                                WHERE 'field_id' = %d AND 'field' IN (
                                   $in_str
                                )", $field_id, $values
        );