Download the PHP package grithin/phpdb without Composer

PHP Database Tools

Purpose

A convenience wrapper over PDO that has lazy loading and singletons, allows for backup connections, and attempts to reconnect on connection loss

Appetizer

The intent of the Db class is to reduce the amount of time coding common database operations.

Output

One common issue is formatting. Say I want only a column in a database, and I want it as an array

Say I want an array of rows, but I want it keyed on the user id

What if I just want a single, non array value of some column, in some record?

Special Query

For a query, the normal ['id'=> 2] array does not suit for situations of non-equal operators. What if I wanted name is null or id > 10? This is built in to Db

This will result in SQL like

This special type of array interpretation allows the full set of complex query filters to be maintained in an array, instead of having to construct the SQL piece by piece.

The query array can be used on all of the methods

Optional Use

There are a variety of ways you can present your SQL. Db supports

• prepared statements
• plain txt
• query array and positional arguments ($table, $query, $select)

Use

Connecting

The first argument to init is the name of the singleton instance (see SingletonDefault or SDLL class in phpbase).

When Db is called statically, it defaults to the primary singleton instance, which is usually the first initialised.

If you want a instance variable for the primary singleton (the singleton default), you can get it

This is useful for looped code (since there is overhead in using static methods).

You can also get an instance by name

Basic Use

There are two flavors of use

1. quoted queries
2. prepared statements

Query

Quoting

All user input placed in raw sql should be quoted or conformed. You can quote with

You can also quote database identities, with

This is sometimes useful when the identity has a name conflicting with a reserved database identity

SQL

Shortcuts

Short cut parameters are automatically quoted if necessary

There are many additional helper functions like the above. I recommended looking through the code. Create a github issue if you require one to be further documented.

Shortcut Magic

Using a custom comparater

There is various behavior based on these rules:

• if key starts with '"', the unescaped value is taken as entire where line
  • ex: ['" anything after the " is not used':'1=1']
• if "?" is in the key, the part after the "?" will serve as the "equator", ("bob?<>"=>'sue') -> "bob <> 'sue'"
• if key starts with ":", value is not escaped
• if value === null, set value to unescaped "null"
• if value set to unescaped "null", and if within a "where" helper function, and comparater is '=', prefix with 'is '.

Prepared Statements

The exec function is overloaded to accept three forms. The end result of the forms is to have one joined sql string and one merged variable array.

• single array (['sql',$var_array,'sql']):
• as params ($sql1, $var_array1, $sql2, $var_array2, $var_array3 )

You can either use pdo statment methods, or you can use Db helper functions. Most of the query based functions have corresponding methods prefixed with as_