Using the Database

Special thanks to Faguss for contributing to the documentation!

Using One or More Databases

The database class is instantiated to the $db variable by default on UserSpice pages, but if you want to call it yourself inside of a function function or other page that does not have it by default then call static method "getInstance".
function test() {
$db = DB::getInstance();
}


If you would like to work with multiple databases other than the default one you setup during install, you have 4 ways to do so. Some of these methods were added in UserSpice 5.1.5, so if you are using an earlier version, you may need to grab the DB class from that version.

To use a second database on the same server with the same credentials, call

$db2 = DB::getDB(['otherDBname']); or $db2 = DB::getDB('otherDBname');

To pass a full set of credentials inline, call

$db2 = DB::getDB(["host","dbname","username","password"]);

To store additional sets of credentials to the init.php file, add additional arrays (each with a unique name) to the config array
$GLOBALS['config'] = array(
'mysql' => array(
'host' => '127.0.0.1',
'username' => 'root',
'password' => '',
'db' => 'myFirstDB',
),
'mysql2' => array(
'host' => 'localhost',
'username' => 'root',
'password' => '',
'db' => 'mySecondDB',
),

You can call that db using the name of the array in your init.php file
$db2 = DB::getDB(['mysql2','init']);

Note about using multiple databases: Be sure to finish your query (i.e. call results() or first()) on one database before moving on to another database to prevent unexpected results.

Versions 5.2.7 and later: You can also specify a character set in your database configuration array with  'charset' => 'utf8mb4'

Sending Queries

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 . "
";

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")->results();


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{3bc1fe685386cc4c3ab89a3f76566d8931e181ad17f08aed9ad73b30bf28114d}"], ["private"=>1]);


deleteById($table, $id)
Removes record that matches given id. Returns object or false.
$db->deleteById("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{3bc1fe685386cc4c3ab89a3f76566d8931e181ad17f08aed9ad73b30bf28114d}"]
["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.