Enhance prepare method to better support SQL IN operator
|Reported by:||cannona||Owned by:|
|Component:||Database||Keywords:||has-patch 2nd-opinion dev-feedback|
When querying the database for rows that have fields that match any of several values, you typically use the IN operator. For example:
$wpdb->prepare("select a from b where c in (%d, %d, %d, %d)", $values);
I propose that the sprintf-like formatting allowed by the prepare method be extended to additionally accept markers such as "%#3s", which would be expanded to "%s, %s, %s". So, the previous example could be rewritten as:
$wpdb->prepare("select a from b where c in (%#4d)", $values);
To be more clear, the syntax I'm proposing is a "%", followed by a "#", followed by a 1 or more digit number specifying how many times to repeat the marker, followed by the type of the marker (d, s, or f). Other markers would continue to work as usual.
The other part of this proposal is that the requirements for the 2nd and all following parameters would be relaxed, so that the user could pass single values, arrays of values, or some combination of the above, and the arrays would be flattened into a single array of values. For example, the following calls to prepare would be equivalent:
$wpdb->prepare( $query, 1, 2, 3, 4, 5 ); $wpdb->prepare( $query, array( 1, 2, 3, 4, 5, ) ); $wpdb->prepare( $query, 1, array( 2, 3, ), 4, array(5,) ); // or even $wpdb->prepare( $query, array( 1, array( 2, ), 3, ), 4, 5 );
The reason I am making this proposal is that, while I do try to avoid raw SQL whenever practical, I often find myself forced to resort to it, and when I do, it is not uncommon for me to have to use the IN operator. So far, I've had to build my format string via a loop, which usually adds a placeholder for each item in an array. It's not a very complex bit of code, but because, at least in my experience, this situation is not super uncommon, it would be nice if the prepare method could make this a bit easier.
I have created a draft version of a patch which will add the above functionality.
Comments and questions are appreciated.
Change History (8)
- Summary changed from Enhance prepare method to better support SQL IN syntax to Enhance prepare method to better support SQL IN operator
comment:3 @johnbillion — 17 months ago
- Keywords 2nd-opinion dev-feedback added
- Type changed from feature request to enhancement