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
        );

 

Tagged in

One comment on “WordPress – How to use IN in WPDB PREPARE SQL statements

  1. This method does not seem to work in wordpress when using the prepare method to protect against sql injection attacks.

    WordPress gives the following errors all which seem to be caused by trying to pass the array called $values into the SQL query:

    Notice: wpdb::prepare was called incorrectly. Unsupported value type (array). Please see Debugging in WordPress for more information.

    Notice: wpdb::prepare was called incorrectly. The query does not contain the correct number of placeholders (6) for the number of arguments passed (3).

    Warning: mysqli_real_escape_string() expects parameter 2 to be string, array given.

Leave a Comment

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