Database Component

A PDO wrapper with lazy connections and query profiling.

use BootPress\Database\Component as Database;

Packagist License MIT HHVM Tested PHP 7 Supported Build Status Code Climate Test Coverage


A PDO wrapper with lazy connections, query profiling, and convenience methods that simplify and speed up your queries.

public __construct ( string|object $dsn [, string|null $username = null [, string|null $password = null [, array $options [, array $exec ]]]] )

Either implement an established PDO instance, or set up a lazy database connection that we only connect to if and when you actually use it. Every $dsn string with a 'dbname' is saved in a databases.yml file (if you have the Symfony Yaml and BootPress Page components installed) so that you only need to spell everything out once, and then just refer to the 'dbname' in your code.

@param $dsn

Either a PDO Instance, a DSN string that contains the information required to connect to the database, or the 'dbname' saved in the databases.yml file. Some examples are:

  • MySQL
    • mysql:host=[name];port=[number];dbname=[database];unix_socket=[instead of host or port];charset=[utf-8]
    • MySQL v. <= 4.1 not supported in PHP v. >= 5.4.0
    • charset is ignored in PHP v. < 5.3.6
  • SQLite
    • sqlite:[filepath OR ':memory:']
  • PostgreSQL
    • pgsql:host=[name];port=[number];dbname=[database];user=[name];password=[secret]
  • Oracle
    • oci:dbname=[database];charset=[utf-8]
  • MSSQL on Windows
    • sqlsrv:Server=[name,port];Database=[name] ... and a bunch of other options
  • MSSQL on Linux / Unix
    • sybase:host=[name];dbname=[database];charset=[utf-8];appname=[application];secure=[currently unused]
  • Sybase
    • sybase:host=[name];dbname=[database];charset=[utf-8];appname=[application];secure=[currently unused]
  • Microsoft SQL Server
    • mssql:host=[name];dbname=[database];charset=[utf-8];appname=[application];secure=[currently unused]
  • Cubrid
    • cubrid:host=[name];port=[number];dbname=[database];
@param $username

The user name for the DSN string. This parameter is optional for some PDO drivers.

@param $password

The password for the DSN string. This parameter is optional for some PDO drivers.

@param $options

An array('key'=>'value', ...) of driver-specific connection options.

@param $exec

Queries you would like to execute upon connecting to the database.

@see
@example
use BootPress\Database\Component as Database;

$db = new Database('mysql:dbname=test;host=127.0.0.1', 'localhost', 'root', array(), array(
    'SET timezone = "GMT"',
));

public mixed exec ( string|array $query [, string|array $values ] )

Prepare and execute a query.

@param $query

An SQL statement.

@param $values

The query parameters.

@return

Either false if there was a problem, or whatever the $db->execute()d.

$db->exec(array(
    'CREATE TABLE employees (',
    '  id INTEGER PRIMARY KEY,',
    '  name TEXT NOT NULL DEFAULT "",',
    '  title TEXT NOT NULL DEFAULT ""',
    ')',
));

public bool|int insert ( string|int $table , array $data [, string $and ] )

Insert new records into a database table.

@param $table

Either the database table name, or the prepared statement id you got from calling this the first time. You can also include 'INTO' here which is handy for prepending a qualifier eg. 'OR IGNORE INTO table'.

@param $data

Either the table column names (when preparing a statement), or a row of values (to insert). If you are only inserting one record and want to save yourself some typing, then make this an array(name => value, ...) of columns.

@param $and

Anything you would like to add at the end of the query eg. 'ON DUPLICATE KEY UPDATE ...'.

@return

Either false if there was an error, a prepared $stmt to keep passing off as the $table, or the $id of the row you just inserted. Don't forget to $db->close($stmt).

if ($stmt = $db->insert('employees', array('id', 'name', 'title'))) {
    $db->insert($stmt, array(101, 'John Smith', 'CEO'));
    $db->insert($stmt, array(102, 'Raj Reddy', 'Sysadmin'));
    $db->insert($stmt, array(103, 'Jason Bourne', 'Developer'));
    $db->insert($stmt, array(104, 'Jane Smith', 'Sales Manager'));
    $db->insert($stmt, array(105, 'Rita Patel', 'DBA'));
    $db->close($stmt);
}

if ($db->insert('OR IGNORE INTO employees', array(
    'id' => 106,
    'name' => "Little Bobby'); DROP TABLE employees;--",
    'title' => 'Intern',
))) {
    echo $db->log('count'); // 1 - It worked!
}

public bool|int update ( string|int $table , array $id , array $data [, string $and ] )

Modify records in a database table.

@param $table

Either the database table name, or the prepared statement id you got from calling this the first time. You can also include 'SET' here which is handy for getting some updates in that we can't otherwise do eg. 'table SET date = NOW(),'.

@param $id

Either the name of the column with the unique identifier you will be referencing (when preparing a statement), or the unique identifier of the column you are updating.

@param $data

Either the table column names (when preparing a statement), or a row of values (to update). If you are only updating one record and want to save yourself some typing, then make this an array(name => value, ...) of columns.

@param $and

Anything you would like to add at the end of the query after the WHERE eg. 'AND approved = "Y"'.

@return

Either false if there was an error, a prepared $stmt to keep passing off as the $table, or the $num of rows affected. Don't forget to $db->close($stmt).

if (!$db->update('employees SET id = 101', 'id', array(
    106 => array(
        'name' => 'Roberto Cratchit',
        'title' => 'CEO',
    )
))) {
    echo $db->log('error'); // A unique id constraint
}

if ($stmt = $db->update('employees', 'id', array('title'))) {
    $db->update($stmt, 103, array('Janitor'));
    $db->update($stmt, 99, array('Quality Control'));
    $db->close($stmt);
}

public bool|int upsert ( string|int $table , array $id , array $data )

Either update or insert records depending on whether they already exist or not.

@param $table

Either the database table name, or the prepared statement id you got from calling this the first time. You cannot include 'SET' or 'INTO' here.

@param $id

Either the name of the column with the unique identifier you will be referencing (when preparing a statement), or the unique identifier of the column you are upserting.

@param $data

Either the table column names (when preparing a statement), or a row of values (to upsert). If you are only upserting one record and want to save yourself some typing, then make this an array(name => value, ...) of columns.

@return

Either false if there was an error, a prepared $stmt to keep passing off as the $table, or the $id of the row that was upserted. Don't forget to $db->close($stmt).

if ($stmt = $db->upsert('employees', 'id', array('name', 'title'))) {
    $db->upsert($stmt, 101, array('Roberto Cratchit', 'CEO'));
    $db->upsert($stmt, 106, array('John Smith', 'Developer'));
    $db->close($stmt);
}

$db->upsert('employees', 'id', array(
    107 => array(
        'name' => 'Ella Minnow Pea',
        'title' => 'Executive Assistant',
    ),
));

public bool|int query ( string|array $select [, string|array $values [, string $fetch = 'row' ]] )

Select data from the database.

@param $select

A SELECT statement.

@param $values

The query parameters.

@param $fetch

How you would like your row.

@return

Either false if there was a problem, or a statement that you can $db->fetch($result) rows from. Don't forget to $db->close($result).

if ($result = $db->query('SELECT name, title FROM employees', '', 'assoc')) {
    while ($row = $db->fetch($result)) {
        print_r($row);
        // array('name'=>'Roberto Cratchit', 'title'=>'CEO')
        // array('name'=>'Raj Reddy', 'title'=>'Sysadmin')
        // array('name'=>'Jason Bourne', 'title'=>'Janitor')
        // array('name'=>'Jane Smith', 'title'=>'Sales Manager')
        // array('name'=>'Rita Patel', 'title'=>'DBA')
        // array('name'=>'John Smith', 'title'=>'Developer')
        // array('name'=>'Ella Minnow Pea', 'title'=>'Executive Assistant')
    }
    $db->close($result);
}

public array all ( string|array $select [, string|array $values [, string $fetch = 'row' ]] )

Get all of the selected rows from your query at once.

@param $select

A SELECT statement.

@param $values

The query parameters.

@param $fetch

How you would like your row.

@return

No false heads up here. You either have rows, or you don't.

foreach ($db->all('SELECT id, name, title FROM employees') as $row) {
    list($id, $name, $title) = $row;
}

public bool|array ids ( string|array $select [, string|array $values ] )

Get all of the id's from your query, or whatever the first column you requested is.

@param $select

A SELECT statement.

@param $values

The query parameters.

@return

Either false if there were no rows, or an array() of every rows first value.

if ($ids = $db->ids('SELECT id FROM employees WHERE title = ?', 'Intern')) {
    // Then Little Bobby Tables isn't as good as we thought.
}

public bool|array row ( string|array $select [, string|array $values [, string $fetch = 'row' ]] )

Get only the first row from your query.

@param $select

A SELECT statement.

@param $values

The query parameters.

@param $fetch

How you would like your row.

@return

Either false if there was no row, or an array() of the first one fetched.

if ($janitor = $db->row('SELECT id, name FROM employees WHERE title = ?', 'Janitor', 'assoc')) {
    // array('id'=>103, 'name'=>'Jason Bourne')
}

public bool|string value ( string|array $select [, mixed $values ] )

Get only the first value of the first row from your query.

@param $select

A SELECT statement.

@param $values

The query parameters.

@return

Either false if there was no row, or the $value you are looking for.

echo $db->value('SELECT COUNT(*) FROM employees'); // 7

public bool|int prepare ( string|array $query [, string $fetch = null ] )

Prepare a query to be executed.

@param $query

An SQL statement.

@param $fetch

How you would like the SELECT rows returned. Either 'obj', 'assoc', 'named', 'both', or 'num' (the default).

@return

Either false if there was an error, or a $stmt id that can be$db->execute()d or $db->fetch()ed. Don't forget to $db->close($stmt).

public mixed execute ( int $stmt [, string|array $values = null ] )

Execute a prepared statement.

@param $stmt

A $db->prepare(...)d statement's return value.

@param $values

The query parameters. If there is only one or none, then this can be a string.

@return

Either false if there was an error, true for a SELECT query, the inserted $id for an INSERT query, or the $num of affected rows for everything else.

public mixed fetch ( int $stmt )

Get the next row from an executed SELECT statement.

@param $stmt

A $db->prepare(...)d statement's return value.

public close ( int $stmt )

Closes a $db->prepared()d statement to free up the database connection.

@param $stmt

A $db->prepare(...)d statement's return value.

public string debug ( string|array $query [, mixed $values ] )

Returns a $query with it's $values in place so that you can stare at it, and try to figure out what is going on.

@param $query

An SQL statement.

@param $values

The query parameters.

public mixed log ( [ string $value = null ] )

Returns information about the previously executed query.

@param $value

If you don't want the whole array, then you can specify the specific value you do want. Either 'sql', 'count', 'prepared', 'executed', 'errors', 'average', 'total', or 'time'.

public object connection ( void )

@return

The database connection. This is how we create lazy connections.

public int id ( void )

@return

The current database's id.

public string|null driver ( [ string $name = null ] )

@param $name

Pass a value to set the database driver's name.

@return

The current database driver's name.

public static array logs ( [ int $id = null ] )

@param $id

To only return the data for a specific database connection.

@return

Debug, error, and profile data for all of your database queries.

public static array errors ( void )

@return

All of the errors generated from all of your database connections.

Document Your Code

Installation

Add the following to your composer.json file.

{
    "require": {
        "bootpress/database": "^1.0"
    }
}

Example Usage

<?php

use BootPress\Database\Component as Database;

$dsn = 'mysql:dbname=test;host=127.0.0.1';
$username = 'localhost';
$password = 'root';

$pdo = new PDO($dsn, $username, $password);

$db = new Database($pdo);

If you already have the PDO connection then you can just give it to the constructor, but if you pass the parameters to us directly, then we will only connect to the database if and when you use it.

$db = new Database($dsn, $username, $password, array(), array(
    'SET timezone = "GMT"',
));

Now you have your $db object, but we haven't done anything yet. Once you crank out a query, then we'll connect to the database, and in this case we'll set the timezone for you as well. Let's do that now.

// First we'll create a table
$db->exec(array(
    'CREATE TABLE employees (',
    '  id INTEGER PRIMARY KEY,',
    '  name TEXT NOT NULL DEFAULT "",',
    '  title TEXT NOT NULL DEFAULT ""',
    ')',
));

// Insert some records
if ($stmt = $db->insert('employees', array('id', 'name', 'title'))) {
    $db->insert($stmt, array(101, 'John Smith', 'CEO'));
    $db->insert($stmt, array(102, 'Raj Reddy', 'Sysadmin'));
    $db->insert($stmt, array(103, 'Jason Bourne', 'Developer'));
    $db->insert($stmt, array(104, 'Jane Smith', 'Sales Manager'));
    $db->insert($stmt, array(105, 'Rita Patel', 'DBA'));
    $db->close($stmt); // The records will be inserted all at once
}

// You can also try this
if ($db->insert('OR IGNORE INTO employees', array(
    'id' => 106,
    'name' => "Little Bobby'); DROP TABLE employees;--",
    'title' => 'Intern',
))) {
    echo $db->log('count'); // 1 - It worked!
}

// Make some updates
if (!$db->update('employees SET id = 101', 'id', array(
    106 => array(
        'name' => 'Roberto Cratchit',
        'title' => 'CEO',
    )
))) {
    echo $db->log('error'); // A unique id constraint
}

if ($stmt = $db->update('employees', 'id', array('title'))) {
    $db->update($stmt, 103, array('Janitor'));
    $db->update($stmt, 99, array('Quality Control'));
    $db->close($stmt);
}

// And upsert more
if ($stmt = $db->upsert('employees', 'id', array('name', 'title'))) {
    $db->upsert($stmt, 101, array('Roberto Cratchit', 'CEO'));
    $db->upsert($stmt, 106, array('John Smith', 'Developer'));
    $db->close($stmt);
}

$db->upsert('employees', 'id', array(
    107 => array(
        'name' => 'Ella Minnow Pea',
        'title' => 'Executive Assistant',
    ),
));

// Check to see who all is on board
if ($result = $db->query('SELECT name, title FROM employees', '', 'assoc')) {
    while ($row = $db->fetch($result)) {
        print_r($row);
        /*
        array('name'=>'Roberto Cratchit', 'title'=>'CEO')
        array('name'=>'Raj Reddy', 'title'=>'Sysadmin')
        array('name'=>'Jason Bourne', 'title'=>'Janitor')
        array('name'=>'Jane Smith', 'title'=>'Sales Manager')
        array('name'=>'Rita Patel', 'title'=>'DBA')
        array('name'=>'John Smith', 'title'=>'Developer')
        array('name'=>'Ella Minnow Pea', 'title'=>'Executive Assistant')
        */
    }
    $db->close($result);
}

foreach ($db->all('SELECT id, name, title FROM employees') as $row) {
    list($id, $name, $title) = $row;
}

if ($ids = $db->ids('SELECT id FROM employees WHERE title = ?', 'Intern')) {
    // Then Little Bobby Tables isn't as good as we thought.
}

// Find someone to clean things up around here
if ($janitor = $db->row('SELECT id, name FROM employees WHERE title = ?', 'Janitor', 'assoc')) {
    // array('id'=>103, 'name'=>'Jason Bourne')
}

// Get a total head count
echo $db->value('SELECT COUNT(*) FROM employees'); // 7

// Trim the fat
$db->exec('DELETE FROM employees WHERE id = ?', 102);