README

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
	$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->delete($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
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
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 for disabled code style rules. See phpmd.xml and .php_cs.dist.

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 - 2018 Josantonius, josantonius.com

If you find it useful, let me know 😉

You can contact me on Twitter or through my email.

josantonius / database

Maintainers

Details

README

Requirements

Installation

Get connection

- Get connection:

Query

- Process query and prepare it for the provider:

CREATE TABLE

- CREATE TABLE statement:

SELECT

- SELECT statement:

INSERT INTO

- INSERT INTO statement:

UPDATE

- UPDATE statement:

REPLACE

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

DELETE

- DELETE statement:

TRUNCATE TABLE

- TRUNCATE TABLE statement:

DROP TABLE

- DROP TABLE statement:

Quick Start

Tests

☑ TODO

Exception Handler

Contribute

Repository

License

Copyright