Database Component

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

• http://php.net/manual/en/pdo.construct.php

@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"',
));

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 ""',
    ')',
));

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!
}

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

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

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

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

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.
}

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')
}

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

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

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.

Get the next row from an executed SELECT statement.

@param $stmt

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

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

@param $stmt

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

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.

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'.

@return

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

@return

The current database's id.

@param $name

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

@return

The current database driver's name.

@param $id

To only return the data for a specific database connection.

@return

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

@return

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