Passing array of strings to a SQL statement in a WordPress plugin

Question

$wpdb->prepare() accepts a single array of arguments as the second parameter, e.g.

$wpdb->prepare(
    "SELECT * FROM table WHERE field1 = %s AND field2 = %d AND field3 = %s",
    array( 'foo-bar', 123, '[email protected]' )
)

Therefore you can use an array of placeholders with the SQL command (the 1st parameter), like so:

// Create an array of placeholders, e.g. array( %s, %s, %s ) in your case.
$placeholders = implode( ', ', array_fill( 0, count( $the_array ), '%s' ) );

$db_options = $wpdb->get_col(
    $wpdb->prepare(
        // 1st parameter: The SQL command.
        "SELECT option_name
        FROM $wpdb->options
        WHERE option_name NOT IN ($placeholders)",

        // 2nd parameter: A single array of arguments.
        $the_array
    )
);

Additionally, note that if you pass an array like the $the_array above, then there must be no 3rd, 4th, 5th, etc. parameters; otherwise, you’ll get an error. So pass either individual arguments or a single array of all arguments, but never both!

// Good
$wpdb->prepare( "...", 'foo', 123, 'etc.' );
$wpdb->prepare( "...", array( 'foo', 123, 'etc.' ) );

// Bad - there is a 3rd parameter ('etc.')!
$wpdb->prepare( "...", array( 'foo', 123 ), 'etc.' );