daily pastebin goal
56%
SHARE
TWEET

Guide to UserSpice DB.php

Faguss Feb 6th, 2018 (edited) 124 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Guide to UserSpice DB.php
  2.  
  3.  
  4.  
  5.     Sending Queries
  6.  
  7. Database class is instantiated to the $db variable.
  8. If you want to access it from your own function then call static method "getInstance".
  9.  
  10. function test() {
  11.     $db = DB::getInstance();
  12. }
  13.  
  14. To execute an SQL statement call query() method from the DB class instance.
  15.  
  16. $db->query("SELECT username FROM users");
  17.  
  18. Results are stored internally and you can access them by using the results() function.
  19.  
  20. foreach ($db->results() as $record)
  21.     echo $record->username . "<BR />";
  22.    
  23. For security reasons pass variables in a separate array instead of appending them to the query string.
  24. Question mark in the string is a placeholder for the value.
  25.  
  26. $name = "admin";
  27. $logins = 1;
  28. $db->query("SELECT email FROM users WHERE username = ? AND logins > ?", [$name, $logins]);
  29.  
  30. Return value is the DB object itself so that you can call another method on the result. For example:
  31.  
  32. $data = $db->query("SELECT username FROM users WHERE logins > 2")->result();
  33.  
  34.  
  35.  
  36.  
  37.  
  38.  
  39.  
  40.  
  41.  
  42.  
  43.     Query Results
  44.  
  45. 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:
  46.  
  47. results()
  48. Returns rows from the table as an array of objects. Optionally pass "true" to return associative arrays instead.
  49. echo $db->results()[0]->email;
  50. echo $db->results(true)[0]["email"];
  51.  
  52. first()
  53. Returns first row from the table as an object. Optionally pass "true" to return an associative array instead.
  54. echo $db->first()->email;
  55. echo $db->first(true)["email"];
  56.  
  57. count()
  58. Returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement.
  59. echo $db->count();
  60.  
  61. error()
  62. Returns "true" if the query failed.
  63. echo $db->error();
  64.  
  65. errorInfo()
  66. Returns an array with error codes and error message.
  67. print_r($db->errorInfo());
  68.  
  69. errorString()
  70. Returns error description.
  71. echo $db->errorString();
  72.  
  73. lastId()
  74. Returns ID number of the last inserted row.
  75. echo $db->lastId();
  76.  
  77. getQueryCount()
  78. Returns number indicating how many times query() function was called.
  79. echo $db->getQueryCount();
  80.  
  81.  
  82.  
  83.  
  84.  
  85.  
  86.  
  87.  
  88.  
  89.  
  90.     Quick Select
  91.  
  92. For making simple requests there are four shortcut functions:
  93.  
  94. findAll($table)
  95. Get all records from the table.
  96. SELECT * FROM table
  97. $db->findAll("users");
  98.  
  99. findById($id, $table)
  100. Get record with the specified id number.
  101. SELECT * FROM table WHERE id=
  102. $db->findById(1,"users");
  103.  
  104. get($table, $where)
  105. Get records matching given condition.
  106. SELECT * FROM table WHERE ...
  107. $db->get("users",["username","=","admin"]);
  108.  
  109. These methods return object in case of success and "false" in case of failure. You can check for error this way:
  110.  
  111. if ($db->get("users",["username","=","admin"]))
  112.     print_r($db->results()[0]->email);
  113. else
  114.     echo $db->errorString();
  115.    
  116. cell($tablecolumn, $id)
  117. Get a single value from one row. Pass an integer to select record with matching id number or an array for a custom condition.
  118. Returns requested value or "null" in case of failure.
  119. SELECT column FROM table WHERE ...
  120. $name = $db->cell("users.username",1);
  121. $name = $db->cell("users.username",["lname","=","Smith"]);
  122.  
  123.  
  124.  
  125.  
  126.  
  127.  
  128.  
  129.  
  130.  
  131.  
  132.  
  133.     Modifying Table
  134.  
  135. There are four methods for modifying tables:
  136.  
  137. insert($table, $fields=[], $update=false)
  138. Add new records to the table. Returns boolean indicating if the operation succeeded.
  139. $db->insert("permissions", ["name"=>"SuperAdmin"]);
  140.  
  141. Argument $fields is an associative array where keys match column names and values are the data that you want to add.
  142. Alternatively values can be arrays if you want to insert multiple rows.
  143.  
  144. $db->insert("pages", ["name"=>["index2.php","index3.php"], "private"=>[0,0]]);
  145.  
  146. If sub-arrays have different sizes then the function will add amount of records matching the size of the smallest array.
  147.  
  148. Pass "true" as the last argument to run the UPDATE operation if record with given ID already exists ($fields array must contain id number).
  149.  
  150.  
  151. update($table, $id, $fields)
  152. Modifies values in a record with matching id number. Returns boolean indicating if the operation succeeded.
  153. $db->update("users", 1, ["fname"=>"Adam"]);
  154.  
  155. Argument $fields is an associative array where keys match column names and values are the data that you want to replace with.
  156. Optionally argument $id can be an array with a custom condition so that you can modify multiple records.
  157.  
  158. $db->update("pages", ["name","LIKE","index%"], ["private"=>1]);
  159.  
  160.  
  161. deleteById($table, $id)
  162. Removes record that matches given id. Returns object or false.
  163. $db-delete("permissions", 3);
  164.  
  165.  
  166. delete($table, $where)
  167. Removes records matching given condition. Won't work if the $where array is empty. Returns object or false.
  168. $db->delete("permissions", ["name","=","SuperAdmin"]);
  169.  
  170.  
  171.  
  172.  
  173.  
  174.  
  175.  
  176.  
  177.  
  178.  
  179.     Condition Array
  180.  
  181. Methods get(), cell(), delete(), update() accept WHERE condition in the form of an array where:
  182. - first item is the column name
  183. - second item is the type of comparison
  184. - following items are values
  185.  
  186. Allowed operators:
  187.     =, <, >, <=, >=, <>, !=, LIKE, NOT LIKE, ALIKE, NOT ALIKE, REGEXP, NOT REGEXP
  188.     IS NULL, IS NOT NULL
  189.     BETWEEN, NOT BETWEEN
  190.     IN, NOT IN
  191.  
  192. Examples:
  193.     ["username","LIKE","a%"]
  194.     ["custom5", "IS NULL"]
  195.     ["logins","BETWEEN",10,30]
  196.     ["permissions","IN",[1,2]]
  197.  
  198. Subqueries are possible with the use of:
  199.     IN SELECT, NOT IN SELECT
  200.     ANY, ALL, SOME
  201.     EXISTS, NOT EXISTS
  202.  
  203. See https://pastebin.com/mWvUsNAh for examples.
  204.  
  205.  
  206. Alternatively the array can have associative keys. It will test for equality:
  207.     ["username" => "admin"]
  208.  
  209. Key name may end with "<" or ">" to test for greater or less:
  210.     ["logins >" => 2]
  211.     ["logins <" => 2]
  212.  
  213.  
  214. Conditions can be combined with the use of a boolean operator:
  215.     AND, OR, AND NOT, OR NOT
  216.  
  217. Write it at the beginning of the array (not between conditions!) and follow with conditions as sub-arrays:
  218.     ["and", ["logins",">","2"], ["permissions","=","1"]]
  219.  
  220. See https://pastebin.com/XmJR0Jk5 for examples.
RAW Paste Data
Top