Quick Start

Last updated: June 4th, 2018

Download

Installation

App Configuration

You can find the appconfig class file path: AppConfig/AppConfig.php and Configure your app.

PHP Code Example

<?php
class AppConfig
{
    //Database Configuration.
    const HOST = "localhost";
    const DBUSER = "root";
    const DBPASSWORD = "123456";
    const DATABASE = "test";
    const PORT = null;
    const SOCKET = null;
    const CHAR_SET = 'utf8';

    //Default File Upload Size
    const DEFAULT_UPLOAD_SIZE = 5; //Size 5 MB

    //Default Date Format.
    const DATETIME_FORMAT = 'Y-m-d H:i:s';

    //JWT Configuration.
    const JWT_SEQURITY_KEY = "YOUR_JWT_SEQURIT_KEY";
    const JWT_ENCRYPTION = 'HS512';
    const JWT_LEEWAY = 60;
}                                                        

                                            

Auto Class Mapper

  • If you are create a new class file for business or Controller then you need to map class file while run following script. .
  • Make sure you need to composer for run this script, if you don't have composer you need to install through this link: Download Composer
Run this batch file after adding new class for map:

mapclass.bat

Manual Class Mapper

If You don't want to use composer or auto map class, you can map new classes manually in two following mapper files:

path: vendor/composer/autoload_classmap.php


<?php

// autoload_classmap.php @generated by Composer

$vendorDir = dirname(dirname(__FILE__));
$baseDir = dirname($vendorDir);

return array(
    'AppConfig' => $baseDir . '/AppConfig/AppConfig.php',
    'BusinessBase' => $baseDir . '/Framework/BusinessBase.php',
    'Common' => $baseDir . '/Framework/Common.php',
    'ControllerBase' => $baseDir . '/Framework/ControllerBase.php',
    'Database' => $baseDir . '/Framework/Database/Database.php',
    'Filter' => $baseDir . '/Framework/Filters.php',
    'Firebase\\JWT\\BeforeValidException' => $baseDir . '/Framework/Libraries/JWT/BeforeValidException.php',
    'Firebase\\JWT\\ExpiredException' => $baseDir . '/Framework/Libraries/JWT/ExpiredException.php',
    'Firebase\\JWT\\JWT' => $baseDir . '/Framework/Libraries/JWT/JWT.php',
    'Firebase\\JWT\\SignatureInvalidException' => $baseDir . '/Framework/Libraries/JWT/SignatureInvalidException.php',
    'Identity' => $baseDir . '/Framework/Indentity.php',
    'LookupType' => $baseDir . '/AppCode/LookupType.php',
    'User' => $baseDir . '/Business/User.php',
    'WebSocketServer' => $baseDir . '/Framework/WebSocket/Websockets.php',
    'WebSocketUser' => $baseDir . '/Framework/WebSocket/WebSocketUser.php',
);

                                        

path: vendor/composer/autoload_static.php


<?php

// autoload_static.php @generated by Composer

namespace Composer\Autoload;

class ComposerStaticInitaae97774b5db8ac286e1301dede91e80
{
    public static $classMap = array (
        'AppConfig' => __DIR__ . '/../..' . '/AppConfig/AppConfig.php',
        'BusinessBase' => __DIR__ . '/../..' . '/Framework/BusinessBase.php',
        'Common' => __DIR__ . '/../..' . '/Framework/Common.php',
        'ControllerBase' => __DIR__ . '/../..' . '/Framework/ControllerBase.php',
        'Database' => __DIR__ . '/../..' . '/Framework/Database/Database.php',
        'Filter' => __DIR__ . '/../..' . '/Framework/Filters.php',
        'Firebase\\JWT\\BeforeValidException' => __DIR__ . '/../..' . '/Framework/Libraries/JWT/BeforeValidException.php',
        'Firebase\\JWT\\ExpiredException' => __DIR__ . '/../..' . '/Framework/Libraries/JWT/ExpiredException.php',
        'Firebase\\JWT\\JWT' => __DIR__ . '/../..' . '/Framework/Libraries/JWT/JWT.php',
        'Firebase\\JWT\\SignatureInvalidException' => __DIR__ . '/../..' . '/Framework/Libraries/JWT/SignatureInvalidException.php',
        'Identity' => __DIR__ . '/../..' . '/Framework/Indentity.php',
        'LookupType' => __DIR__ . '/../..' . '/AppCode/LookupType.php',
        'User' => __DIR__ . '/../..' . '/Business/User.php',
        'WebSocketServer' => __DIR__ . '/../..' . '/Framework/WebSocket/Websockets.php',
        'WebSocketUser' => __DIR__ . '/../..' . '/Framework/WebSocket/WebSocketUser.php',
    );

    public static function getInitializer(ClassLoader $loader)
    {
        return \Closure::bind(function () use ($loader) {
            $loader->classMap = ComposerStaticInitaae97774b5db8ac286e1301dede91e80::$classMap;

        }, null, ClassLoader::class);
    }
}     

                                        

Business Base class/model

Declaration

  • After create a table in database you need to create business base class with properties as same as name table columns which is associated with this class.
  • You can create BusinessBase class under directory path: /Business
Sample Business base class:

<?php

class User extends BusinessBase  //Make sure User class name should be same as Database table name.
{
    public function __construct()
    {
        parent::__construct();
    }

    public $Username;
    public $Password;
    public $Name;
    public $DOB;
    public $CreatedOn;
    public $CreatedBy;
    public $ModifiedOn;
    public $ModifiedBy;

}


                                        

Save

You can insert and update record by using BusinessBase class, here are difference in save/insert/update/delete based on Key Field (also known as Id Properties).

  • If you are set prperties for class apart from keyfield data simply insert in database.
  • If you are set key field as well with all properties then your record update based on mention key field.

Insert Example


<?php

$user = new User();
$user->Username = "78945";
$user->Password = "Devesh";
$user->Name = "Kumar";
$user->DOB = date('Y-m-d H:i:s');

$user->save(); //Create new record.


                                            

Update Example


<?php

$user = new User();
$user->Username = "78945";
$user->Password = "Devesh";
$user->Name = "Kumar";
$user->DOB = date('Y-m-d H:i:s');

$user->save(5); //Update record based on reord id 5


                                            

Load Example

  • You can load your businessBase class object with database specific record based on keyField

<?php

$user = new User();
$user->load(5); //Load databse record with businessBase class object based on reord id 5


                                                

Delete Example


<?php

$user = new User();
$user->delete(5); //Record delete based on reord id 5


                                                

Get List Example


<?php

$user = new User();

$records = $user->getList(); // Get all records of user table

$startIndex = 0; // Start index of the records.
$limit = 50; // Record limit per page.

$records = $user->getList($startIndex, $limit); // Get Records with pagination.

Common::serializeObject($records); //Json output


                                                

Controller Declaration

Declaration

  • Here we have three types of controller Authorized, Unauthorized and simple controllers.
  • All controller constructor supports two parameters.
    • BusinessBase class object which needs to perform crud operation with custom filters.
    • Support bool parameter (AUTHORIZED, UNAUTHORIZED) which is help to find out controller is Authorized or Unauthorized
  • Authorized controller need JWT token for execute and supports actions.
  • If you are not pass action in controller then controller execute function will call.
  • Unauthorized controller no need JWT token for execute and supports actions as well.
  • Simple controller support Authorized and Unauthorized but the controller don't have businessBase class for perform actions, you can write your custom logic under execute function.
  • You can create controllers class under directory path: /Controllers
Authorized Controller:

<?php

require_once '../AppLoad.php';

class UserController extends ControllerBase
{
    public function __construct()
    {
        parent::__construct(new User(), AUTHORIZED); //Authorized means it takes a valid token for execute.
    }

    public function execute()
    {
        echo "executed";
    }
}
Common::executeController("UserController");

                                    
Unauthorized Controller

<?php

require_once '../AppLoad.php';

class UserController extends ControllerBase
{
    public function __construct()
    {
        parent::__construct(new User(), UNAUTHORIZED); //Unauthorized means controller no need to take token for execute
    }

    public function execute()
    {
        echo "executed";
    }
}
Common::executeController("UserController");

                                        
Simple controller

<?php

require_once '../AppLoad.php';

class UserController extends ControllerBase
{
    public function __construct()
    {
        parent::__construct(null, AUTHORIZED/UNAUTHORIZED);
    }

    public function execute()
    {
        //Write a custom code as per your need.
    }
}
Common::executeController("UserController");

                                            

Get Params:

  • Get Params is an function which is works under controller class for parse the Params value, which is posted from client side
  • function supports three following parameters
    • Field name: String
    • Required/NotRequired: Bool (true/false)
    • Default value: If value are not posted from client side set a default value.

<?php

require_once '../AppLoad.php';

class ParamsExampleController extends ControllerBase
{
    public function __construct()
    {
        parent::__construct(Class Object, AUTHORIZED/UNAUTHORIZED);
    }

    public function execute()
    {
        $Firstname = $this->params("FirstName", true); //Required value.
        $Firstname = $this->params("FirstName", false); //Not Required.
        $Firstname = $this->params("FirstName", false, "John"); //Not required + Default value
    }
}
Common::executeController("ParamsExampleController");

Actions

  • There are four major actions which are supported by conterolles
    • Save
    • Load
    • Delete
    • List
Save from client side
Load from client side
Delete from client side
List from client side
List with paging from client side
List with filter from client side

Filters

You can pass filter in controller request from client side

  • Here is the example of filter json
  • [{ "fieldname": "Username", "type": "string", "value": "dkrock012", "condition": "eq" }]
Filter conditions:

EQUAL' = 'EQ'
NOT_EQUAL' = 'NEQ'
GREATER_THEN' = 'GT'
GREATER_THEN_OR_EQUAL' = 'GTE'
LESS_THEN' = 'LT'
LESS_THEN_OR_EQUAL' = 'LTE'
LIKE' = 'LIKE'
IN' = 'IN'
NOTIN' = 'NOTIN'

										  

Database

Insert Query

Simple example

<?php
$db = new Database();
$data = Array ("login" => "admin",
               "firstName" => "John",
               "lastName" => 'Doe'
);
$id = $db->insert ('users', $data);
if($id)
    echo 'user was created. Id=' . $id;

										
Insert with functions use

<?php

$data = Array (
'login' => 'admin',
'active' => true,
'firstName' => 'John',
'lastName' => 'Doe',
'password' => $db->func('SHA1(?)',Array ("secretpassword+salt")),
// password = SHA1('secretpassword+salt')
'createdAt' => $db->now(),
// createdAt = NOW()
'expires' => $db->now('+1Y')
// expires = NOW() + interval 1 year
// Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear
);

$id = $db->insert ('users', $data);
if ($id)
echo 'user was created. Id=' . $id;
else
echo 'insert failed: ' . $db->getLastError();

Insert with on duplicate key update

<?php
$data = Array ("login" => "admin",
               "firstName" => "John",
               "lastName" => 'Doe',
               "createdAt" => $db->now(),
               "updatedAt" => $db->now(),
);
$updateColumns = Array ("updatedAt");
$lastInsertId = "id";
$db->onDuplicate($updateColumns, $lastInsertId);
$id = $db->insert ('users', $data);

Insert multiple datasets at once

<?php
$data = Array(
    Array ("login" => "admin",
        "firstName" => "John",
        "lastName" => 'Doe'
    ),
    Array ("login" => "other",
        "firstName" => "Another",
        "lastName" => 'User',
        "password" => "very_cool_hash"
    )
);
$ids = $db->insertMulti('users', $data);
if(!$ids) {
    echo 'insert failed: ' . $db->getLastError();
} else {
    echo 'new users inserted with following id\'s: ' . implode(', ', $ids);
}

If all datasets only have the same keys, it can be simplified

<?php
$data = Array(
    Array ("admin", "John", "Doe"),
    Array ("other", "Another", "User")
);
$keys = Array("login", "firstName", "lastName");

$ids = $db->insertMulti('users', $data, $keys);
if(!$ids) {
    echo 'insert failed: ' . $db->getLastError();
} else {
    echo 'new users inserted with following id\'s: ' . implode(', ', $ids);
}

Update Query


<?php
$data = Array (
	'firstName' => 'Bobby',
	'lastName' => 'Tables',
	'editCount' => $db->inc(2),
	// editCount = editCount + 2;
	'active' => $db->not()
	// active = !active;
);
$db->where ('id', 1);
if ($db->update ('users', $data))
    echo $db->count . ' records were updated';
else
    echo 'update failed: ' . $db->getLastError();

update() also support limit parameter:

<?php
$db->update ('users', $data, 10);
// Gives: UPDATE users SET ... LIMIT 10

Select Query

After any select/get function calls amount or returned rows is stored in $count variable

<?php
$users = $db->get('users'); //contains an Array of all users 
$users = $db->get('users', 10); //contains an Array 10 users

or select with custom columns set. Functions also could be used

<?php
$cols = Array ("id", "name", "email");
$users = $db->get ("users", null, $cols);
if ($db->count > 0)
    foreach ($users as $user) { 
        print_r ($user);
    }

or select just one row

<?php
$db->where ("id", 1);
$user = $db->getOne ("users");
echo $user['id'];

$stats = $db->getOne ("users", "sum(id), count(*) as cnt");
echo "total ".$stats['cnt']. "users found";

or select one column value or function result

<?php
$count = $db->getValue ("users", "count(*)");
echo "{$count} users found";

select one column value or function result from multiple rows:

<?php
$logins = $db->getValue ("users", "login", null);
// select login from users
$logins = $db->getValue ("users", "login", 5);
// select login from users limit 5
foreach ($logins as $login)
    echo $login;

Pagination

  • Use paginate() instead of get() to fetch paginated result

<?php
$page = 1;
// set page limit to 2 results per page. 20 by default
$db->pageLimit = 2;
$products = $db->arraybuilder()->paginate("products", $page);
echo "showing $page out of " . $db->totalPages;

											

Delete Query


<?php
$db->where('id', 1);
if($db->delete('users')) echo 'successfully deleted';

Insert Data

  • You can also load .CSV or .XML data into a specific table. To insert .csv data, use the following syntax:

<?php
$path_to_file = "/home/john/file.csv";
$db->loadData("users", $path_to_file);

										
This will load a .csv file called file.csv in the folder /home/john/ (john's home directory.) You can also attach an optional array of options. Valid options are:

<?php
Array(
	"fieldChar" => ';', 	// Char which separates the data
	"lineChar" => '\r\n', 	// Char which separates the lines
	"linesToIgnore" => 1	// Amount of lines to ignore at the beginning of the import
);

											
Attach them using

<?php
$options = Array("fieldChar" => ';', "lineChar" => '\r\n', "linesToIgnore" => 1);
$db->loadData("users", "/home/john/file.csv", $options);
// LOAD DATA ...

										
You can specify to use LOCAL DATA instead of DATA:

<?php
$options = Array("fieldChar" => ';', "lineChar" => '\r\n', "linesToIgnore" => 1, "loadDataLocal" => true);
$db->loadData("users", "/home/john/file.csv", $options);
// LOAD DATA LOCAL ...

										

Insert XML

  • To load XML data into a table, you can use the method loadXML. The syntax is smillar to the loadData syntax.

<?php
$path_to_file = "/home/john/file.xml";
$db->loadXML("users", $path_to_file);

										
You can also add optional parameters. Valid parameters

<?php
Array(
	"linesToIgnore" => 0,		// Amount of lines / rows to ignore at the beginning of the import
	"rowTag"	=> ""	// The tag which marks the beginning of an entry
)

											
Usage:

<?php
$options = Array("linesToIgnore" => 0, "rowTag"	=> ""):
$path_to_file = "/home/john/file.xml";
$db->loadXML("users", $path_to_file, $options);

												

Running raw SQL queries


<?php
$users = $db->rawQuery('SELECT * from users where id >= ?', Array (10));
foreach ($users as $user) {
    print_r ($user);
}

										
To avoid long if checks there are couple helper functions to work with raw query select results: Get 1 row of results:

<?php
$user = $db->rawQueryOne ('select * from users where id=?', Array(10));
echo $user['login'];
// Object return type
$user = $db->ObjectBuilder()->rawQueryOne ('select * from users where id=?', Array(10));
echo $user->login;

										
Get 1 column value as a string:

<?php
$password = $db->rawQueryValue ('select password from users where id=? limit 1', Array(10));
echo "Password is {$password}";
NOTE: for a rawQueryValue() to return string instead of an array 'limit 1' should be added to the end of the query.

										
Get 1 column value from multiple rows:

<?php
$logins = $db->rawQueryValue ('select login from users limit 10');
foreach ($logins as $login)
    echo $login;

										
More advanced examples

<?php
$params = Array(1, 'admin');
$users = $db->rawQuery("SELECT id, firstName, lastName FROM users WHERE id = ? AND login = ?", $params);
print_r($users); // contains Array of returned rows

// will handle any SQL query
$params = Array(10, 1, 10, 11, 2, 10);
$q = "(
    SELECT a FROM t1
        WHERE a = ? AND B = ?
        ORDER BY a LIMIT ?
) UNION (
    SELECT a FROM t2 
        WHERE a = ? AND B = ?
        ORDER BY a LIMIT ?
)";
$resutls = $db->rawQuery ($q, $params);
print_r ($results); // contains Array of returned rows

										

Query Keywords

  • To add LOW PRIORITY | DELAYED | HIGH PRIORITY | IGNORE and the rest of the mysql keywords to INSERT (), REPLACE (), GET (), UPDATE (), DELETE() method or FOR UPDATE | LOCK IN SHARE MODE into SELECT ():

<?php
$db->setQueryOption ('LOW_PRIORITY')->insert ($table, $param);
// GIVES: INSERT LOW_PRIORITY INTO table ...



<?php
$db->setQueryOption ('FOR UPDATE')->get ('users');
// GIVES: SELECT * FROM USERS FOR UPDATE;

										
Also you can use an array of keywords:

<?php
$db->setQueryOption (Array('LOW_PRIORITY', 'IGNORE'))->insert ($table,$param);
// GIVES: INSERT LOW_PRIORITY IGNORE INTO table ...

										
Same way keywords could be used in SELECT queries as well:

<?php
$db->setQueryOption ('SQL_NO_CACHE');
$db->get("users");
// GIVES: SELECT SQL_NO_CACHE * FROM USERS;

										
Optionally you can use method chaining to call where multiple times without referencing your object over and over:

<?php
->where('id', 1)
->where('login', 'admin')
->get('users');

										

Where / Having Methods

  • where(), orWhere(), having() and orHaving() methods allows you to specify where and having conditions of the query. All conditions supported by where() are supported by having() as well.
  • WARNING: In order to use column to column comparisons only raw where conditions should be used as column name or functions cant be passed as a bind variable.
Regular == operator with variables:

<?php
$db->where ('id', 1);
$db->where ('login', 'admin');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE id=1 AND login='admin';


$db->where ('id', 1);
$db->having ('login', 'admin');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE id=1 HAVING login='admin';

Regular == operator with column to column comparison:

<?php
// WRONG
$db->where ('lastLogin', 'createdAt');
// CORRECT
$db->where ('lastLogin = createdAt');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE lastLogin = createdAt;


$db->where ('id', 50, ">=");
// or $db->where ('id', Array ('>=' => 50));
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE id >= 50;

												
BETWEEN / NOT BETWEEN:

<?php
$db->where('id', Array (4, 20), 'BETWEEN');
// or $db->where ('id', Array ('BETWEEN' => Array(4, 20)));

$results = $db->get('users');
// Gives: SELECT * FROM users WHERE id BETWEEN 4 AND 20

												
IN / NOT IN:

<?php
$db->where('id', Array(1, 5, 27, -1, 'd'), 'IN');
// or $db->where('id', Array( 'IN' => Array(1, 5, 27, -1, 'd') ) );

$results = $db->get('users');
// Gives: SELECT * FROM users WHERE id IN (1, 5, 27, -1, 'd');

												
OR CASE:

<?php
$db->where ('firstName', 'John');
$db->orWhere ('firstName', 'Peter');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE firstName='John' OR firstName='peter'

												
NULL comparison:

<?php
$db->where ("lastName", NULL, 'IS NOT');
$results = $db->get("users");
// Gives: SELECT * FROM users where lastName IS NOT NULL

												
LIKE comparison:

<?php
$db->where ("fullName", 'John%', 'like');
$results = $db->get("users");
// Gives: SELECT * FROM users where fullName like 'John%'

													
Also you can use raw where conditions:

<?php
$db->where ("id != companyId");
$db->where ("DATE(createdAt) = DATE(lastLogin)");
$results = $db->get("users");

													
Or raw condition with variables:

<?php
$db->where ("(id = ? or id = ?)", Array(6,2));
$db->where ("login","mike")
$res = $db->get ("users");
// Gives: SELECT * FROM users WHERE (id = 6 or id = 2) and login='mike';

													
Find the total number of rows matched. Simple pagination example:

<?php
$offset = 10;
$count = 15;
$users = $db->withTotalCount()->get('users', Array ($offset, $count));
echo "Showing {$count} from {$db->totalCount}";

													

Ordering method


<?php
$db->orderBy("id","asc");
$db->orderBy("login","Desc");
$db->orderBy("RAND ()");
$results = $db->get('users');
// Gives: SELECT * FROM users ORDER BY id ASC,login DESC, RAND ();

												
Order by values example:

<?php
$db->orderBy('userGroup', 'ASC', array('superuser', 'admin', 'users'));
$db->get('users');
// Gives: SELECT * FROM users ORDER BY FIELD (userGroup, 'superuser', 'admin', 'users') ASC;

												
If you are using setPrefix () functionality and need to use table names in orderBy() method make sure that table names are escaped with ``

<?php
$db->setPrefix ("t_");
$db->orderBy ("users.id","asc");
$results = $db->get ('users');
// WRONG: That will give: SELECT * FROM t_users ORDER BY users.id ASC;

$db->setPrefix ("t_");
$db->orderBy ("`users`.id", "asc");
$results = $db->get ('users');
// CORRECT: That will give: SELECT * FROM t_users ORDER BY t_users.id ASC;

												

Grouping method


<?php
$db->groupBy ("name");
$results = $db->get ('users');
// Gives: SELECT * FROM users GROUP BY name;

											  

Properties sharing

  • It is also possible to copy properties
Query:

<?php
$db->where ("agentId", 10);
$db->where ("active", true);

$customers = $db->copy ();
$res = $customers->get ("customers", Array (10, 10));
// SELECT * FROM customers where agentId = 10 and active = 1 limit 10, 10

$cnt = $db->getValue ("customers", "count(id)");
echo "total records found: " . $cnt;
// SELECT count(id) FROM users where agentId = 10 and active = 1

												  

JOIN method


<?php
$db->join("users u", "p.tenantID=u.tenantID", "LEFT");
$db->where("u.id", 6);
$products = $db->get ("products p", null, "u.name, p.productName");
print_r ($products);

											  
Add AND condition to join statement

<?php
$db->join("users u", "p.tenantID=u.tenantID", "LEFT");
$db->joinWhere("users u", "u.tenantID", 5);
$products = $db->get ("products p", null, "u.name, p.productName");
print_r ($products);
// Gives: SELECT  u.login, p.productName FROM products p LEFT JOIN users u ON (p.tenantID=u.tenantID AND u.tenantID = 5)

											  
Add OR condition to join statement

<?php
$db->join("users u", "p.tenantID=u.tenantID", "LEFT");
$db->joinOrWhere("users u", "u.tenantID", 5);
$products = $db->get ("products p", null, "u.name, p.productName");
print_r ($products);
// Gives: SELECT  u.login, p.productName FROM products p LEFT JOIN users u ON (p.tenantID=u.tenantID OR u.tenantID = 5)

											  

Subqueries

  • Subquery init.
Subquery init without an alias to use in inserts/updates/where Eg. (select * from users)

<?php
$sq = $db->subQuery();
$sq->get ("users");

											
A subquery with an alias specified to use in JOINs . Eg. (select * from users) sq

<?php
$sq = $db->subQuery("sq");
$sq->get ("users");

											
Subquery in selects:

<?php
$ids = $db->subQuery ();
$ids->where ("qty", 2, ">");
$ids->get ("products", null, "userId");

$db->where ("id", $ids, 'in');
$res = $db->get ("users");
// Gives SELECT * FROM users WHERE id IN (SELECT userId FROM products WHERE qty > 2)

											
Subquery in inserts:

<?php
$userIdQ = $db->subQuery ();
$userIdQ->where ("id", 6);
$userIdQ->getOne ("users", "name"),

$data = Array (
    "productName" => "test product",
    "userId" => $userIdQ,
    "lastUpdated" => $db->now()
);
$id = $db->insert ("products", $data);
// Gives INSERT INTO PRODUCTS (productName, userId, lastUpdated) values ("test product", (SELECT name FROM users WHERE id = 6), NOW());

											
Subquery in joins:

<?php
$usersQ = $db->subQuery ("u");
$usersQ->where ("active", 1);
$usersQ->get ("users");

$db->join($usersQ, "p.userId=u.id", "LEFT");
$products = $db->get ("products p", null, "u.login, p.productName");
print_r ($products);
// SELECT u.login, p.productName FROM products p LEFT JOIN (SELECT * FROM t_users WHERE active = 1) u on p.userId=u.id;

											

EXISTS / NOT EXISTS condition


<?php
$sub = $db->subQuery();
    $sub->where("company", 'testCompany');
    $sub->get ("users", null, 'userId');
$db->where (null, $sub, 'exists');
$products = $db->get ("products");
// Gives SELECT * FROM products WHERE EXISTS (select userId from users where company='testCompany')

												

Helper methods

Disconnect from the database:

<?php
$db->disconnect();

										
Reconnect in case mysql connection died:

<?php
if (!$db->ping())
    $db->connect()

										
Get last executed SQL query: Please note that function returns SQL query only for debugging purposes as its execution most likely will fail due missing quotes around char variables.

<?php
$db->get('users');
echo "Last executed query was ". $db->getLastQuery();

										
Check if table exists:

<?php
if ($db->tableExists ('users'))
echo "hooray";

										
mysqli_real_escape_string() wrapper:

<?php
$escaped = $db->escape ("' and 1=1");

										

Declaration

  • A convenient function that returns TRUE if exists at least an element that satisfy the where condition specified calling the "where" method before this one.

<?php
$db->where("user", $user);
$db->where("password", md5($password));
if($db->has("users")) {
    return "You are logged";
} else {
    return "Wrong user/password";
}

										

Transaction helpers

  • Please keep in mind that transactions are working on innoDB tables. Rollback transaction if insert fails:

<?php
$db->startTransaction();
...
if (!$db->insert ('myTable', $insertData)) {
    //Error while saving, cancel new record
    $db->rollback();
} else {
    //OK
    $db->commit();
}

												  

Error helpers

  • After you executed a query you have options to check if there was an error. You can get the MySQL error string or the error code for the last executed query.

<?php
$db->where('login', 'admin')->update('users', ['firstName' => 'Jack']);

if ($db->getLastErrno() === 0)
    echo 'Update succesfull';
else
    echo 'Update failed. Error: '. $db->getLastError();

											  

Table Locking

  • To lock tables, you can use the lock method together with setLockMethod. The following example will lock the table users for write access.
Query:

<?php
$db->setLockMethod("WRITE")->lock("users");

												
Calling another ->lock() will remove the first lock. You can also use

<?php
$db->unlock();

												
to unlock the previous locked tables. To lock multiple tables, you can use an array. Example:

<?php
$db->setLockMethod("READ")->lock(array("users", "log"));

												
  • This will lock the tables users and log for READ access only. Make sure you use *unlock() afterwards or your tables will remain locked!

JWT Token

  • JWT Token use for controller Authorization.
Generate Token:

<?php
$tokenValue = Identity::generateToken($row["UserId"], 60 * 24); //24 is an hours

											  
Example with login

<?php

require_once '../AppLoad.php';

class LoginController extends ControllerBase
{
    public function __construct()
    {
        parent::__construct(null, AUTHORIZATION_DISABLED);
    }

    public function execute()
    {
        try {
            $username = $this->params("Username", true);
            $password = $this->params("Password", true);
            $password = Common::passwordHash($password);
            $tokenValue = null;
            $db = new Database();
            $db->where("Username", $username);
            $db->where("Password", $password);
            $row = $db->getOne('User');

            if ($row) {
                $tokenValue = Identity::generateToken($row["UserId"], 60 * 24);
                Common::serializeObject(array(SUCCESS => true, TOKEN => $tokenValue, USER => array($row)));
            } else {
                Common::serializeObject(array(SUCCESS => false, ERROR => INVALID_USERNAME_PASSWORD));
            }
        } catch (Exception $ex) {
            Common::serializeObject($ex->getMessage());
        }
    }
}
Common::executeController("LoginController");

											  

WebSocket

WebSocket Declaration

Example:

<?php

class echoServer extends WebSocketServer {
  //protected $maxBufferSize = 1048576; //1MB... overkill for an echo server, but potentially plausible for other applications.
  
  protected function process ($user, $message) {
    $this->send($user,$message);
  }
  
  protected function connected ($user) {
    // Do nothing: This is just an echo server, there's no need to track the user.
    // However, if we did care about the users, we would probably have a cookie to
    // parse at this step, would be looking them up in permanent storage, etc.
  }
  
  protected function closed ($user) {
    // Do nothing: This is where cleanup would go, in case the user had any sort of
    // open files or other objects associated with them.  This runs after the socket 
    // has been closed, so there is no need to clean up the socket itself here.
  }
}
$echo = new echoServer("0.0.0.0","9000");
try {
  $echo->run();
}
catch (Exception $e) {
  $echo->stdout($e->getMessage());
}