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

[WordPress Trac]

#25559: Enhance prepare method to better support SQL IN operator
------------------------------------------------+--------------------------
 Reporter:  cannona                             |       Owner:
     Type:  enhancement                         |      Status:  new
 Priority:  normal                              |   Milestone:  Awaiting
Component:  Database                            |  Review
 Severity:  normal                              |     Version:  trunk
 Keywords:  has-patch 2nd-opinion dev-feedback  |  Resolution:
------------------------------------------------+--------------------------

Comment (by cannona):

 Thanks johnbillion for the well-thought-out response.

 Here is some of the reasoning behind the decisions I made in my patch:

 * Like the printf family of functions, you can pass additional arguments
 to prepare, and any extra arguments not accounted for in the format string
 will be ignored. So, this is why I required the number be specified in the
 placeholder; so the parser knows when to stop.
 * I'll grant that there is not a compelling use case for allowing arrays
 inside arrays, but it is more of a side-effect, and also gives the
 developer the flexibility to pass parameters in whatever way is most
 convenient.
 * You are correct that the patch doesn't allow for multiple variables in a
 single parameter, and everything is flattened, but again this is not a
 problem, because we know how many variables to process because of the
 counts.

 That being said, I think your suggestion could work just as well, if not
 better.  Here's how I understand that might work:

 * If the first placeholder in the format string is %#d %#f or %#s, and if
 the second parameter is an array, whose first element was an array, we
 would assume that the parameters are passed inside an array.
 * If the first placeholder is not %#d, %#f or %#s, and the second
 parameter was an array, we would again assume that the parameters are
 passed inside an array.
 * Otherwise, we assume that parameters were passed as parameters.
 * Either way, anywhere the placeholder starts with %#, we would expect to
 see an array at that position in the parameters list, whether that list of
 parameters was passed as an array, or as regular function parameters.
 * Empty arrays would cause their placeholders to simply be replaced with
 nothing, as I believe it is valid to write something like "SELECT a FROM b
 WHERE i IN ()".
 * In the docs, we'll want to discourage users from manually adding
 additional placeholders, like "WHERE c IN (%#s, %s)" unless they are
 certain that the array behind "%#s" will never be empty.

 Thoughts?  Am I missing something?

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