Dabble

Dabble is a lightweight wrapper and collection of helpers for MySQLi.

Installation

The prefered way to install Dabble is through composer; the minimum composer.json configuration is:

{
    "require": {
        "dabble/dabble": "@stable"
    }
}

PHP 5.3 or newer with the mysqli extension enabled is required. Dabble is developed and tested against MySQL 5.1+.

Usage

The following is a typical Dabble usage:

<?php
require_once 'path/to/Dabble/Database.php';
require_once 'path/to/Dabble/Result.php';

use Dabble\Database;

$db = new Database('localhost', 'user', 'pass', 'test');

$posts = $db->query(
    'SELECT `title`, `body` FROM `posts` WHERE `tenant_id` = :tenant_id',
    array('tenant_id' => 42)
);

echo 'There are ' . count($posts) . 'posts:' . PHP_EOL;

foreach ($posts as $post) {
    echo $post['title'] . PHP_EOL;
    echo str_repeat('=', strlen($post['title'])) . PHP_EOL;
    echo $post['body'] . PHP_EOL;
}

The full constructor parameters are:

• $host: Server host.
• $username: Server username.
• $password: Server password.
• $database: Database name.
• $charset: Server connection character set; defaults to utf8.
• $port: Server connection port; defaults to 3306.
• $socket: Server connection socket, optional.
• $ssl: Use SSL connection, optional.
• $clientkey: clientkey file, mandatory if $ssl == true
• $clientcert: clientcert file, mandatory if $ssl == true
• $cacert: cacert file, mandatory if $ssl == true

Connect via SSL:

$db = new Database('localhost', 'user', 'pass', 'test', 'utf8', '3306', null, 
    true, '/etc/ssl/clientkey.pem',  '/etc/ssl/clientcert.pem',  
    '/etc/ssl/cacert.pem'
);

While the query() method's parameters are:

• $sql: SQL string.
• $bindings: Array of key-value bindings.

Every parameter binding will be escaped using the mysqli_real_escape_string() function. String parameters will be properly quoted before inserted into the query while true and false will be converted into 1 and 0 respectively. The Result object returned from query() implements Iterator and Count. Errors will yield a RuntimeException.

Array parameters

Parameters in the form of arrays will automatically be transformed and inserted into the query as a comma separated list. The following:

<?php
$posts = $db->query('SELECT * FROM `posts` WHERE `id` IN (:search)', array(
    'search' => array(12, 24, 42, 68, 75)
));

Will execute the SQL:

SELECT * FROM `posts` WHERE `id` IN (12,24,42,68,75);

Optional SQL fragments

SQL passed to the query() method and CRUD helper methods may contain optional SQL fragments delimited by [ and ]. These fragments will be removed from the final SQL if not all placeholders used inside them exist inside the parameter binding. This results in a more coherent way of building queries:

<?php
$params = array();

$params['tenant_id'] = $_SESSION['tenant_id'];
if (isset($_GET['title'])) $params['title'] = '%' . $_GET['title'] . '%';

$posts = $db->query(
    'SELECT * FROM `posts` WHERE `tenant_id` = :tenant_id [AND title LIKE :title]',
    $params
);

In the above example, the [AND title LIKE :title] part will be removed if $params['title'] does not exist. You can nest as many of these optional SQL fragments as you need. Unbalanced [ and ] delimiters is considered to be an error and will yield a RuntimeException.

Transactions

Use begin(), commit(), and rollback() to manage transactions:

<?php
try {
    $db->begin();
    $db->query('UPDATE `users` SET `bal` = `bal` - :amount WHERE id = :id',
        array('amount' => 100, 'id' => 1));
    $db->query('UPDATE `users` SET `bal` = `bal` + :amount WHERE id = :id',
        array('amount' => 100, 'id' => 2));
    $db->commit();
} catch (\Exception $e) {
    $db->rollback();
}

Any SQL errors between begin() and commit() will yield a RuntimeException.

You can also use the Database::transact() method. The following is equivalent to the above:

<?php
$db->transact(function($db) {
    $db->query('UPDATE `users` SET `bal` = `bal` - :amount WHERE id = :id',
        array('amount' => 100, 'id' => 1));
    $db->query('UPDATE `users` SET `bal` = `bal` + :amount WHERE id = :id',
        array('amount' => 100, 'id' => 2));
});

Result querying

num_rows

Even though the Result object implements Countable, the number of rows is also available as a public property:

<?php
$posts = $db->query('SELECT * FROM `posts`');
echo 'This result has ' . $posts->num_rows . ' rows.';

found_rows

If you use SQL_CALC_FOUND_ROWS in your SELECT queries, you can find the number of rows the result would have returned without the LIMIT clause:

<?php
$posts = $db->query(
    'SELECT SQL_CALC_FOUND_ROWS * FROM `posts` LIMIT 10 OFFSET 0'
);
echo 'Showing ' . $posts->num_rows . ' posts out of ' . $posts->found_rows;

This is very useful for things like paginations. If your query does not use SQL_CALC_FOUND_ROWS, accessing Result::$found_rows will give you the same number as Result::$num_rows.

Pagination

If you use SQL_CALC_FOUND_ROWS along with a LIMIT and OFFSET clause, you can get the total number of pages and the current page:

<?php
$posts = $db->query(
    'SELECT SQL_CALC_FOUND_ROWS * FROM `posts` LIMIT 10 OFFSET 10'
);
echo 'Page ' . $posts->page . ' out of ' . $posts->num_pages . ' total pages.';

fetch_fields

Returns rows of field information in a result set:

<?php
$fields = $result->fetch_fields();

Pass true as argument if you want each field information returned as an associative array instead of an object. The default is to return each as an object, exactly like the mysqli_fetch_fields function.

fetch

Fetches a row or a single column within a row:

<?php
$data = $result->fetch($row_number, $column);

This method forms the basis of all fetch methods. All forms of fetch advances the internal row pointer to the next row. null will be returned when there are no more rows to be fetched.

fetch_one

Fetches the next row:

<?php
$next_row = $result->fetch_one();

Pass a column name as argument to return a single column from the next row:

<?php
$name = $result->fetch_one('name');

fetch_all

Returns all rows at once as an array:

<?php
$users = $result->fetch_all();

Pass a column name as argument to return an array of scalar column values:

<?php
$all_tags = $posts->fetch_all('tags');

fetch_transpose

Returns all rows at once, transposed as an array of arrays:

<?php
$plan_details = $plans->fetch_transpose();

Transposing a result set of X rows each with Y columns will result in an array of Y rows each with X columns.

Pass a column name as argument to return each column as an associative array with keys taken from values of the provided column. If not provided, the keys will be numeric starting from zero.

fetch_pairs

Returns all rows at once as key-value pairs using the column in the first argument as the key:

<?php
$countries = $result->fetch_pairs('id');

Pass a column name as the second argument to only return a single column as the value in each pair:

<?php
$countries = $result->fetch_pairs('id', 'name');

fetch_groups

Returns all rows at once as a grouped array:

<?php
$students_grouped_by_gender = $result->fetch_groups('gender');

Pass a column name as the second argument to only return single columns as the values in each groups:

<?php
$student_names_grouped_by_gender = $result->fetch_groups('gender', 'name');

first

Returns the first row element from the result:

<?php
$first = $result->first();

Pass a column name as argument to return a single column from the first row:

<?php
$name = $result->first('name');

last

Returns the last row element from the result:

<?php
$last = $result->last();

Pass a column name as argument to return a single column from the last row:

<?php
$name = $result->last('name');

slice

Returns a slice of rows from the result:

<?php
$slice = $result->slice(1, 10);

The above will return 10 rows skipping the first one. The first parameter is the zero-based offset; the second parameter is the number of elements; the third parameter is a boolean value to indicate whether to preserve the keys or not (optional and defaults to false). This methods essentially behaves the same as PHP's built-in array_slice() function.

map

Sets a mapper callback function that's used inside the Result::fetch method:

<?php
$result->map(function($row) {
    return (object) $row;
});
$objects = $result->fetch_all();

The above example will map all rows returned as arrays from the result set to objects. Set the mapper callback function to null to disable it.

CRUD helpers

Select

Selects rows from a table according to a where clause.

Parameters:

• $table: The table name.
• $where: Where-clause; can contain placeholders.
• $args: Array of key-value bindings for the where-clause.

The following:

<?php
$db->select('posts', 'published = :published', array('published' => true));

Will execute the SQL:

SELECT * FROM `posts` WHERE `published` = 1;

The $where parameter can also be an array of simple key-value comparisons. The following is equivalent to the above:

<?php
$db->select('posts', array('published' => true));

Insert

Inserts a row into a table. Returns true on success, false otherwise.

Parameters:

• $table: The table name.
• $data: The row array to insert.
• $insert_id: The last insert ID, optional.

The following:

<?php
$db->insert('posts', array(
    'title' => 'This is a new post!',
    'body'  => 'How convenient.',
), $id);
echo 'Last insert id = ' . $id;

Will execute the SQL:

INSERT INTO `posts` (`title`, `body`) VALUES ('This is a new post!', 'How convenient.');

To manually get the last insert ID:

<?php
if ($db->insert('posts', $post)) {
    $id = $db->insert_id();
    echo 'The last insert ID is ' . $id;
}

Update

Updates a row in a table. Returns true on success, false otherwise.

Parameters:

• $table: The table name.
• $data: The row array to update.
• $where: Where-clause; can contain placeholders.
• $args: Array of key-value bindings for the where-clause.

The following:

<?php
$db->update('posts',
    array(
        'title' => 'Lets change the title',
    ),
    '`id` = :id AND `published` = :published',
    array('id' => 42, 'published' => true)
);

Will execute the SQL:

UPDATE `posts` SET `title` = 'Lets change the title' WHERE `id` = 42 AND `published` = 1;

The $where parameter can also be an array of simple key-value comparisons. The following is equivalent to the above:

<?php
$db->update('posts',
    array('title' => 'Lets change the title'),
    array('id' => 42, 'published' => true)
);

Upsert

Upsert is MySQL's INSERT INTO ... ON DUPLICATE KEY UPDATE ... construct. Returns true on success, false otherwise.

Parameters:

• $table: The table name.
• $data: The row array to insert.
• $update: Update-clause; can contain placeholders.
• $args: Array of key-value bindings for the duplicate-clause.
• $insert_id: The last insert ID, optional.

The following:

<?php
$db->upsert('posts',
    array(
        'id' => 1,
        'title' => 'First Post!'
    ),
    '`title` = :title',
    array('title' => 'Update: First Post!')
);

Will execute the SQL:

INSERT INTO `posts` (`id`, `title`) VALUES (1, 'First Post!') ON DUPLICATE KEY UPDATE `title` = 'Update: First Post!';

As in the Database::update() method, the $update parameter can also be an array of simple key-value comparisons. The following is equivalent to the above:

<?php
$db->upsert('posts',
    array(
        'id' => 1,
        'title' => 'First Post!'
    ),
    array('title' => 'Update: First Post!')
);

Delete

Deletes a row in a table. Returns true on success, false otherwise.

Parameters:

• $table: The table name.
• $where: Where-clause; can contain placeholders.
• $args: Array of key-value bindings for the where-clause.

The following:

<?php
$db->delete('posts', '`published` = :published', array('published' => true));

Will execute the SQL:

DELETE FROM `posts` WHERE `published` = 1;

As in the Database::update() method, the $where parameter can also be an array of simple key-value comparisons. The following is equivalent to the above:

<?php
$db->delete('posts', array('published' => true));

Replace

Replace is MySQL's extension of SQL which is equivalent to insert or delete and then re-insert if row exists. Returns true on success, false otherwise.

Parameters:

• $table: The table name.
• $data: Array of column-value pairs of data.
• $insert_id: The last insert ID, optional.

The following:

<?php
$db->replace('posts',
    array('id' => 1, 'title' => 'Override', 'body' => 'test.')
);

Will execute the SQL:

REPLACE INTO `posts` (`id`, `title`, `body`) VALUES (1, 'Override', 'test.');

Literal parameters

Sometimes it is necessary to pass literal strings as part of a key-value array argument for the various CRUD methods above. Use the Database::literal() method as follows:

<?php
$db->insert('posts', array(
    'title'      => $db->literal('CONCAT(:a, :b)', array('a' => 'Hello, ', 'b' => 'World!')),
    'created_at' => $db->literal('NOW()')
));

Will execute the SQL:

INSERT INTO `posts` (`title`, `created_at`) VALUES (CONCAT('Hello, ', 'World'), NOW());

Literal values will not be escaped. They will be inserted into the query as is. You may use literal helpers anywhere key-value arrays can be used as arguments to CRUD helper methods. As can be seen from the example above, you can pass a key-value array as the second argument to Database::literal() so that it will safely replace all placeholders found in the literal string with the values in the array before marking it as a literal and returning a Literal object. This way you can still use the CRUD helper methods to construct quite complicated inserts or updates without resolving to the Database::query() method.

Truncate

Truncates a table. Returns true on success, false otherwise.

Parameters:

• $table: The table name.
• $auto_increment: Auto-increment number; optional, defaults to 1.

The following:

<?php
$db->truncate('posts');

Will execute the SQL:

TRUNCATE `posts`;
ALTER TABLE `posts` AUTO_INCREMENT = 1;

Unit Tests

To run the accompanying phpunit unit tests, you will need to provide a database connection in the phpunit.xml file (please see the phpunit.xml.dist file).

License

Dabble is released under the MIT License.