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

2. 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

Data Type	MySQL Data Type	SQLite Data Type
STRING	VARCHAR (255)	TEXT
TEXT	LONG TEXT	TEXT
INTEGER	BIG INT	INTEGER
FLOAT	DOUBLE	REAL
BLOB	LONG BLOB	TEXT

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