Database access

All database access should be made via the Pdb helper, which is a wrapper for the PDO library.

When used correctly, this method of database access will always be safe from SQL injection.

Making queries

The method to make database queries is Pdb::query, and it takes three arguments:

• The query string
• An array of bind parameters
• The return type of the query

All three arguments are required.

The query string can contain table names which are prefixed with a tilde (~) character, which will automatically include the table prefix, so ~pages will become sprout_pages.

The query string can also contain binding placeholders, either ? for numeric placeholder or :name for named placeholders. Both types can be used in a single query.

The actual values for the bind parameters should be provided in the second argument to the method.

The third argument is a string representing the return type of the query. This can be 'pdo' to return directly the PDO resultset, but can also be one of a number of different return types, based on your needs.

Return types

It's common to need data in different formats. To avoid having to write conversion code every time, it's provided as part of the Pdb class.

pdo

Return the resultset directly

count

The number of affected/fetched rows

null

Don't return anything

arr

An array of rows, where each row is an associative array. Use only for very small result sets, e.g. <= 20 rows.

arr-num

An array of rows, where each row is a numeric array. Use only for very small result sets, e.g. <= 20 rows.

row

A single row, as an associative array

row-num

A single row, as a numeric array

map

An array of identifier => value pairs, where the identifier is the first column in the result set, and the value is the second

map-arr

An array of identifier => value pairs, where the identifier is the first column in the result set, and the value an associative array of name => value pairs (if there are multiple subsequent columns)

val

A single value (i.e. the value of the first column of the first row)

col

All values from the first column, as a numeric array. (cannot be used with boolean columns)