Guide to UserSpice DB.php

Guide to UserSpice DB.php



	Sending Queries

Database class is instantiated to the $db variable.
If you want to access it from your own function then call static method "getInstance".

function test() {
	$db = DB::getInstance();
}

To execute an SQL statement call query() method from the DB class instance.

$db->query("SELECT username FROM users");

Results are stored internally and you can access them by using the results() function.

foreach ($db->results() as $record)
	echo $record->username . "<BR />";

For security reasons pass variables in a separate array instead of appending them to the query string.
Question mark in the string is a placeholder for the value.

$name = "admin";
$logins = 1;
$db->query("SELECT email FROM users WHERE username = ? AND logins > ?", [$name, $logins]);

Return value is the DB object itself so that you can call another method on the result. For example:

$data = $db->query("SELECT username FROM users WHERE logins > 2")->result();










	Query Results

Query results are stored internally and reset every time a new query is made. You can access this information with the use of following methods:

results()
Returns rows from the table as an array of objects. Optionally pass "true" to return associative arrays instead.
echo $db->results()[0]->email;
echo $db->results(true)[0]["email"];

first()
Returns first row from the table as an object. Optionally pass "true" to return an associative array instead.
echo $db->first()->email;
echo $db->first(true)["email"];

count()
Returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement.
echo $db->count();

error()
Returns "true" if the query failed.
echo $db->error();

errorInfo()
Returns an array with error codes and error message.
print_r($db->errorInfo());

errorString()
Returns error description.
echo $db->errorString();

lastId()
Returns ID number of the last inserted row.
echo $db->lastId();

getQueryCount()
Returns number indicating how many times query() function was called.
echo $db->getQueryCount();










	Quick Select

For making simple requests there are four shortcut functions:

findAll($table)
Get all records from the table.
SELECT * FROM table
$db->findAll("users");

findById($id, $table)
Get record with the specified id number.
SELECT * FROM table WHERE id=
$db->findById(1,"users");

get($table, $where)
Get records matching given condition.
SELECT * FROM table WHERE ...
$db->get("users",["username","=","admin"]);

These methods return object in case of success and "false" in case of failure. You can check for error this way:

if ($db->get("users",["username","=","admin"]))
	print_r($db->results()[0]->email);
else
	echo $db->errorString();

cell($tablecolumn, $id)
Get a single value from one row. Pass an integer to select record with matching id number or an array for a custom condition.
Returns requested value or "null" in case of failure.
SELECT column FROM table WHERE ...
$name = $db->cell("users.username",1);
$name = $db->cell("users.username",["lname","=","Smith"]);











	Modifying Table

There are four methods for modifying tables:

insert($table, $fields=[], $update=false)
Add new records to the table. Returns boolean indicating if the operation succeeded.
$db->insert("permissions", ["name"=>"SuperAdmin"]);

Argument $fields is an associative array where keys match column names and values are the data that you want to add.
Alternatively values can be arrays if you want to insert multiple rows.

$db->insert("pages", ["name"=>["index2.php","index3.php"], "private"=>[0,0]]);

If sub-arrays have different sizes then the function will add amount of records matching the size of the smallest array.

Pass "true" as the last argument to run the UPDATE operation if record with given ID already exists ($fields array must contain id number).


update($table, $id, $fields)
Modifies values in a record with matching id number. Returns boolean indicating if the operation succeeded.
$db->update("users", 1, ["fname"=>"Adam"]);

Argument $fields is an associative array where keys match column names and values are the data that you want to replace with.
Optionally argument $id can be an array with a custom condition so that you can modify multiple records.

$db->update("pages", ["name","LIKE","index%"], ["private"=>1]);


deleteById($table, $id)
Removes record that matches given id. Returns object or false.
$db-delete("permissions", 3);


delete($table, $where)
Removes records matching given condition. Won't work if the $where array is empty. Returns object or false.
$db->delete("permissions", ["name","=","SuperAdmin"]);










	Condition Array

Methods get(), cell(), delete(), update() accept WHERE condition in the form of an array where:
- first item is the column name
- second item is the type of comparison
- following items are values

Allowed operators:
	=, <, >, <=, >=, <>, !=, LIKE, NOT LIKE, ALIKE, NOT ALIKE, REGEXP, NOT REGEXP
	IS NULL, IS NOT NULL
	BETWEEN, NOT BETWEEN
	IN, NOT IN

Examples:
	["username","LIKE","a%"]
	["custom5", "IS NULL"]
	["logins","BETWEEN",10,30]
	["permissions","IN",[1,2]]

Subqueries are possible with the use of:
	IN SELECT, NOT IN SELECT
	ANY, ALL, SOME
	EXISTS, NOT EXISTS

See https://pastebin.com/mWvUsNAh for examples.


Alternatively the array can have associative keys. It will test for equality:
	["username" => "admin"]

Key name may end with "<" or ">" to test for greater or less:
	["logins >" => 2]
	["logins <" => 2]


Conditions can be combined with the use of a boolean operator:
	AND, OR, AND NOT, OR NOT

Write it at the beginning of the array (not between conditions!) and follow with conditions as sub-arrays:
	["and", ["logins",">","2"], ["permissions","=","1"]]

See https://pastebin.com/XmJR0Jk5 for examples.