Dabble is a lightweight wrapper and collection of helpers for MySQLi.
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+.
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.
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);
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.
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));
});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.';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.
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.';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.
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.
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');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');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.
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');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');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');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');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.
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.
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));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;
}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 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!')
);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 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.');
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.
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;
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).
Dabble is released under the MIT License.