phossa2/db

DB connection, management, statistics library for PHP.

phossa2/db

, _(*1)

phossa2/db is a PHP db connection management library which handles the interaction with db., _(*2)

It requires PHP 5.4, supports PHP 7.0+ and HHVM. It is compliant with PSR-1, PSR-2, PSR-3, PSR-4, and the proposed PSR-5., _(*3)

Features

• Simple interface. Nothing you don't need., _(*4)

• Multiple db platform/driver support, currently PDO (all PDO drivers) and Mysqli., _(*5)

• Handles multiple connections through driver manager, _(*6)

  • Round-robin load balancing

  Multiple db connections are used in round-robin fashion and weighting factor (1-10) supported. Each connection is monitored (pinged)., _(*7)

  • driver tagging, so user can tag different db connection as 'reader' or 'writer' etc.

• Easy profiling, get each executed sql and its execution time., _(*8)

• Secure. All SQL executed through prepare/execute in low-level drivers., _(*9)

Installation

Install via the composer utility., _(*10)

composer require "phossa2/db"

or add the following lines to your composer.json, _(*11)

{
    "require": {
       "phossa2/db": "2.*"
    }
}

Usage

• Driver, _(*12)

  • DDL

  use Phossa2\Db\Driver\Pdo\Driver as Pdo_Driver;
  
  $db = new Pdo_Driver([
      'dsn' => 'mysql:dbname=test;host=127.0.0.1;charset=utf8'
  ]);
  
  // simple delete
  if ($db->query("DELETE FROM test WHERE id < 10")) {
      echo sprintf("%d records deleted", $db->affectedRows()) . \PHP_EOL;
  } else {
      echo $db->getError() . \PHP_EOL;
  }
  
  // with parameters
  if ($db->query("INSERT INTO test (val) VALUES (?)", [ 100 ])) {
      echo sprintf("last id is %d", $db->lastInsertId()) . \PHP_EOL;
  } else {
      echo $db->getError() . \PHP_EOL;
  }
  

  • SELECT

  // simple select
  if ($db->query("SELECT * FROM test WHERE id < 10")) {
      $rows = $db->getResult()->fetchAll();
  } else {
      echo $db->getError() . \PHP_EOL;
  }
  
  // fetch first 5 rows
  if ($db->query("SELECT * FROM test WHERE id > ? LIMIT ?", [10, 20])) {
      $rows = $db->getResult()->fetchRow(5);
  }
  
  // fetch first field
  if ($db->query("SELECT id, name FROM test WHERE id < :id", ['id' => 10])) {
      $cols = $db->getResult()->fetchCol('id');
  }
  

• Statment, _(*13)

  Statement is returned after $db->prepare()., _(*14)

  // PREPARE using prepare()
  if ($db->prepare("SELECT * FROM test WHERE id < :id")) {
    $stmt = $db->getStatement();
    if ($stmt->execute(['id' => 10])) {
        $rows = $stmt->getResult()->fetchAll();
    }
  } else {
    echo $db->getError() . \PHP_EOL;
  }
  

• Result, _(*15)

  Result is returned by $db->getResult() or $stmt->getResult(), _(*16)

  if ($db->query('SELECT * FROM test')) {
    // SELECT
    if ($db->getResult()->isSelect()) {
        // get fields count
        $fieldCount = $db->getResult()->fieldCount();
        // row count
        $rowCount = $db->getResult()->rowCount();
  
    // DDL
    } else {
        $affectedRows = $db->getResult()->affectedRows();
    }
  }
  

Mysqli

Mysqli driver is also supported., _(*17)

use Phossa2\Db\Driver\Mysqli\Driver as Mysqli_Driver;

$db = new Mysqli_Driver([
    'db' => 'mysql',
    'host' => '127.0.0.1',
    'charset' => 'utf8'
]);

// simple delete
if ($db->query("DELETE FROM test WHERE id < ?", [10])) {
    echo sprintf("%d records deleted", $db->affectedRows()) . \PHP_EOL;
} else {
    echo $db->getError() . \PHP_EOL;
}

Note: named parameters are not supported in Mysqli driver., _(*18)

Driver manager

Driver manager manages multiple db connections. Weighting factor N means add one driver virtually N times. Adding driver A with factor 5 and adding driver B with factor 1 into the pool, means when calling getDriver(), user will get A five times vs B for one time., _(*19)

// dbwriter 1
$db1 = (new Phossa2\Db\Driver\Pdo\Driver($conf1))->addTag('RW');

// dbreader 2
$db2 = (new Phossa2\Db\Driver\Pdo\Driver($conf2))->addTag('RO');

// dbreader 3
$db3 = (new Phossa2\Db\Driver\Pdo\Driver($conf3))->addTag('RO');

// db manager
$dbm = (new Phossa2\Db\Manager\Manager())
    ->addDriver($db1, 1)    // writable connection with factor 1
    ->addDriver($db2, 5)    // read_only, factor 5
    ->addDriver($db3, 5)    // read_only, factor 5

// get a db connect, no matter writer or reader
$db = $dbm->getDriver();

// get a readonly driver
$db = $dbm->getDriver('RO');

SQL profiling

Get the executed SQL and its execution time., _(*20)

// init driver
$db = new Phossa2\Db\Driver\Pdo\Driver($conf);

// enable profiling
$db->enableProfiling();

// execute a DELETE
$db->query("DELETE FROM test WHERE test_id > 10");

// get sql
$sql = $db->getProfiler()->getSql();
$time = $db->getProfiler()->getExecutionTime();

Method overloading

Methods from Phossa2\Db\Interfaces\ResultInterface can be accessed through the db driver after successful execution of a query., _(*21)

if ($db->query('SELECT * FROM test')) {
    // normally is $db->getResult()->fetchAll()
    $rows = $db->fetchAll();
}

Change log

Please see CHANGELOG from more information., _(*22)

Testing

$ composer test

Contributing

Please see CONTRIBUTE for more information., _(*23)

Dependencies

• PHP >= 5.4.0, _(*24)

• phossa2/shared >= 2.0.25, _(*25)

License

MIT License, _(*26)