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
Supported datatypes for prepared statements
Tests
Sponsor
License

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. Once the connection is made, this configuration will be available in the database connection object: $db->settings.	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;

Supported datatypes for prepared statements

Boolean

bool
boolean

Null

null

Integer numbers

int
integer

Text strings

str
string

If any type of data that does not match the aboves, it will be validated as a string.

If no data type is specified, the data type will not be validated in the prepared query.

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

Sponsor

If this project helps you to reduce your development time, you can sponsor me to support my open source work 😊

License

This repository is licensed under the MIT License.

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

Supported datatypes for prepared statements

Boolean

Null

Integer numbers

Text strings

Tests

Sponsor

License