[wp-trac] [WordPress Trac] #25559: Enhance prepare method to better support SQL IN syntax

[WordPress Trac]

#25559: Enhance prepare method to better support SQL IN syntax
-----------------------------+-----------------------------
 Reporter:  cannona          |      Owner:
     Type:  feature request  |     Status:  new
 Priority:  normal           |  Milestone:  Awaiting Review
Component:  Database         |    Version:  trunk
 Severity:  normal           |   Keywords:
-----------------------------+-----------------------------
 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.

--
Ticket URL: <http://core.trac.wordpress.org/ticket/25559>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software