README

PHP Library SqlDB

PHP Library for working with SQL databases.

Features

• MySQL, SQlite and SQLiteDB (SQLite in the cloud) supported
• Unified data types. The data types are developer orientated (string, text, integer, float, blob). These are then translated to the correct column type for the corresponding database.
• Fluent interface for building queries

Installation

1. Via composer command line

composer require sinevia/php-library-sqldb

1. Via composer file:

Add the latest stable version to your composer file, and update via composer

"require": {
    "sinevia/php-library-sqldb": "3.8.0"
}

Data Types

Usage

1) Creating a new database instance

// MySQL
$db = new Sinevia\SqlDB(array(
    'database_type'=>'mysql',
    'database_name'=>'db_name',
    'database_host'=>'db_host',
    'database_user'=>'db_user',
    'database_pass'=>'db_pass'
));

// SQLite (creating a new SQLite database, if it does not exist)
$db = new Sinevia\SqlDB(array(
    'database_type'=>'sqlite',
    'database_name'=>'db_name',
    'database_host'=>'db_host',
    'database_user'=>'db_user',
    'database_pass'=>'db_pass'
));

// SQLiteDB (SQLite in the cloud)
$db = new Sinevia\SqlDB(array(
    'database_type'=>'sqlitedb',
    'database_host'=>'sqlitedb_api_url',
    'database_pass'=>'sqlitedb_api_key'
));

// Using existing PDO instance
$db = new Sinevia\SqlDB($pdo);

2) Drop a database

Depending on your database hosting this may or may not be supported

// Dropping a database
$db->drop();

3) Creating a new table

// Check if table already exists?
if ($db->table("person")->exists() == false) {
    // Create table
    $db->table("person")
        ->column("Id", "INTEGER", "NOT NULL PRIMARY KEY AUTO_INCREMENT")
        ->column("FirstName", "STRING")
        ->column("LastName", "STRING")
        ->create();
}

3) Drop a table

// Dropping a table
$isOk = $db->table("person")->drop();

3) Inserting rows

$isOk = $db->table('person')->insert([
    'FirstName' => 'Peter',
    'LastName' => 'Pan',
]);

// Getting the new autoincremented ID
$personId = $db->lastInsertId();

4) Selecting rows

//Selects all the rows from the table
$rows = $db->table("person")->select();

// Selects the rows where the column NAME is different from Peter, in descending order
$rows = $db->table("person")
    ->where("Name", "!=", "Peter")
    ->orderby("Name","desc")
    ->select();

5) Updating rows

// Delete row by ID
$isOk = $db->table("person")
    ->where("Id", "==", "1")
    ->update([
        'LastName' => 'Voldemort'
    ]);

5) Deleting rows

// Delete row by ID
$isOk = $db->table("person")
    ->where("Id", "==", "1")
    ->delete();

// Delete all rows in the table
$isOk = $db->table("person")->delete();

Helper Functions

1) Generating UUIDs

$uuid = Sinevia\SqlDB::uuid();

2) Generating HUIDs

HUIDs are human friendly unique identifiers, which are date based.

$uid20 = Sinevia\SqlDB::uid(); // 20 digits default
$uid32 = Sinevia\SqlDB::uid(32); // 32 digits

Related Projects

• Cache
• Monolog
• Tasks