D 2014-03-20T06:39:31.793
L db
N text/x-markdown
P ec8f5257646c7335955d180f5162e87fee77b668
U mario
W 3522
<h2>db()</h2>
<kbd>[db.php](artifact/0f8ae0eb995299394f4015b047658c5c9c098f53)</kbd> is a lightweight wrapper for PDO. It allows some flexibility with passing scalar parameters, and adds new placeholders for array binding.
<h3>Placeholders</h3>
<table width=95%>
<tr><th>List params</th> <th>Expanded</th> <th>Usage</th></tr>
<tr><td><code>?</code></td> <td><code>?</code></td> <td>A single scalar value (as in PDO).</td></tr>
<tr><td><code>??</code></td> <td><code>?, ?, ?</code></td> <td>List of scalars.</td></tr>
<tr><th>Named params</th> <th> </th> <th> </th></tr>
<tr><td><code>:name</code></td> <td><code>:name</code></td> <td>Named parameter (as in PDO).</td></tr>
<tr><td><code>::</code></td> <td><code>:a, :b, :c</code></td> <td>Extracts named array into list of named placeholders.</td></tr>
<tr><td><code>:&</code></td> <td><code>`x`=:x AND `y`=:y</code></td> <td>Named column = :param comparison conjoined with <code>AND</code>.</td></tr>
<tr><td><code>:|</code></td> <td><code>`x`=:x OR `y`=:y</code></td> <td>Named Named column = :param comparison conjoined with <code>OR</code>.</td></tr>
<tr><td><code>:,</code></td> <td><code>`x`=:x, `y`=:y</code></td> <td>Named column=:param list, typically for INSERT statements.</td></tr>
<tr><th>Identifiers</th> <th> </th> <th> </th></tr>
<tr><td><code>?:</code></td> <td><code>`a`, `b`, `c`</code></td> <td>Expands array key names into escaped column identifier list.</td></tr>
</table>
<h3>Parameter passing</h3>
`db()` takes a SQL query as first parameter, and allows an arbitrary number of indexed or array arguments thereafter.
It will bind function arguments to a single placeholder each:
db("SELECT ?,?,?", 1, 2, 3)
An array arg will be consumed through complex placeholders:
db("SELECT ??, ??", array(1, 2, 3), array(4, 5, 6));
For indexed parameterization, the order of arguments needs to associate them exactly with the placeholders; obviously.
For named parameters that's not relevant. They have to be passed as array with alphanumeric indexes, and not literal `db()` arguments, of course:
db("SELECT ::", array("key"=>1, "var"=>2));
Keys need to be unique currently.
<h3> Result wrapper </h3>
Queries don't return a plain PDO handle. They're wrapped in a `db_result{}`, which still allows iterations:
foreach (db("SELECT * FROM all") as $row) {
Where dual-access with `->property` or `["array"]` syntax is permitted:
print $row->value;
print $row["value"]
But the result set also allows just accessing one column from the first row:
print db("SELECT value FROM config")->value;
Alternatively the real PDOStatement methods are also accessible:
db("SELECT * FROM all")->fetchAll()
<h3> Database connection </h3>
Per default `db()` just operates on the globally shared `$db` handle.
It can be construed manually however with:
db("connect", "mysql:*", $user, $pw);
When calling `db()` without any params, it will return said PDO instance. Thus allowing raw access to the active database through this global wrapper:
db()->prepare("INSERT INTO tbl VALUES(?,?)")
->execute(array(1,2));
<h3> Token binding </h3>
Another common feature of such database wrappers are literal placeholder tokens. `db()` provides them as `{NAME}` syntax, for e.g. table name prefixes.
Declaring them is as easy as:
$db()->tokens["PREFIX"] = "dbname.";
Z c1940f3768b02d35d7f0d4e9edd60ef1