Extended placeholders for SQL, e.g. WHERE id IN (??)
I like the basic idea behind your proposal, but dislike the "naming" of the placeholders. I basically have two objections:
- Your placeholders start either with
?. You should choose one form, so a placeholder may be immediately recognized. I would choose
?because it has less possible collisions with SQL and is more common for denoting placeholders.
- The placeholders are hard to understand and hard to remember.
:|seem plausible to me, but distinguishing
:is quite hard.
I changed my DB class to support some more placeholders and be more intelligent: DB_intelligent.php (the part of the README about placeholders doesn't apply to this class. It is only for the normal class.)
The DB class has two kinds of placeholders: The multifunctional
? placeholder and the associative array placeholder
x may be
? placeholder: This placeholder determines the type of insertion from the type of the argument:
null => 'NULL''string' => 'string'array('foo', 'bar') => ('foo','bar')
?x placeholder: Every element in the array is converted to a
`field`='value' structure and imploded with a delimiter. The delimiter is specified by the
, delimits by comma,
DB::x( 'UPDATE table SET ?, WHERE value IN ? AND ?&', array('foo' => 'bar'), array('foo', 'bar'), array('hallo' => 'world', 'hi' => 'back'));// Results in this query:// UPDATE table SET `foo`='bar' WHERE value IN ('foo','bar') AND `hallo`='world' AND `hi`='back'
Some thoughts I had while designing this version of the DB class:
An obvious thought that may arise: Why not use
? for all types of data, even associative arrays. Only add
?| additionally. Using
? on an associative array would be same as using
?, in the current design. The reason why I did not do this is security. You often want to insert data from a
<select multiple> into the query (
IN ?). But as HTML allows arraying (
form[array]) form controls also an associative array with the same name may be submitted. Thus my Query Compositor would recognize it as a field => value list. Even though this probably would not harm security it would result in a SQL error which is bad.