PHP utility collection with hybrid and fluent APIs.

⌈⌋ ⎇ branch:  hybrid7 libraries


Update of "db"

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Artifact ID: cd1512165a2d733cb2da64b7a18876e045063900
Page Name:db
Date: 2014-07-27 16:21:51
Original User: mario
Mimetype:text/x-markdown
Parent: 57514a38c37ea790b9467ce8dbc899d9b75291c6 (diff)
Next b116593aebf87af825838e7cfd8fe397ecbbc160
Content

db()

db.php is a lightweight wrapper for PDO. It allows some flexibility with passing scalar parameters, and adds new placeholders for array binding.

Placeholders

List params Expanded Usage
? ? A single scalar value (as in PDO).
?? ?, ?, ? List of scalars.
Named params    
:name :name Named parameter (as in PDO).
:: :a, :b, :c Extracts named array into list of named placeholders.
:& x=:x AND y=:y Named column = :param comparison conjoined with AND.
:| x=:x OR y=:y Named Named column = :param comparison conjoined with OR.
:, x=:x, y=:y Named column=:param list, typically for INSERT statements.
Identifiers    
?: a, b, c Expands array key names into escaped column identifier list.
Expressions    
:* SQL and args Takes a list of SQL+parameter pairs. Only interpolates the SQL if the array is non-empty. Allows to nest expressions and complex parameters.

Parameter passing

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.

Result wrapper

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()

Database connection

Per default db() just operates on the globally shared $db handle.

The db_wrap{} handle is initialized with:

db(new PDO("sqlite:"));

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));

Token binding

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.";