SuperSQL is an easy-to-use powerful SQL query builder for SQLite and MySQL.

* PHP 5.6+

* Download the <em>ssql.php</em> file ([https://raw.githubusercontent.com/ratajs/SuperSQL/2.2/ssql.php](https://raw.githubusercontent.com/ratajs/SuperSQL/2.2/ssql.php))

Let’s have a table “<strong>users</strong>” with 5 columns: `uid`, `username`, `password`, `sign_up_time` and `nickname`

include "ssql.php";

//Connect with new Ssql($host[, $user[, $password[, $database]]])

$ssql = new Ssql("localhost", "root", "root", "db"); // MySQL

//Connect to SQLite with just the first parameter

$ssql = new Ssql("db.sqlite3");

//To execute raw SQL query use $ssql->q($q[, $a]), FETCH_ALL returns an array of rows, FETCH_OBJECT and FETCH_ARRAY return one row per call

$ssql->q("SELECT * FROM users")->fetch(SQ::FETCH_ALL);

//You can use wildcards for escaping

$ssql->q("SELECT * FROM users WHERE `username`=%0 OR `nickname`=%1", [$name, $nick])->fetch();

//You can set the object‐wide $debug property to print all queries before being executed

$ssql->debug = true;

//You can use queries as methods

$ssql->getUser = "SELECT * FROM users WHERE `username`=%0 OR `nickname`=%1";

$user = $ssql->getUser($name, $nick)->fetch();

//For simple requests use $ssql->read($table[, $cond][, $flags])

//Read function uses FETCH_SMART as default (FETCH_OBJECT for one row, FETCH_ALL for more), so you need to use the FETCH_ALL flag to return an array even when there is only one result

$users = $ssql->read("users", SQ::FETCH_ALL);

$user = $ssql->read("users", ['uid' => $id]);

//You can use the DEBUG flag to print this query before execution

$user = $ssql->read("users", ['uid' => $id], SQ::DEBUG);

//You can use more conditions

$user = $ssql->read("users", ['username' => $name, 'password' => $pass]);

//You can use the COND_OR flag for OR operator instead of AND

$user = $ssql->read("users", ['username' => $name, 'nickname' => $nick], SQ::FETCH_ALL | SQ::COND_OR)[0];

//You can use custom conditions

$users = $ssql->read("users", "`sign_up_time` > " . bcsub(time(), 3600));

//You can use more of them

$users = $ssql->read("users", ["`sign_up_time` > " . bcsub(time(), 3600), "`nickname` IS NOT NULL"], SQ::FETCH_ALL);

//For more complicated requests use $ssql->select($table[, $order[, $cols[, $limit[, $flags]]]]), you can use array keys for aliases

$users = $ssql->select("users", "sign_up_time", ['id' => "uid", 'name' => "username", "sign_up_time", "nickname"], NULL, SQ::ORDER_DESC)->fetch(SQ::FETCH_ALL);

//Or $ssql->selectWhere($table, $cond[, $order[, $cols[, $limit[, $flags]]]])

$user = $ssql->selectWhere("users", ['uid' => $id], "name", ['id' => "uid", 'name' => "username", "sign_up_time", 'nick' => "nickname"])->fetch();

//To quickly display tables use ->dump()

$ssql->select("users")->dump();

//Insert with $ssql->insert($table, $values[, $flags])

$ssql->insert("users", [$id, $name, $pass, time(), NULL]);

//You can use array keys as col names

$ssql->insert("users", ['username' => $name, 'password' => $pass, 'sign_up_time' => time()]);

//You can use INSERT_RETURN_ID flag for returning the first auto increment col value (depends on the database type)

$id = $ssql->insert("users", ['username' => $name, 'password' => $pass, 'sign_up_time' => time()], SQ::INSERT_RETURN_ID);

//Use $ssql->update($table, $cond, $values[, $flags]) to update rows

$ssql->update("users", ['id' => $id], ['nickname' => $nick]);

//You can delete rows with $ssql->delete($table, $cond[, $flags])

$ssql->delete("users", ['id' => $id]);

//You can use $ssql->put($table, $data[, $cond][, $flags]) for inserting, updating and deleting as well

//Insert

$ssql->put("users", [$id, $name, $pass, time(), NULL]);

$ssql->put("users", ['username' => $name, 'password' => $pass, 'sign_up_time' => time()], SQ::INSERT_RETURN_ID);

//Update

$ssql->put("users", ['nickname' => $nick], ['id' => $id]);

//Delete

$ssql->put("users", NULL, ['id' => $id]);

//Use $ssql->truncate($table[, $flags]) to delete all rows in a table

$ssql->truncate("users");

* <b>FETCH_OBJECT</b>

* <b>FETCH_ARRAY</b>

* <b>FETCH_ALL</b>

* <b>FETCH_SMART</b>

//To use the same database in more projects you can extend the Ssql class

class mySsql extends Ssql {

protected $host = "localhost";

protected $user = "root";

protected $password = "root";

protected $db = "db"; //Optional

};

//And then create an instance

$ssql = new mySsql();

//If $db is not set, you can set it afterwards

$ssql->changeDb("newDB");

//Join

//Use $ssql->selectJoin($table, $join, $on[, $order[, $cols[, $limit[, $flags]]]]) to execute a JOIN command

$result = $ssql->selectJoin("users", "messages", ['from_user' => 'uid'], "time", "*", 5, SQ::ORDER_DESC)->fetch(SQ::FETCH_ALL);

//Use JOIN_LEFT, JOIN_RIGHT and JOIN_FULL flags to other types of JOIN

//To combine JOIN and WHERE use $ssql->selectJoinWhere($table, $join, $on, $cond[, $order[, $cols[, $limit[, $flags]]]])

$result = $ssql->selectJoinWhere("users", "messages", ['from_user' => 'uid'], ['from_user' => $uid])->fetch(SQ::FETCH_ALL);

//Table creation and deletion

//For basic table creation use $ssql->createTable($table, $params[, $primary[, $flags]])

$ssql->createTable("myTable", [

'number' => [ // Column name

'type' => "int", // Column type

'NULL' => false // NULL

],

'text' => [

'type' => "varchar",

'length' => 64, // Max length

'NULL' => true

]

], "number");

//Use $ssql->deleteTable($table[, $flags]) to delete a table

$ssql->deleteTable("myTable");

//To see a list of all tables in your database use $ssql->tableList([$flags])

print_r($ssql->tableList());

//Advanced conditions

//You can use more advanced conditions with $ssql->cond(), this will select users that have the same username and nickname signed up in the last hour

$ssql->read("users", $ssql->cond()->eq("username", "nickname")->gte("sign_up_time", time() - 3600));

//Use arrays to differentiate string values from column names and to specify more alternatives, the COND_OR flag is also supported

//This will select users that have username either "ratajs" or "admin" or that have nickname "RatajS"

$ssql->read("users", $ssql->cond()->eq("username", ["ratajs", "admin"])->eq("nickname", ["RatajS"], SQ::COND_OR));

//->not() negates the condition, this will select users with usernames other than admin

$ssql->read("users", $ssql->cond()->eq("username", ["admin"])->not());

//You can also pass another condition to it, this will select users that don’t have any nickname with username other than "admin" or "root".

$ssql->read("users", $ssql->cond()->eq("nickname", [""])->not($ssql->cond()->eq("username", ["admin", "root"])));

//Supported conditions: ->eq(), ->lt(), ->gt(), ->lte(), ->gte(), ->like(), ->between(), ->begins(), ->ends(), ->contains() and ->in()

const FETCH_OBJECT = 512;

const FETCH_ARRAY = 1024;

const FETCH_ALL = 2048;

const FETCH_SMART = 4096;

const FETCH_HTML = 8192;

const CASE_INSENSITIVE = 16384;

const NO_ERROR = 32768;

const DEBUG = 65536;

if($flags & self::FETCH_OBJECT)

$flags-= self::FETCH_OBJECT;

if($flags & self::FETCH_ARRAY)

$flags-= self::FETCH_ARRAY;

if($flags & self::FETCH_ALL)

$flags-= self::FETCH_ALL;

if($flags & self::FETCH_SMART)

$flags-= self::FETCH_SMART;

if($flags & self::COND_AND)

$flags-= self::COND_AND;

if($flags & self::COND_OR)

$flags-= self::COND_OR;

if($flags & self::ORDER_ASC)

$flags-= self::ORDER_ASC;

if($flags & self::ORDER_DESC)

$flags-= self::ORDER_DESC;

if($flags & self::JOIN_INNER)

$flags-= self::JOIN_INNER;

if($flags & self::JOIN_LEFT)

$flags-= self::JOIN_LEFT;

if($flags & self::JOIN_RIGHT)

$flags-= self::JOIN_RIGHT;

if($flags & self::JOIN_FULL)

$flags-= self::JOIN_FULL;

$ssql->q("UPDATE `table3` SET `table3_num` = \"%1\" WHERE `table3_id` = \"%0\"", "3", "42");

$ssql->inc = "UPDATE `table3` SET `table3_num` = `table3_num` + 1 WHERE `table3_id` = %0";