Sparrow

Sparrow is a simple but powerful database toolkit. Sparrow is a fluent SQL builder, database abstraction layer, cache manager, query statistics generator, and micro-ORM all rolled into a single class file.

Requirements

Sparrow requires PHP 5.1 or greater.

Building SQL

// Include the library
include '/path/to/Sparrow.php';

// Declare the class instance
$db = new Sparrow;

// Select a table
$db->from('user');

// Build a select query
$db->select();

// Display the SQL
echo $db->sql();

Output:

SELECT * FROM user

Method Chaining

Sparrow allows you to chain methods together, so you can instead do:

echo $db->from('user')->select()->sql();

Where Conditions

To add where conditions to your query, use the where function.

echo $db->from('user')
  ->where('id', 123)
  ->select()
  ->sql();

Output:

SELECT * FROM user WHERE id=123

You can call where multiple times to add multiple conditions.

echo $db->from('user')
  ->where('id', 123)
  ->where('name', 'bob')
  ->select()
  ->sql();

Output:

SELECT * FROM user WHERE id=123 AND name='bob'

You can also pass an array to the where function. The following would produce the same output.

$where = array('id' => 123, 'name' => 'bob');

echo $db->from('user')
  ->where($where)
  ->select()
  ->sql();

You can even pass in a string literal.

echo $db->from('user')
  ->where('id = 99')
  ->select()
  ->sql();

Output:

SELECT * FROM user WHERE id = 99

Custom Operators

The default operator for where queries is =. You can use different operators by placing them after the field declaration.

echo $db->from('user')
  ->where('id >', 123)
  ->select()
  ->sql();

Output:

SELECT * FROM user WHERE id>123;

OR Queries

By default where conditions are joined together by AND keywords. To use OR instead, simply place a | delimiter before the field name.

echo $db->from('user')
  ->where('id <', 10)
  ->where('|id >', 20)
  ->select()
  ->sql();

Output:

SELECT * FROM user WHERE id<10 OR id>20

LIKE Queries

To build a LIKE query you can use the special % operator.

echo $db->from('user')
  ->where('name %', '%bob%')
  ->select()
  ->sql();

Output:

SELECT * FROM user WHERE name LIKE '%bob%'

To build a NOT LIKE query, add a ! before the % operator.

echo $db->from('user')
  ->where('name !%', '%bob%')
  ->select()
  ->sql();

Output:

SELECT * FROM user WHERE name NOT LIKE '%bob%'

IN Queries

To use an IN statement in your where condition, use the special @ operator and pass in an array of values.

echo $db->from('user')
  ->where('id @', array(10, 20, 30))
  ->select()
  ->sql();

Output:

SELECT * FROM user WHERE id IN (10,20,30)

To build a NOT IN query, add a ! before the @ operator.

echo $db->from('user')
  ->where('id !@', array(10, 20, 30))
  ->select()
  ->sql();

Output:

SELECT * FROM user WHERE id NOT IN (10,20,30)

Selecting Fields

To select specific fields, pass an array in to the select function.

echo $db->from('user')
  ->select(array('id','name'))
  ->sql();

Output:

SELECT id,name FROM user

Limit and Offset

To add a limit or offset to a query, you can use the limit and offset functions.

echo $db->from('user')
  ->limit(10)
  ->offset(20)
  ->select()
  ->sql();

Output:

SELECT * FROM user LIMIT 10 OFFSET 20

You can also pass in additional parameters to the select function.

echo $db->from('user')
  ->select('*', 50, 10)
  ->sql();

Output:

SELECT * FROM user LIMIT 50 OFFSET 10

Distinct

To add a DISTINCT keyword to your query, call the distinct function.

echo $db->from('user')
  ->distinct()
  ->select('name')
  ->sql();

Output:

SELECT DISTINCT name FROM user

Table Joins

To add a table join, use the join function and pass in an array of fields to join on.

echo $db->from('user')
  ->join('role', array('role.id' => 'user.id'))
  ->select()
  ->sql();

Output:

SELECT * FROM user INNER JOIN role ON role.id=user.id

The default join type is an INNER join. To build other types of joins you can use the alternate join functions leftJoin, rightJoin, and fullJoin.

The join array works just like where conditions, so you can use custom operators and add multiple conditions.

echo $db->from('user')
  ->join('role', array('role.id' => 'user.id', 'role.id >' => 10))
  ->select()
  ->sql();

Output:

SELECT * FROM user INNER JOIN role ON role.id=user.id AND role.id>10

Sorting

To add sorting to a query, use the sortAsc and sortDesc functions.

echo $db->from('user')
  ->sortDesc('id')
  ->select()
  ->sql();

Output:

SELECT * FROM user ORDER BY id DESC

You can also pass an array to the sort functions.

echo $db->from('user')
  ->sortAsc(array('rank','name'))
  ->select()
  ->sql();

Output:

SELECT * FROM user ORDER BY rank ASC, name ASC

Grouping

To add a field to group by, use the groupBy function.

echo $db->from('user')
  ->groupBy('points')
  ->select(array('id','count(*)'))
  ->sql();

Output:

SELECT id, count(*) FROM user GROUP BY points

Insert Queries

To build an insert query, pass in an array of data to the insert function.

$data = array('id' => 123, 'name' => 'bob');

echo $db->from('user')
  ->insert($data)
  ->sql();

Output:

INSERT INTO user (id,name) VALUES (123,'bob')

Update Queries

To build an update query, pass in an array of data to the update function.

$data = array('name' => 'bob', 'email' => 'bob@aol.com');
$where = array('id' => 123);

echo $db->from('user')
  ->where($where)
  ->update($data)
  ->sql();

Output:

UPDATE user SET name='bob',email='bob@aol.com' WHERE id=123

Delete Queries

To build a delete query, use the delete function.

echo $db->from('user')
  ->where('id', 123)
  ->delete()
  ->sql();

Output:

DELETE FROM user WHERE id=123

Executing Queries

Sparrow can also execute the queries it builds. You will need to call the setDb() method with either a connection string, an array of connection information, or a connection object.

The supported database types are mysql, mysqli, pgsql, sqlite and sqlite3.

Using a connection string:

$db->setDb('mysql://admin:hunter2@localhost/mydb');

The connection string uses the following format:

type://username:password@hostname[:port]/database

For sqlite, you need to use:

type://database

Using a connection array:

$db->setDb(array(
  'type' => 'mysql',
  'hostname' => 'localhost',
  'database' => 'mydb',
  'username' => 'admin',
  'password' => 'hunter2'
));

The possible array options are type, hostname, database, username, password, and port.

Using a connection object:

$mysql = new mysqli('localhost', 'admin', 'hunter2');

$mysql->select_db('mydb');

$db->setDb($mysql);

You can also use PDO for the database connection. To use the connection string or array method, prefix the database type with pdo:

$db->setDb('pdomysql://admin:hunter2@localhost/mydb');

The possible PDO types are pdomysql, pdopgsql, and pdosqlite.

You can also pass in any PDO object directly:

$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'admin', 'hunter2');

$db->setDb($pdo);

Fetching records

To fetch multiple records, use the many function.

$rows = $db->from('user')
  ->where('id >', 100)
  ->many();

The result returned is an array of associative arrays:

array(
  array('id' => 101, 'name' => 'joe'),
  array('id' => 102, 'name' => 'ted');
)

To fetch a single record, use the one function.

$row = $db->from('user')
  ->where('id', 123)
  ->one();

The result returned is a single associative array:

array('id' => 123, 'name' => 'bob')

To fetch the value of a column, use the value function and pass in the name of the column.

$username = $db->from('user')
  ->where('id', 123)
  ->value('username');

All the fetch functions automatically perform a select, so you don't need to include the select function unless you want to specify the fields to return.

$row = $db->from('user')
  ->where('id', 123)
  ->select(array('id', 'name'))
  ->one();

Non-queries

For non-queries like update, insert and delete, use the execute function after building your query.

$db->from('user')
  ->where('id', 123)
  ->delete()
  ->execute();

Executes:

DELETE FROM user WHERE id = 123

Custom Queries

You can also run raw SQL by passing it to the sql function.

$posts = $db->sql('SELECT * FROM posts')->many();

$user = $db->sql('SELECT * FROM user WHERE id = 123')->one();

$db->sql('UPDATE user SET name = 'bob' WHERE id = 1')->execute();

Escaping Values

Sparrow's SQL building functions automatically quote and escape values to prevent SQL injection. To quote and escape values manually, like when you're writing own queries, you can use the quote function.

$name = "O'Dell";

printf("SELECT * FROM user WHERE name = %s", $db->quote($name));

Output:

SELECT * FROM user WHERE name = 'O\'Dell'

Query Properties

After executing a query, several property values will be populated which you can access directly.

// Last query executed
$db->last_query;

// Number of rows returned
$db->num_rows;

// Last insert id
$db->insert_id;

// Number of affected rows
$db->affected_rows;

These values are reset every time a new query is executed.

Helper Methods

To get a count of rows in a table.

$count = $db