A very simple and usefull database wrapper, very similar to Zend_Db
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
examples
lib/Elegance
.gitignore
LICENSE
README.md

README.md

Welcome to the Elegance Database Wrapper

What is this?

Elegance_Db is a PHP class built over the pdo_mysql extension and It's aimed to provide a simple API that supports the most basic(and most frequently used) operations with MySQL.

Any similarities with Zend_Db are not a accidental. You can look at Elegance_Db as a light version of Zend_Db_Adapter_Pdo_Mysql. Elegance_Db is inspired by Zend_Db, but It's designed for small and simple projects.

Elegance Database Wrapper is maintained by Joan-Alexander Grigorov a.k.a Sasquatch

This project is subject to the GNU General Public License v3, so you can modify it, redistribute it, print it, burn it, take a sh*t on it, or whatever you want.

How is Elegance_Db better?

It's not. It's just lighter and smaller than the most libraries.

There are many database libraries (AdoDB, Zend_Db, Doctrine, Propel) which are great, but as I said before, Elegance_Db is very small, very simple and yet - very handy.

Configuration

How to setup Elegance_Db

<?php
require_once 'Elegance/Db.php';
// Somewhere in your project configuration
$db = new Elegance_Db(new PDO(
    'mysql:host=HOSTNAME;dbname=DATABASE_NAME',
    'USERNAME',
    'PASSWORD'
));

Documentation

query()

query() is used to execute a sql query and bind parameters to it.

Parameters

This method accepts the following arguments:

  • $sql - The SQL Query itself
  • $bind - An array with parameters to bind to the placeholders in the query (by default you can use "?" as a placeholder)
  • $driverOptions - Used to configure placeholders for the $bind array. More information here

Return values

query() returns a PDOStatement object

Examples

<?php
$db->query('UPDATE `artists` SET `theBest` = 1 WHERE `name` = ?', array('Maceo Parker'));

This query is automatically prepared and executed

Example with more parameters binded:

<?php
$sql = 'INSERT INTO `records` (`name`, `author`, `year`) VALUES (?, ?, ?)';
$db->query($sql, array('Kind of blue', 'Miles Davis', 1959));

Example with using named parameters:

<?php
$sql = 'DELETE FROM `good_hip_hop_artists` WHERE `name` = :name';
$db->query($sql, array(':name' => 'Lil Wayne'), array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

fetchRow()

fetchRow() is used to retrive a single row from the database

Parameters

  • $sql - The SQL Query itself
  • $fetchStyle - Used to set as what (array,object) the row will be returned. You can choose from the following styles: PDO::FETCH_ASSOC, PDO::FETCH_BOTH, PDO::FETCH_BOUND, PDO::FETCH_CLASS, PDO::FETCH_INTO, PDO::FETCH_LAZY, PDO::FETCH_NUM, PDO::FETCH_OBJ. Detailed information about each could be found here
  • $bind - An array with parameters to bind to the placeholders in the query (by default you can use "?" as a placeholder)
  • $driverOptions - Used to configure placeholders for the $bind array. More information here

Return values

Depends on the $fetchStyle which is used. By default PDO::FETCH_OBJ is used. Look at the examples bellow for more information.

Examples

<?php
$row = $db->fetchRow('SELECT * FROM `rappers` WHERE `wack` = 1');
echo 'Name: ' . $row->name;

Output:

Name: Drake

We can bind parameters here also:

<?php
$row = $db->fetchRow('SELECT * FROM `rappers` WHERE `name` = ?', array('Big Daddy Kane'));
echo 'Description: ' . $row->description;

Output:

Description: Very valuable music with good lyrics

Let's use different $fetchStyle. The following example demonstrates the usage of PDO::FETCH_ASSOC(fetches as an array where columnName = rowValue):

<?php
$sql = 'SELECT * FROM `samples` WHERE `track` = ? AND `artist` = ?';
$row = $db->fetchRow($sql, array('Suicidal Thoughts', 'The Notorious B.I.G'), PDO::FETCH_ASSOC);
echo 'Sample used: ' . $row['sampledTrack'];

Output:

Sample used: Miles Davis - Lonley fire

fetchAll()

fetchAll() is almost the same as fetchRow() but returns an array of rows(here the $fetchStyle option refers to every single row in the array, fetchAll() always returns an array).

Parameters

  • $sql - The SQL Query itself
  • $fetchStyle - Used to set as what (array,object) the rowa in the rowset will be fetched. You can choose from the following styles: PDO::FETCH_ASSOC, PDO::FETCH_BOTH, PDO::FETCH_BOUND, PDO::FETCH_CLASS, PDO::FETCH_INTO, PDO::FETCH_LAZY, PDO::FETCH_NUM, PDO::FETCH_OBJ. Detailed information about each could be found here
  • $bind - An array with parameters to bind to the placeholders in the query (by default you can use "?" as a placeholder)
  • $driverOptions - Used to configure placeholders for the $bind array. More information here

Return values

Always return an array with rows, fetched using the given $fetchStyle (uses PDO::FETCH_OBJ by default)

Examples

<?php
$rows = $db->fetchAll('SELECT * FROM `my_fav_albums` WHERE `year` < ?', array(1990));
var_dump($rows);

Output:

array(4) {
  [0]=>
  object(stdClass)#4 (4) {
    ["id"]=>
    string(1) "1"
    ["name"]=>
    string(18) "Long Live The Kane"
    ["year"]=>
    string(4) "1988"
    ["author"]=>
    string(14) "Big Daddy Kane"
  }
  [1]=>
  object(stdClass)#5 (4) {
    ["id"]=>
    string(1) "2"
    ["name"]=>
    string(44) "Freedom of Speech... Just Watch What You Say"
    ["year"]=>
    string(4) "1989"
    ["author"]=>
    string(5) "Ice-T"
  }
  [2]=>
  object(stdClass)#6 (4) {
    ["id"]=>
    string(1) "3"
    ["name"]=>
    string(20) "Tougher Than Leather"
    ["year"]=>
    string(4) "1988"
    ["author"]=>
    string(7) "Run DMC"
  }
  [3]=>
  object(stdClass)#7 (4) {
    ["id"]=>
    string(1) "4"
    ["name"]=>
    string(19) "Beneath The Remains"
    ["year"]=>
    string(4) "1989"
    ["author"]=>
    string(9) "Sepultura"
  }
}

Example with foreach:

<?php
$rows = $db->fetchAll('SELECT * FROM `my_fav_albums` WHERE `year` < ?', array(1990));

foreach ($rows as $row) {
    echo 'Album: ' . $row->name . '; Year: ' . $row->year . '; By: ' . $row->author . PHP_EOL;
}

Output:

Album: Long Live The Kane; Year: 1988; By: Big Daddy Kane
Album: Freedom of Speech... Just Watch What You Say; Year: 1989; By: Ice-T
Album: Tougher Than Leather; Year: 1988; By: Run DMC
Album: Beneath The Remains; Year: 1989; By: Sepultura

fetchOne()

fetchOne() is used to return the value of the first column of the first row from the result set.

Parameters

  • $sql - The SQL Query itself
  • $bind - An array with parameters to bind to the placeholders in the query (by default you can use "?" as a placeholder)
  • $driverOptions - Used to configure placeholders for the $bind array. More information here

Return values

Returns the value as a string of the first column of the first row from the result set

Examples

<?php
$name = $db->fetchOne('SELECT author FROM `my_fav_albums` WHERE `id` = ?', array(4));
echo 'My favorite trash band is ' . $name;

Output:

My favorite trash band is Sepultura

fetchPair()

fetchPair() is used to fetch a select query as an array of key-value pairs. For array keys are used the values of the first column in the select list, and for a values the value of the second column.

Parameters

  • $sql - The SQL Query itself
  • $bind - An array with parameters to bind to the placeholders in the query (by default you can use "?" as a placeholder)
  • $driverOptions - Used to configure placeholders for the $bind array. More information here

Return values

Always returns array. If the results set from the query is empty, the returned array is also empty.

Examples

<?php
$pairs = $db->fetchPair('SELECT `id`, `name` FROM `my_fav_albums`');
var_dump($pairs);

Output:

array(4) {
  [1]=>
  string(18) "Long Live The Kane"
  [2]=>
  string(44) "Freedom of Speech... Just Watch What You Say"
  [3]=>
  string(20) "Tougher Than Leather"
  [4]=>
  string(19) "Beneath The Remains"
}

Using other column as a key:

<?php
$pairs = $db->fetchPair('SELECT `author`, `name` FROM `my_fav_albums`');
var_dump($pairs);

Output:

array(4) {
  ["Big Daddy Kane"]=>
  string(18) "Long Live The Kane"
  ["Ice-T"]=>
  string(44) "Freedom of Speech... Just Watch What You Say"
  ["Run DMC"]=>
  string(20) "Tougher Than Leather"
  ["Sepultura"]=>
  string(19) "Beneath The Remains"
}

insert()

insert() is used to quickly add row to the database, but you don't have to write any SQL.

Parameters

  • $table - Table to use (table name)
  • $bind - Array of data to insert
  • $isIgnore - If true - creates INSERT IGNORE query (false by default)

Return values

Returns last inserted primary key value

Examples

<?php
$data = array(
    'name' => 'All we got is uz',
    'author' => 'Onyx',
    'year' => 1995
);
$id = $db->insert('records', $data);

Using the $isIgnore flag:

<?php
// Let's assume that there is a UNIQUE index on name and author
$data = array(
    'name' => 'Dead Serious',
    'author' => 'Das EFX',
    'year' => 1992
);
$db->insert('records', $data);
// If we try to run the same again, it won't work, 
// but any errors from duplicate key will be ignored
$db->insert('records', $data, true);

update()

update() is very similar to insert(), but (as you probably guessed) is used for updating row(s).

Parameters

  • $table - Table to use (table name)
  • $bind - Array of data to insert
  • $where - A typical WHERE condition as a string. An array could be passed for binding parameters. Look at the examples for more information.
  • $limit - Limit the updated rows. If 0 - no limit is applied

Return values

Returns the count of the affected rows

Examples

<?php
$data = array(
    'status' => 'destroyed',
    'intendedFor' => 'Inteligent people'
);
$db->update('music', $data, '`genre` = \'Real hip hop\'');

Using where as an array of column-value pairs and a limit update up to 3 rows

<?php
$data = array(
    'status' => 'destroyed',
    'intendedFor' => 'People you don\'t wanna mess with'
);
$db->update('music', $data, array('`genre` = ?' => 'Hardcore rap'), 3);

delete()

delete() is very similar to update(), but (as as you probably guessed again) is used for row(s) removal.

Parameters

  • $table - Table to use (table name)
  • $where - A typical WHERE condition as a string. An array could be passed for binding parameters. Look at the examples for more information.
  • $limit - Limit the updated rows. If 0 - no limit is applied

Return values

Returns the count of the affected rows

Examples

<?php
$db->delete('music', '`genre` = \'dubstep\'');

Using column-value pairs for the WHERE clause and limit up to 100 rows

<?php
$db->delete('music', array('`genre` = ?' => 'hiphop', '`topic` = ?' => 'Money, b*tches, cars'), 100);

Ok... at this point I'm probably on some party, getting drunk and sh*t, so... to be continued...