mbfisher/dimple

A simple PDO wrapper for making simple SQL queries

Installs: 0

Dependents: 0

Watchers: 1

Language: PHP

v0.1.0 2014-03-11 17:04 UTC

README

Build Status

A simple PDO wrapper for making simple SQL queries.

Installation

Composer

"require": {
    "mbfisher/dimple": "~0.1"
}

Usage

require 'vendor/autoload.php';

use mbfisher\Dimple;

$pdo = new PDO('sqlite::memory:');
$db = new Dimple($pdo);

Select

select($table, array $columns = null, array $where = [], array $order = [], $limit = null)

All results are returned as an array of associative arrays.

select('my_table');
SELECT * FROM `my_table`;

Restrict the columns returned:

select('my_table', ['id']);
SELECT `id` FROM `my_table`;

Add a where clause. Note that passing null as the second argument selects all columns:

select('my_table', null, ['id' => 1]);
SELECT * FROM `my_table` WHERE `id` = 1;

Order the result:

select('my_table', null, null, ['id'])
SELECT * FROM `my_table` ORDER BY `id` ASC;

select('my_table', null, null, [['id', 'DESC']])
SELECT * FROM `my_table` ORDER BY `id` DESC;

Limit the result:

select('my_table', null, null, null, 1);
SELECT * FROM `my_table` LIMIT 1;

Go crazy!

select('my_table', ['id'], ['id' => ['>', 1]], [['id', 'DESC']], 5);
SELECT `id` FROM `my_table` WHERE `id` > 1 ORDER BY `id` DESC LIMIT 5;

Insert

insert($table, array $data)

Returns the ID of the last inserted row.

insert('my_table', ['id' => 1, 'name' => 'Mike']);
INSERT INTO `my_table` (`id`, `name`) VALUES (1, "Mike");

Update

update($table, array $data, array $where = [])

Returns the number of rows affected by the query.

update('my_table', ['name' => 'Mike']);
UPDATE `my_table` SET `name` = "Mike";

update('my_table', ['name' => "Mike"], ['id' => 1]);
UPDATE `my_table` SET `name` = "Mike" WHERE `id` = 1;

Replace

replace($table, array $data)

Returns the number of rows affected by the query.

replace('my_table', ['id' => 1, 'name' => 'Mike']);
REPLACE INTO `my_table` (`id`, `name`) VALUES (1, "Mike");

Advanced

Where

If the value is an array and two elements are provided the first is a comparator and the second a value:

['id' => ['>', 1]]
WHERE `id` > 1

If the value is an array and on element is provided it is assumed to be a comparator without a value:

['name' => ['IS NOT NULL']
WHERE `name` IS NOT NULL

Multiple keys are chained together with AND:

['id' => ['>', 10], 'name' => ['IS NOT NULL']]
WHERE `id` > 1 AND `name` IS NOT NULL

Order by

If the array element is a string or an array with one element it is treated as a column name and the default sorting ASC is applied:

['id'] || [['id']]
ORDER BY `id` ASC

If the array element is an array with two values the first is used as a column name and the second as a sorting order:

[['id', 'DESC']]
ORDER BY `id` DESC

Multiple elements produced composite ordering:

['id', [['age', 'DESC']], ['name', 'ASC']]
ORDER BY `id` ASC, `age` DESC, `name` ASC