The challenges of providing WordPress 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.
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?
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]
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.
Further Reading:
