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
:or 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.
:&and:|seem plausible to me, but distinguishing??,:?and: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 (x may be ,, & or |).
? 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 x component: , delimits by comma, & by AND and | by OR.
Example code:
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 ?& and ?| 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.
You might want to avoid using := as a placeholder because it already has a usage in for example MySQL.
See for example this answer for a real world usage.