PHP Classes
elePHPant
Icontem

PHP Database: Execute common SQL queries with a fluent interface

Recommend this page to a friend!
  Info   View files Documentation   View files View files (26)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Last Updated Ratings Unique User Downloads Download Rankings
2017-12-06 (15 hours ago) RSS 2.0 feedNot enough user ratingsTotal: 152 This week: 23All time: 8,425 This week: 27Up
Version License PHP version Categories
php-database 1.1.8Custom (specified...5.6PHP 5, Databases
Description Author

This package can execute common SQL queries with a fluent interface.

It can connect to a given database using PDO or the Microsoft SQL Server extension.

The class can also compose and execute SQL SELECT, INSERT, UPDATE and DELETE queries using a fluent interface of functions for setting the parameters like the table, fields, values, condition clauses, etc..

  Performance   Level  
Name: Josantonius <contact>
Classes: 18 packages by
Country: Spain Spain
Innovation award
Innovation award
Nominee: 8x

Details

PHP Database library

Latest Stable Version Latest Unstable Version License Codacy Badge Total Downloads Travis PSR2 PSR4 CodeCov

Versión en español

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

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.

  Files folder image Files  
File Role Description
Files folder imagesrc (1 file, 2 directories)
Files folder imagetests (9 files)
Accessible without login Plain text file .editorconfig Data Auxiliary data
Accessible without login Plain text file .php_cs.dist Example Example script
Accessible without login Plain text file .travis.yml Data Auxiliary data
Accessible without login Plain text file CHANGELOG.md Data Auxiliary data
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file CONDUCT.md Data Auxiliary data
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file phpcs.xml Data Auxiliary data
Accessible without login Plain text file phpmd.xml Data Auxiliary data
Accessible without login Plain text file phpunit.xml Data Auxiliary data
Accessible without login Plain text file README-ES.md Doc. Documentation
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files  /  src  
File Role Description
Files folder imageException (1 file)
Files folder imageProvider (3 files)
  Plain text file Database.php Class Class source

  Files folder image Files  /  src  /  Exception  
File Role Description
  Plain text file DBException.php Class Class source

  Files folder image Files  /  src  /  Provider  
File Role Description
  Plain text file MSSQLprovider.php Class Class source
  Plain text file PDOprovider.php Class Class source
  Plain text file Provider.php Class Class source

  Files folder image Files  /  tests  
File Role Description
  Plain text file ConnectionTest.php Class Class source
  Plain text file CreateTest.php Class Class source
  Plain text file DeleteTest.php Class Class source
  Plain text file DropTest.php Class Class source
  Plain text file InsertTest.php Class Class source
  Plain text file ReplaceTest.php Class Class source
  Plain text file SelectTest.php Class Class source
  Plain text file TruncateTest.php Class Class source
  Plain text file UpdateTest.php Class Class source

 Version Control Unique User Downloads Download Rankings  
 100%
Total:152
This week:23
All time:8,425
This week:27Up