PHP Database library

Versión en español

SQL database management to be used by several providers at the same time.

• Requirements
• Installation
• Quick Start
• Get connection
• CREATE TABLE
• SELECT
• INSERT INTO
• UPDATE
• REPLACE
• DELETE
• TRUNCATE TABLE
• DROP TABLE
• Tests
• TODO
• Exception Handler
• Contribute
• Repository
• License
• Copyright

Requirements

This library is supported by PHP versions 5.6 or higher and is compatible with HHVM versions 3.0 or higher.

Installation

The preferred way to install this extension is through Composer.

To install PHP Database library, simply:

$ composer require Josantonius/Database

The previous command will only install the necessary files, if you prefer to download the entire source code you can use:

$ composer require Josantonius/Database --prefer-source

You can also clone the complete repository with Git:

$ git clone https://github.com/Josantonius/PHP-Database.git

Or install it manually:

Download Database.php, Provider.php, PDOprovider.php, MSSQLprovider.php and DBException.php:

$ wget https://raw.githubusercontent.com/Josantonius/PHP-Database/master/src/Database.php
$ wget https://raw.githubusercontent.com/Josantonius/PHP-Database/master/src/Provider/Provider.php
$ wget https://raw.githubusercontent.com/Josantonius/PHP-Database/master/src/Provider/PDOprovider.php
$ wget https://raw.githubusercontent.com/Josantonius/PHP-Database/master/src/Provider/MSSQLprovider.php
$ wget https://raw.githubusercontent.com/Josantonius/PHP-Database/master/src/Exception/DBException.php

Get connection

- Get connection:

Database::getConnection($id, $provider, $host, $user, $name, $password, $settings);

| Attribute | Description | Type | Required | Default | --- | --- | --- | --- | --- | | $id | Database unique ID. | string | Yes | | | $provider | Name of provider class. | string | No | null | | $host | Database host. | string | No | null | | $user | Database user. | string | No | null | | $name | Database name. | string | No | null | | $password | Database password . | string | No | null |

| Attribute | Key | Description | Type | Required | Default | --- | --- | --- | --- | --- | --- | | $settings | | Database options. | array | No | null | | $settings | 'port' | Database port. | string | No | | | $settings | 'charset' | Database charset. | string | No | |

# Return (object) ? object with the connection

$db = Database::getConnection(
    'identifier',  # Unique identifier
    'PDOprovider', # Database provider name
    'localhost',   # Database server
    'db-user',     # Database user
    'db-name',     # Database name
    'password',    # Database password
    array('charset' => 'utf8')
);

$externalDB = Database::getConnection(
    'external',          # Unique identifier
    'PDOprovider',       # Database provider name
    'http://site.com',   # Database server
    'db-user',           # Database user
    'db-name',           # Database name
    'password',          # Database password
    array('charset' => 'utf8')
);

// And once the connection is established:

$db = Database::getConnection('identifier');

$externalDB = Database::getConnection('external');

Query

- Process query and prepare it for the provider:

$db->query($query, $statements, $result);

| Attribute | Description | Type | Required | Default | --- | --- | --- | --- | --- | | $query | Query. | string | Yes | | | $statements | Statements. | array | No | null | | $result | Query result; 'obj', 'array_num', 'array_assoc', 'rows', 'id'. | string | No | 'obj' |

# Return (mixed) ? result as object, array, int...

# throws [DBException] ? invalid query type

$db->query(
    'CREATE TABLE test (
        id    INT(6)      PRIMARY KEY,
        name  VARCHAR(30) NOT NULL,
        email VARCHAR(50)
    )'
);

$db->query(
    'SELECT id, name, email
     FROM test',
    false,
    'array_assoc' // array_assoc, obj, array_num
);

$statements[] = [1, "Many"];
$statements[] = [2, "many@email.com"];
        
$db->query(
    'INSERT INTO test (name, email)
     VALUES (?, ?)',
    $statements,
    'id' // id, rows
);

CREATE TABLE

- CREATE TABLE statement:

$db->create($data)
   ->table($table)
   ->foreing($id)
   ->reference($table)
   ->on($table)
   ->actions($action)
   ->engine($type)
   ->charset($type)
   ->execute();

| Method | Attribute | Description | Type | Required | Default | --- | --- | --- | --- | --- | --- | | | $data | Column name and configuration for data types. | array | Yes | | | table() | | Set database table name. | method | Yes | | | | $table | Table name. | string | Yes | | | foreing() | | Set foreing key. | method | No | | | | $id | Column id. | string | Yes | | | reference() | | Set reference for foreing keys. | method | No | | | | $table | Table name. | array | Yes | | | on() | | Set database table name. | method | No | | | | $table | Table name. | array | Yes | | | actions() | | Set actions when delete or update for foreing key. | method | No | | | | $action | Action when delete or update. | array | Yes | | | engine() | | Set table engine. | method | No | | | | $type | Engine type. | string | Yes | | | charset() | | Set table charset. | method | No | | | | $type | Charset type. | string | Yes | | | execute() | | Execute query. | method | Yes | |

# Return (boolean)

$params = [
    'id'    => 'INT(6) PRIMARY KEY',
    'name'  => 'VARCHAR(30) NOT NULL',
    'email' => 'VARCHAR(50)'
];

$query = $db->create($params)
            ->table('test')
            ->execute();

$db->create($params)
   ->table('test_two')
   ->foreing('id')
   ->reference('id')
   ->on('test')
   ->actions('ON DELETE CASCADE ON UPDATE CASCADE')
   ->engine('innodb')
   ->charset('utf8')
   ->execute();

SELECT

- SELECT statement:

$db->select($columns)
   ->from($table)
   ->where($clauses, $statements)
   ->order($type)
   ->limit($number)
   ->execute($result);

| Method | Attribute | Description | Type | Required | Default | --- | --- | --- | --- | --- | --- | | | $columns | Column/s name. | mixed | No | '*' | | from() | | Set database table name. | method | Yes | | | | $table | Table name. | string | Yes | | | where() | | Where clauses. | method | No | | | | $clauses | Column name and value. | mixed | Yes | | | | $statements | Statements. | array | No | null | | order() | | Order. | method | No | | | | $type | Query sort parameters. | string | Yes | | | limit() | | Limit. | method | No | | | | $number | Number. | int | Yes | | | execute() | | Execute query. | method | Yes | | | | $result | Query result; 'obj', 'array_num', 'array_assoc', 'rows'. | string | No | 'obj' |

# Return (mixed) ? query result (object, array, int...) or rows affected

#SELECT all
$db->select()
    ->from('test')
    ->execute('array_num');

#SELECT with all params
$db->select(['id', 'name'])
   ->from('test')
   ->where(['id = 4885', 'name = "Joe"'])
   ->order(['id DESC', 'name ASC'])
   ->limit(1)
   ->execute('obj');

#SELECT with statements
$statements[] = [1, 3008];
$statements[] = [2, 'Manny'];
        
$db->select('name')
   ->from('test')
   ->where('id = ? OR name = ?', $statements)
   ->execute('rows');

#Other version of SELECT with statements
$statements[] = [':id', 8, 'int'];
$statements[] = [':email', null, 'null'];

$clauses = [
    'id    = :id',
    'email = :email'
];

$db->select('name')
   ->from('test')
   ->where($clauses, $statements)
   ->execute('rows');

INSERT INTO

- INSERT INTO statement:

$db->insert($data, $statements)
   ->in($table)
   ->execute($result);

| Method | Attribute | Description | Type | Required | Default | --- | --- | --- | --- | --- | --- | | | $data | Column name and value. | array | Yes | | | | $statements | Statements. | array | No | null | | in() | | Set database table name. | method | Yes | | | | $table | Table name. | string | Yes | | | execute() | | Execute query. | method | Yes | | | | $result | Query result; 'rows', 'id'. | string | No | 'rows' |

# Return (int) ? rows affected or last row affected ID

#INSERT INTO basic example
$data = [
    "name"  => "Isis",
    "email" => "isis@email.com",
];
        
$db->insert($data)
   ->in('test')
   ->execute();

#INSERT INTO with statements
$data = [
    "name"  => "?",
    "email" => "?",
];

$statements[] = [1, "Isis"];
$statements[] = [2, "isis@email.com"];

$db->insert($data, $statements)
   ->in('test')
   ->execute('rows');

#Other version of INSERT INTO with statements
$data = [
    "name"  => ":name",
    "email" => ":email",
];

$statements[] = [":name", "Isis", "str"];
$statements[] = [":email", "isis@email.com", "str"];

$db->insert($data, $statements)
   ->in('test')
   ->execute('id');

UPDATE

- UPDATE statement:

$db->update($data, $statements)
   ->in($table)
   ->where($clauses, $statements)
   ->execute();

| Method | Attribute | Description | Type | Required | Default | --- | --- | --- | --- | --- | --- | | | $data | Column name and value. | array | Yes | | | | $statements | Statements. | array | No | null | | in() | | Set database table name. | method | Yes | | | | $table | Table name. | string | Yes | | | where() | | Where clauses. | method | No | | | | $clauses | Column name and value. | mixed | Yes | | | | $statements | Statements. | array | No | null | | execute() | | Execute query. | method | Yes | |

# Return (int) ? rows affected

#UPDATE basic example
$data = [
    'name'  => 'Isis',
    'email' => 'isis@email.com',
];

$db->update($data)
   ->in('test')
   ->execute();

#UPDATE with WHERE
$data = [
    'name'  => 'Manny',
    'email' => 'manny@email.com',
];

$clauses = [
    'name  = "isis"',
    'email = "isis@email.com"'
];

$db->update($data)
   ->in('test')
   ->where($clauses)
   ->execute();

#UPDATE with statements
$data = [
    'name'  => '?',
    'email' => '?',
];

$statements['data'][] = [1, 'Isis'];
$statements['data'][] = [2, 'isis@email.com'];

$clauses = 'id = ? AND name = ? OR name = ?';

$statements['clauses'][] = [3, 4883];
$statements['clauses'][] = [4, 'Isis'];
$statements['clauses'][] = [5, 'Manny'];

$db->update($data, $statements['data'])
   ->in('test')
   ->where($clauses, $statements['clauses'])
   ->execute();

#Other version of UPDATE with statements
$data = [
    'name'  => ':new_name',
    'email' => ':new_email',
];

$statements['data'][] = [':new_name', 'Manny', 'str'];
$statements['data'][] = [':new_email', 'manny@email.com', 'str'];

$clauses = 'name = :name1 OR name = :name2';

$statements['clauses'][] = [':name1', 'Isis', 'str'];
$statements['clauses'][] = [':name2', 'Manny', 'str'];

$db->update($data, $statements['data'])
   ->in('test')
   ->where($clauses, $statements['clauses'])
   ->execute();

REPLACE

- Replace a row in a table if it exists or insert a new row if not exist:

$db->replace($data, $statements)
   ->from($table)
   ->execute($result);

| Method | Attribute | Description | Type | Required | Default | --- | --- | --- | --- | --- | --- | | | $data | Column name and value. | array | Yes | | | | $statements | Statements. | array | No | null | | from() | | Set database table name. | method | Yes | | | | $table | Table name. | string | Yes | | | execute() | | Execute query. | method | Yes | | | | $result | Query result; 'rows', 'id'. | string | No | 'rows' |

# Return (int) ? rows affected or last row affected ID

#REPLACE basic example
$data = [
    'id'    => 3008,
    'name'  => 'Manny',
    'email' => 'manny@email.com',
];

$db->replace($data)
   ->from('test')
   ->execute();

#UPDATE with statements
$data = [
    'id'    => 4889,
    'name'  => ':name',
    'email' => ':email',
];

$statements[] = [':name', 'Manny'];
$statements[] = [':email', 'manny@email.com'];

$db->replace($data, $statements)
   ->from('test')
   ->execute('rows');

#Other version of UPDATE with statements
$data = [
    'id'    => 2,
    'name'  => '?',
    'email' => '?',
];

$statements[] = [1, 'Manny'];
$statements[] = [2, 'manny@email.com'];

$db->replace($data, $statements)
   ->from('test')
   ->execute('id');

DELETE

- DELETE statement:

$db->replace($data, $statements)
   ->from($table)
   ->where($clauses, $statements)
   ->execute();

| Method | Attribute | Description | Type | Required | Default | --- | --- | --- | --- | --- | --- | | | $data | Column name and value. | array | Yes | | | | $statements | Statements. | array | No | null | | from() | | Set database table name. | method | Yes | | | | $table | Table name. | string | Yes | | | where() | | Where clauses. | method | No | | | | $clauses | Column name and value. | mixed | Yes | | | | $statements | Statements. | array | No | null | | execute() | | Execute query. | method | Yes | |

# Return (int) ? rows affected

#DELETE all
$db->delete()
   ->from('test')
   ->execute();

#DELETE with WHERE
$clauses = [
    'id = 4884',
    'name  = "isis"',
    'email = "isis@email.com"',
];

$db->delete()
   ->from('test')
   ->where($clauses)
   ->execute();

#DELETE with statements
$clauses = 'id = :id AND name = :name1 OR name = :name2';

$statements[] = [':id', 4885];
$statements[] = [':name1', 'Isis'];
$statements[] = [':name2', 'Manny'];

$db->delete()
   ->from('test')
   ->where($clauses, $statements)
   ->execute();

#Other version of DELETE with statements
$clauses = 'id = :id AND name = :name1 OR name = :name2';

$statements[] = [':id', 4886, 'int'];
$statements[] = [':name1', 'Isis', 'src'];
$statements[] = [':name2', 'Manny', 'src'];

$db->delete()
   ->from('test_table')
   ->where($clauses, $statements)
   ->execute();

TRUNCATE TABLE

- TRUNCATE TABLE statement:

$db->truncate()
   ->table($table)
   ->execute();

| Method | Attribute | Description | Type | Required | Default | --- | --- | --- | --- | --- | --- | | table() | | Set database table name. | method | Yes | | | | $table | Table name. | string | Yes | | | execute() | | Execute query. | method | Yes | |

# Return (boolean)

$db->truncate()
   ->table('test')
   ->execute();

DROP TABLE

- DROP TABLE statement:

$db->drop()
   ->table($table)
   ->execute();

| Method | Attribute | Description | Type | Required | Default | --- | --- | --- | --- | --- | --- | | table() | | Set database table name. | method | Yes | | | | $table | Table name. | string | Yes | | | execute() | | Execute query. | method | Yes | |

# Return (boolean)

$db->drop()
   ->table('test')
   ->execute();

Quick Start

To use this class with Composer:

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

use Josantonius\Database\Database;

Or If you installed it manually, use it:

require_once __DIR__ . '/Database.php';

use Josantonius\Database\Database;

Tests

To run tests you just need composer and to execute the following:

$ git clone https://github.com/Josantonius/PHP-Database.git

$ cd PHP-Database

$ composer install

Run unit tests with PHPUnit:

$ composer phpunit

Run PSR2 code standard tests with PHPCS:

$ composer phpcs

Run PHP Mess Detector tests to detect inconsistencies in code style:

$ composer phpmd

Run all previous tests:

$ composer tests

? TODO

• [ ] Add new feature
• [ ] Improve tests
• [ ] Improve documentation
• [ ] Refactor code

Exception Handler

This library uses exception handler that you can customize.

Contribute

If you would like to help, please take a look at the list of issues or the To Do checklist.

Pull requests

• Fork and clone.
• Run the command composer install to install the dependencies. This will also install the dev dependencies.
• Run the command composer fix to excute code standard fixers.
• Run the tests.
• Create a branch, commit, push and send me a pull request.

Repository

The file structure from this repository was created with PHP-Skeleton.

License

This project is licensed under MIT license. See the LICENSE file for more info.

Copyright

2017 Josantonius, josantonius.com

If you find it useful, let me know :wink:

You can contact me on Twitter or through my email.