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

Via composer command line

composer require sinevia/php-library-sqldb

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

sinevia / php-library-sqldb

Maintainers

Details