KeepItSimple\Db

KeepItSimple\Db

PHP Database wrapper made simple with PDO

Under development...

A set of simple yet powerful classes to interact with your database easily and accelerate development.

Features

  • Easy to use - intuitive query builder and friendly methods.
  • Powerful - your imagination is the limit.
  • Strong compatibility - made on top of PDO.
  • Secure - each queries use prepared statements.

Installation

Via composer :

$ composer require debuss-a/db "dev-master"

Define some constants :

define('_DB_DRIVER_', 'mysql');
define('_DB_HOST_', 'localhost');
define('_DB_SOCKET_', false);
define('_DB_PORT_', '3306');
define('_DB_NAME_', 'test');
define('_DB_USER_', 'root');
define('_DB_PASSWORD_', 'root');
define('_DB_CHARSET_', 'utf8mb4');
define('_DB_COLLATION_', 'utf8mb4_general_ci');
define('_DB_PREFIX_', '');
define('_DB_FETCH_MODE_', PDO::FETCH_OBJ);

Then use it :

require __DIR__.'/vendor/autoload.php';

use KeepItSimple\Db\Db;

// Magic here

Usage

A more complete documentation is available here : https://github.com/debuss/Db/wiki.

Simple queries

Run any query directly.

SELECT

This method return an array of object or array depending on _DB_FETCHMODE\.

use KeepItSimple\Db\Db;

Db::getInstance()->select('SELECT * FROM customer WHERE id_customer = 1');

/*
array (size=1)
  0 => 
    object(stdClass)[4]
      public 'id_customer' => int 1
      public 'name' => string 'Fleur' (length=5)
      public 'surname' => string 'Donovan' (length=7)
      public 'email' => string 'semper.auctor.Mauris@elementumpurus.ca' (length=38)
      public 'date_add' => string '2016-07-29 17:27:26' (length=19)
*/

INSERT, UPDATE, DELETE

These methods return the number of affected rows.

Note : As these methods run raw queries, you are responsible for the security of what you send to the database.
Cast your variable if necessary.

use KeepItSimple\Db\Db;

Db::getInstance()->insert(
    'INSERT INTO customer VALUES (107, "Alex", "Deb", "alexdeb@email.com", "2016-10-07 12:00:00")'
);

/* Returns 1 (affected row) */

Db::getInstance()->delete('DELETE IGNORE FROM `customer` WHERE `id_customer` = '.(int)$id_customer)

Transaction

Run multiple queries with a transaction.
Note : You do not need to try/catch, the function do it already, in case of an exception occurs queries are rollbacked and false is returned.

Db::getInstance()->transaction(function () {
    Db::getInstance()->insert(
        'INSERT INTO customer VALUES (108, "Steve", "Smith", "steve@email.com", "2016-10-07 12:00:00")'
    );

    Db::getInstance()->insert(
        'INSERT INTO customer VALUES (109, "Stan", "Smith", "stan@email.com", "2016-10-07 12:00:00")'
    );

    Db::getInstance()->insert(
        'INSERT INTO customer VALUES (110, "Roger", "The alien", "roger@email.com", "2016-10-07 12:00:00")'
    );
});

Query builder

Create queries using well named methods to keep your code clean and meaningful :

  • select()
  • show()
  • distinct()
  • where()
  • isEqualTo()
  • isNotEqualTo()
  • isLessThan()
  • isLessThanOrEqualTo()
  • isGreaterThan()
  • isGreaterThanOrEqualTo()
  • contains()
  • doesNotContain()
  • beginWith()
  • endWith()
  • isNull()
  • isNotNull()
  • isEmpty()
  • isNotEmpty()
  • isBetween()
  • isNotBetween()
  • isInList()
  • isNotInList()
  • alongWith()
  • either()
  • orderBy()
  • orderByRand()
  • groupBy()
  • having()
  • desc()
  • limit()

Note : The methods alongWith() and either() correspond to AND and OR clauses respectively (because one can not create an and() and or() function in PHP).

The table method of the Db class returns an instance of DbQuery allowing one to create queries by chaining more methods.
As you can see, methods name are straightforward :

// SELECT * FROM customer
$customers = Db::getInstance()->table('customer')->getAll();
$customers = Db::getInstance()->table('customer')->select('*')->getAll();

// SELECT name, surname, email FROM customer
$customers = Db::getInstance()->table('customer')->select('name', 'surname', 'email')->getAll();

// A bit more options
$customers = Db::getInstance()->table('customer')
    ->select('name', 'surname', 'email')
    ->distinct()
    ->where('id_customer')
    ->isInList(range(0, 10))
    ->alongWith('date_add')
    ->isLessThan(2017)
    ->either('email')
    ->isEqualTo('sit@ligulaNullamenim.com')
    ->orderBy('name')
    ->desc()
    ->limit(3)
    ->get();

// Query will be :
// SELECT DISTINCT * FROM customer WHERE id_customer IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND date_add < ? OR email = ? ORDER BY name DESC LIMIT 3
// With binded parameters :
// Array
// (
//     [0] => 0
//     [1] => 1
//     [2] => 2
//     [3] => 3
//     [4] => 4
//     [5] => 5
//     [6] => 6
//     [7] => 7
//     [8] => 8
//     [9] => 9
//     [10] => 10
//     [11] => 2017
//     [12] => sit@ligulaNullamenim.com
// )

Fetch methods

Get the result from the query in various ways :

  • get()
  • getClass()
  • getInto()
  • getAll()
  • getValue()
  • getColumn()
  • getBy()
  • getChunk()
  • getFirst()
  • getLast()

Iterate results with iterators

The methods get(), getClass(), getInto() return a PDO statement so you can easily iterate on it :

foreach (Db::getInstance()->table('customer')->get() as $customer) {
    var_dump($customer);
}

As the result is a PDO statement, you still can use fetch() method to get the instance directly :

$customer1 = Db::getInstance()->table('customer')->get()->fetch();
// OR to get an instance of Customer class :
$customer1 = Db::getInstance()->table('customer')->getClass(Customer::class);

Get all results set

// Returns an array of object
$customers = Db::getInstance()->table('customer')
    ->limit(3)
    ->getAll();

// Returns an array of array
$customers = Db::getInstance()->table('customer')
    ->limit(3)
    ->getAll(true);

Get a single row

Instead of having an array as a result, get directly the first or last result in the row collection :

$customer1 = Db::getInstance()->table('customer')->getFirst();
// OR
$customer1 = Db::getInstance()->table('customer')->getLast();

Get a single column from the first row of result

// Get value of column `name`
Db::getInstance()->table('customer')
->select('name')
->where('id_customer')
->isEqualTo(1)
->getValue();

// Get value of column `surname`
Db::getInstance()->table('customer')
->select('name', 'surname', 'email')
->where('id_customer')
->isEqualTo(1)
->getValue('surname');

Get a single column from all the results

Db::getInstance()->table('customer')
    ->where('id_customer')
    ->isLessThan(2)
    ->getColumns('email');

// Output array of emails
//
// array (size=2)
//   0 => string 'semper.auctor.Mauris@elementumpurus.ca' (length=38)
//   1 => string 'ligula.consectetuer.rhoncus@est.edu' (length=35)

Get all the data and group them by $column name

Db::getInstance()->table('customer')
    ->getBy('name');

// Output
//
// array (size=108)
//  'Fleur' =>
//    array (size=2)
//      0 =>
//        object(stdClass)[6]
//          public 'id_customer' => int 1
//          public 'name' => string 'Fleur' (length=5)
//          public 'surname' => string 'Donovan' (length=7)
//          public 'email' => string 'semper.auctor.Mauris@elementumpurus.ca' (length=38)
//          public 'date_add' => string '2016-07-29 17:27:26' (length=19)
//      1 =>
//        object(stdClass)[111]
//          public 'id_customer' => int 106
//          public 'name' => string 'Fleur' (length=5)
//          public 'surname' => string 'Debussa' (length=7)
//          public 'email' => string 'semper.auctor.Mauris@elementumpurus.ca' (length=38)
//          public 'date_add' => string '2016-07-29 17:27:26' (length=19)
//  'Kiara' =>
//    array (size=1)
//      0 =>
//        object(stdClass)[7]
//          public 'id_customer' => int 2
//          public 'name' => string 'Kiara' (length=5)
//          public 'surname' => string 'Fry' (length=3)
//          public 'email' => string 'ligula.consectetuer.rhoncus@est.edu' (length=35)
//          public 'date_add' => string '2016-11-16 07:07:20' (length=19)

Get the first row of results

Db::getInstance()->table('customer')
    ->getFirst();

Get the last row of results

Db::getInstance()->table('customer')
    ->getLast();

COUNT, MIN, MAX, AVG

Db::getInstance()->table('customer')
    ->max('id_customer');

Models (ORM)

A simple yet efficient Model/ORM class is provided with DbModel.

In this example, a basic customer class extending KeepItSimple\Db\DbModel.
The $definition variable is mandatory, this is where is stored informations concerning the database table :

  • index table contains the table name
  • index primary contains the name of the primary key
  • index fields contains the table definition, possible values :
    • type MySQL data type
    • size
    • unsigned to specify UNSIGNED in the field definition
    • auto_increment True or false
    • unique to specify a unique key for the field definition
    • default default value
    • null to specify a default value in the field definition
    • required
    • validate a function name to call to validate the data in this field
namespace My\App\Model;

use KeepItSimple\Db\DbModel;

class Customer extends DbModel
{
    public $id_customer;
    public $name;
    public $surname;
    public $email;
    public $date_add;

    public static $definition = [
        'table' => 'customer',
        'primary' => 'id_customer',
        'fields' => [
            'id_customer' => [
                'type' => 'INT',
                'size' => 11,
                'unsigned' => true,
                'auto_increment' => true
            ],
            'name' => [
                'type' => 'VARCHAR',
                'size' => 32,
                'default' => 'NULL',
                'required' => true
            ],
            'surname' => [
                'type' => 'VARCHAR',
                'size' => 32,
                'default' => 'NULL',
                'required' => true
            ],
            'email' => [
                'type' => 'VARCHAR',
                'size' => 128,
                'default' => 'NULL',
                'required' => true
            ],
            'date_add' => [
                'type' => 'DATETIME',
                'default' => 'CURRENT_TIMESTAMP'
            ]
        ]
    ];

    public function __construct($id = null)
    {
        parent::__construct($id);
    }
}

Most of the methods from DbQuery class are available :

$customers = Customer::select('name', 'lastname')->where('id_customer')->isLessThan(100)->getAll();

Loading a single instance

If an ID is given to the parent constructor then DbModel will look for the row with this Id in the database and return an instance of the class with the loaded data from database.

$my_customer = new Customer(42);

Add, update, delete

Three methods exist to create, update or delete a row in the database according to the class informations.

$my_customer = new Customer();
$my_customer->name = 'Foo';
$my_customer->surname = 'bar';
$my_customer->email = 'foo.bar@baz.com';
$my_customer->add(); // Return the ID on success, false otherwise

$my_customer->name = 'New name';
$my_customer->update();

$my_customer->delete();

There is also a save() method which simply call add() method if the instance does not exists in the database or update() method if the instance already exists.

Some checking

To check if your object is properly loaded, which means if it has an ID so it exists in the database, you can use isLoadedModel() method.
Return true if the object is properly loaded, false otherwise.

To check the fields data of the object, one can use the validateField() or validateFields() methods.

$my_customer = new Customer(42);
if (!$my_customer->isLoadedModel()) {
    // Does not exist in database, create it
    $my_customer->name = 'Foo';
    $my_customer->surname = 'bar';
    $my_customer->email = 'foo.bar@baz.com';
    $my_customer->add();
}

if (!$my_customer->validateField('email')) {
    // For example, if email is "foo@bar"
    die('Woops, wrong email !');
}

try {
    $my_customer->validateField();
} catch (Exception $e) {
    die('One of the field is invalid : '.$e->getMessage());
}

Database backup

One can use the DbDump class to create backup files of all the database or only selected tables.

Note : Use with caution, do not rely on it and do not hesitate to extend it to match your requirement.
This class works for common tables and data types but most probably not for complex data types so test it thoroughly for your use case (has only been tested with MySQL).

In a file

use KeepItSimple\Db\DBDump;

// First parameters is for tables.
// Uses an array of table names, or * for all tables.
// Second parameter is the path where to dump the file.

DBDump::file('*', __DIR__.'/dump.sql');

One can generate a compressed dump file by setting the third parameter to true.

use KeepItSimple\Db\DBDump;

DBDump::file('*', __DIR__.'/dump.sql', true);

Preview dump

It is possible to get the dump content (string) without creating a file with the preview method.

use KeepItSimple\Db\DBDump;

echo DBDump::preview('*');

More examples

More examples in the examples folder.



Blog Comments powered by Disqus.