Aura SQL

<?php
$connection_factory = include '/path/to/Aura.Sql/scripts/instance.php';
$connection = $connection_factory->newInstance(

    // adapter name
    'mysql',

    // DSN elements for PDO; this can also be
    // an array of key-value pairs
    'host=localhost;dbname=database_name',

    // username for the connection
    'username',

    // password for the connection
    'password'
);

<?php
use Aura\Sql\ConnectionFactory;
$connection_factory = new ConnectionFactory;
$connection = $connection_factory->newInstance(...);

<?php
$connection->connect();

<?php
// returns all rows
$result = $connection->fetchAll('SELECT * FROM foo');

<?php
// the text of the query
$text = 'SELECT * FROM foo WHERE id = :id';

// values to bind to query placeholders
$bind = [
    'id' => 1,
];

// returns one row; the data has been parameterized
// into a prepared statement for you
$result = $connection->fetchOne($text, $bind);

<?php
// the text of the query
$text = 'SELECT * FROM foo WHERE id = :id AND bar IN(:bar_list)';

// values to bind to query placeholders
$bind = [
    'id' => 1,
    'bar_list' => ['a', 'b', 'c'],
];

// returns all rows; the query ends up being
// "SELECT * FROM foo WHERE id = 1 AND bar IN('a', 'b', 'c')"
$result = $connection->fetchOne($text, $bind);

<?php
// create a new Select object
$select = $connection->newSelect();

// SELECT * FROM foo WHERE bar > :bar ORDER BY baz
$select->cols(['*'])
       ->from('foo')
       ->where('bar > :bar')
       ->orderBy('baz');

$bind = ['bar' => '88'];

$list = $connection->fetchAll($select, $bind);

<?php
// create a new Insert object
$insert = $connection->newInsert();

// INSERT INTO foo (bar, baz, date) VALUES (:bar, :baz, NOW());
$insert->into('foo')
       ->cols(['bar', 'baz'])
       ->set('date', 'NOW()');

$bind = [
    'bar' => null,
    'baz' => 'zim',
];

$stmt = $connection->query($insert, $bind);

<?php
// create a new Update object
$update = $connection->newUpdate();

// UPDATE foo SET bar = :bar, baz = :baz, date = NOW() WHERE zim = :zim OR gir = :gir
$update->table('foo')
       ->cols(['bar', 'baz'])
       ->set('date', 'NOW()')
       ->where('zim = :zim')
       ->orWhere('gir = :gir');

$bind = [
    'bar' => 'barbar',
    'baz' => 99,
    'zim' => 'dib',
    'gir' => 'doom',
];

$stmt = $connection->query($update, $bind);

<?php
// create a new Delete object
$delete = $connection->newDelete();

// DELETE FROM WHERE zim = :zim OR gir = :gir
$delete->from('foo')
       ->where('zim = :zim')
       ->orWhere('gir = :gir');

$bind = [
    'zim' => 'dib',
    'gir' => 'doom',
];

$stmt = $connection->query($delete, $bind);

<?php
// get the list of tables
$list = $connection->fetchTableList();

// show them
foreach ($list as $table) {
    echo $table . PHP_EOL;
}

<?php
// the table to get cols for
$table = 'foo';

// get the cols
$cols = $connection->fetchTableCols($table);

// show them
foreach ($cols as $name => $col) {
    echo "Column $name is of type "
       . $col->type
       . " with a size of "
       . $col->size
       . PHP_EOL;
}

<?php
// turn off autocommit and start a transaction
$connection->beginTransaction();

try {
    // ... perform some queries ...
    // now commit to the database:
    $connection->commit();
} catch (Exception $e) {
    // there was an error, roll back the queries
    $connection->rollBack();
}

// at this point we are back in autocommit mode

<?php
$text = "SELECT * FROM foo WHERE id = :id";
$bind = ['id' => 1];
$stmt = $connection->query($text, $bind);

<?php
// turn on the profiler
$connection->getProfiler()->setActive(true);

// issue a query
$result = $connection->fetchAll('SELECT * FROM foo');

// now get the profiler information
foreach ($connection->getProfiler()->getProfiles() as $i => $profile) {
    echo 'Query #' . ($i + 1)
       . ' took ' . $profile->time . ' seconds.'
       . PHP_EOL;
}