Search

Using $wpdb->prepare query for IN clauses

Listen to this article

Multiple PlaceholdersThe challenges of providing customization services are immense. Some requirements can be tougher than they initially appear. Mainly at times because the clients set some constraints. We had a use case, which was not simple to resolve because the database was not normalized, and we were not allowed to change it. So we needed a workaround while accessing certain data from the database.

But then we realized, that this workaround could help many people resolve the principle issue, which was, to set a query with a dynamic number of placeholders, in $wpdb->prepare. This would be a common use case if you wanted to use a $wpdb prepare for the IN clause.

Read about the use of placeholders to prevent any SQL injection.

[space]

Let’s understand this with an example

Consider a function, which accepts a string from the user. And based on that string we provide some information. Say, the user enters his favorite fruit and we have to return the color of that fruit. Our query would be,

[pre]// get_fav_fruit returns the fruit name as a string
$singleFavFruit = get_fav_fruit( );

// one fruit to be searched, returning one color result
$color = $wpdb->get_var($wpdb->prepare(“SELECT color FROM $fruitsTable WHERE fruit=%s”, $singleFavFruit));[/pre]

But, let’s say we want to allow the user to enter multiple favorite fruits. We have a function get_fav_fruits, which will return an array of strings.

[pre]// e.g. $multiple_fav_fruits = “apple”, “orange”, “watermelon”,…..
$multipleFavFruits = get_fav_fruits( );[/pre]

For such a function, we would not know the number of strings a user enters. How can we then set the number of placeholders for $multipleFavFruits?How do we solve this problem?

[space]

How do we solve this problem?

Well, once you know the solution it is fairly simple. You need to create an array of placeholders. Here’s how.

[pre]// Count the number of fruit names
$favFruitsCount = count($multipleFavFruits);

// Prepare the right amount of placeholders, in an array
// For strings, you would use, ‘%s’
$stringPlaceholders = array_fill(0, $favFruitsCount, ‘%s’);

// Put all the placeholders in one string ‘%s, %s, %s, %s, %s,…’
$placeholdersForFavFruits = implode(‘, ‘, $stringPlaceholders);[/pre]

Now, our query with multiple placeholders would be:

[pre]$query = “SELECT color FROM $fruitsTable WHERE fruit IN $placeholdersForFavFruits”;

// get the corresponding colors for the fruits
$mutlipleColors = $wpdb->get_results($wpdb->prepare($query, $multipleFavFruits));[/pre]

[space]
And there you have it! This ensures that our query is well sanitized and our arguments are prevented from being tampered with. This implementation is free of any for or while loops and is optimal.

In case you had any further questions, do let us know in the comment section below.

Ankit Shah

Ankit Shah

7 Responses

  1. `// e.g. $multiple_fav_fruits = “apple”, “orange”, “watermelon”,…..
    $multipleFavFruits = get_fav_fruits( );`

    $multipleFavFruits should be array, not comma separated string.

    Hope this helps.

    1. Hi Ankit, I have not quite understood your comment. Under the ‘How do we solve this problem’ heading we have explained exactly what you have mentioned in your comment.

      Is there something I am missing?

      1. In this line

        e.g. $multiple_fav_fruits = “apple”, “orange”, “watermelon”,…..

        variable $multiple_fav_fruits seems to be string and not array, making it array should make this solution perfect.

        Hope I am clear 🙂

  2. Took me a while to find one that actually addressed this specific topic. Thanks! and thanks to Ankit for his addition as well. Works!

  3. Also, don’t forget to enclose IN argument in parentheses:

    WHERE fruit IN %s, %s, %s — WRONG

    WHERE fruit IN (%s, %s, %s) — OK

  4. It’s not working for me. 🙁 I’m getting the correct # of items from the array in my IN (…) statement, but it doesn’t seem to like my array even though i’ve verified it has the values. They are numbers (ie. 3,5), so i’m using %d instead of %s and it is an array when I place it in my prepare statement. The only difference is, I also have a few other parameters in that prepare statement too. Could that be my issue? Should I append all my other parameters to the end of my array?

Leave a Reply

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

Get The Latest Updates

Subscribe to our Newsletter

A key to unlock the world of open-source. We promise not to spam your inbox.

Suggested Reads

WordPress Development

Custom WordPress Solutions, for You

LearnDash Development

Custom LearnDash Solutions, for You

WooCommerce Development

Custom WooCommerce Solutions, for You

WordPress Plugins

Scale your WordPress Business

WooCommerce Plugins

Scale your WooCommerce Business

LearnDash Plugins

Scale your LearnDash Business

Label

Title

Subtext

Label

Title

Subtext

Join our 55,000+ Subscribers

    The Wisdm Digest delivers all the latest news, and resources from the world of open-source businesses to your inbox.

    Suggested Reads

    WordPress Tips & Tricks
    Ketan Vyawahare

    How to Make Responsive Tables using CSS without Tag Read More »