API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Sep 3rd, 2018
290
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.75 KB | None | 0 0
raw download clone embed print report
  1. <?php
  2. /*
  3. UserSpice 4
  4. An Open Source PHP User Management System
  5. by the UserSpice Team at http://UserSpice.com
  6.  
  7. This program is free software: you can redistribute it and/or modify
  8. it under the terms of the GNU General Public License as published by
  9. the Free Software Foundation, either version 3 of the License, or
  10. (at your option) any later version.
  11.  
  12. This program is distributed in the hope that it will be useful,
  13. but WITHOUT ANY WARRANTY; without even the implied warranty of
  14. MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  15. GNU General Public License for more details.
  16.  
  17. You should have received a copy of the GNU General Public License
  18. along with this program. If not, see <http://www.gnu.org/licenses/>.
  19. */
  20. class DB {
  21. private static $_instance = null;
  22. private $_pdo, $_query, $_error = false, $_errorInfo, $_results=[], $_resultsArray=[], $_count = 0, $_lastId, $_queryCount=0;
  23.  
  24. private function __construct($config = []){
  25. if (!$opts = Config::get('mysql/options'))
  26. $opts = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode = ''");
  27. try{
  28. if($config == []){
  29. $this->_pdo = new PDO('mysql:host=' .
  30. Config::get('mysql/host') .';dbname='.
  31. Config::get('mysql/db') . ';charset=utf8',
  32. Config::get('mysql/username'),
  33. Config::get('mysql/password'),
  34. $opts);
  35. }elseif(in_array('ns',$config)){
  36. //if you want to specify a completely new DB with new host, un, pw put ns second in the array
  37. //ie $db2 = getDB(['myotherdb','ns']) for new server
  38. $this->_pdo = new PDO('mysql:host=' .
  39. Config::get($config[0].'/host') .';dbname='.
  40. Config::get($config[0].'/db') . ';charset=utf8',
  41. Config::get($config[0].'/username'),
  42. Config::get($config[0].'/password'),
  43. $opts);
  44. }else{
  45. //if your alt db is on the same server as your primary db, you can specify
  46. //ie $db3 = getDB(['myotherdb']) for another db on the same server with the same credentials.
  47. $this->_pdo = new PDO('mysql:host=' .
  48. Config::get('mysql/host') .';dbname='.
  49. ($config[0]) . ';charset=utf8',
  50. Config::get('mysql/username'),
  51. Config::get('mysql/password'),
  52. $opts);
  53. }
  54. } catch(PDOException $e){
  55. die($e->getMessage());
  56. }
  57. }
  58.  
  59. public static function getInstance(){
  60. if (!isset(self::$_instance)) {
  61. self::$_instance = new DB();
  62. }
  63. return self::$_instance;
  64. }
  65.  
  66. public static function getDB($config){
  67. self::$_instance = new DB($config);
  68. return self::$_instance;
  69. }
  70.  
  71. public function query($sql, $params = array()){
  72. #echo "DEBUG: query(sql=$sql, params=".print_r($params,true).")<br />\n";
  73. $this->_queryCount++;
  74. $this->_error = false;
  75. $this->_errorInfo = array(0, null, null); $this->_resultsArray=[]; $this->_count=0; $this->_lastId=0;
  76. if ($this->_query = $this->_pdo->prepare($sql)) {
  77. $x = 1;
  78. if (count($params)) {
  79. foreach ($params as $param) {
  80. $this->_query->bindValue($x, $param);
  81. $x++;
  82. }
  83. }
  84.  
  85. if ($this->_query->execute()) {
  86. if ($this->_query->columnCount() > 0) {
  87. $this->_results = $this->_query->fetchALL(PDO::FETCH_OBJ);
  88. $this->_resultsArray = json_decode(json_encode($this->_results),true);
  89. }
  90. $this->_count = $this->_query->rowCount();
  91. $this->_lastId = $this->_pdo->lastInsertId();
  92. } else{
  93. $this->_error = true;
  94. $this->_errorInfo = $this->_query->errorInfo();
  95. }
  96. }
  97. return $this;
  98. }
  99.  
  100. public function findAll($table){
  101. return $this->action('SELECT *',$table);
  102. }
  103.  
  104. public function findById($id,$table){
  105. return $this->action('SELECT *',$table,array('id','=',$id));
  106. }
  107.  
  108. public function action($action, $table, $where = array()){
  109. $sql = "{$action} FROM {$table}";
  110. $values = array();
  111. $is_ok = true;
  112.  
  113. if ($where_text = $this->_calcWhere($where, $values, "and", $is_ok))
  114. $sql .= " WHERE $where_text";
  115.  
  116. if ($is_ok)
  117. if (!$this->query($sql, $values)->error())
  118. return $this;
  119.  
  120. return false;
  121. }
  122.  
  123. private function _calcWhere($w, &$vals, $comboparg='and', &$is_ok=NULL) {
  124. #echo "DEBUG: Entering _calcwhere(w=".print_r($w,true).",...)<br />\n";
  125. if (is_array($w)) {
  126. #echo "DEBUG: is_array - check<br />\n";
  127. $comb_ops = ['and', 'or', 'and not', 'or not'];
  128. $valid_ops = ['=', '<', '>', '<=', '>=', '<>', '!=', 'LIKE', 'NOT LIKE', 'ALIKE', 'NOT ALIKE', 'REGEXP', 'NOT REGEXP'];
  129. $two_args = ['IS NULL', 'IS NOT NULL'];
  130. $four_args = ['BETWEEN', 'NOT BETWEEN'];
  131. $arr_arg = ['IN', 'NOT IN'];
  132. $nested_arg = ['ANY', 'ALL', 'SOME'];
  133. $nested = ['EXISTS', 'NOT EXISTS'];
  134. $nestedIN = ['IN SELECT', 'NOT IN SELECT'];
  135. $wcount = count($w);
  136.  
  137. if ($wcount == 0)
  138. return "";
  139.  
  140. # believe it or not, this appears to be the fastest way to check
  141. # sequential vs associative. Particularly with our expected short
  142. # arrays it shouldn't impact memory usage
  143. # https://gist.github.com/Thinkscape/1965669
  144. if (array_values($w) === $w) { // sequential array
  145. #echo "DEBUG: Sequential array - check!<br />\n";
  146. if (in_array(strtolower($w[0]), $comb_ops)) {
  147. #echo "DEBUG: w=".print_r($w,true)."<br />\n";
  148. $sql = '';
  149. $combop = '';
  150. for ($i = 1; $i < $wcount; $i++) {
  151. $sql .= ' '. $combop . ' ' . $this->_calcWhere($w[$i], $vals, "and", $is_ok);
  152. $combop = $w[0];
  153. }
  154. return '('.$sql.')';
  155.  
  156. } elseif ($wcount==3 && in_array($w[1],$valid_ops)) {
  157. #echo "DEBUG: normal condition w=".print_r($w,true)."<br />\n";
  158. $vals[] = $w[2];
  159. return "{$w[0]} {$w[1]} ?";
  160.  
  161. } elseif ($wcount==2 && in_array($w[1],$two_args)) {
  162. return "{$w[0]} {$w[1]}";
  163.  
  164. } elseif ($wcount==4 && in_array($w[1],$four_args)) {
  165. $vals[] = $w[2];
  166. $vals[] = $w[3];
  167. return "{$w[0]} {$w[1]} ? AND ?";
  168.  
  169. } elseif ($wcount==3 && in_array($w[1],$arr_arg) && is_array($w[2])) {
  170. $vals = array_merge($vals,$w[2]);
  171. return "{$w[0]} {$w[1]} (" . substr( str_repeat(",?",count($w[2])), 1) . ")";
  172.  
  173. } elseif (($wcount==5 || $wcount==6 && is_array($w[5])) && in_array($w[1],$valid_ops) && in_array($w[2],$nested_arg)) {
  174. return "{$w[0]} {$w[1]} {$w[2]}" . $this->get_subquery_sql($w[4],$w[3],$w[5],$vals,$is_ok);
  175.  
  176. } elseif (($wcount==3 || $wcount==4 && is_array($w[3])) && in_array($w[0],$nested)) {
  177. return $w[0] . $this->get_subquery_sql($w[2],$w[1],$w[3],$vals,$is_ok);
  178.  
  179. } elseif (($wcount==4 || $wcount==5 && is_array($w[4])) && in_array($w[1],$nestedIN)) {
  180. return "{$w[0]} " . substr($w[1],0,-7) . $this->get_subquery_sql($w[3],$w[2],$w[4],$vals,$is_ok);
  181.  
  182. } else {
  183. echo "ERROR: w=".print_r($w,true)."<br />\n";
  184. $is_ok = false;
  185. }
  186. } else { // associative array ['field' => 'value']
  187. #echo "DEBUG: Associative<br />\n";
  188. $sql = '';
  189. $combop = '';
  190. foreach ($w as $k=>$v) {
  191. if (in_array(strtolower($k), $comb_ops)) {
  192. #echo "DEBUG: A<br />\n";
  193. #echo "A: k=$k, v=".print_r($v,true)."<br />\n";
  194. $sql .= $combop . ' (' . $this->_calcWhere($v, $vals, $k, $is_ok) . ') ';
  195. $combop = $comboparg;
  196. } else {
  197. #echo "DEBUG: B<br />\n";
  198. #echo "B: k=$k, v=".print_r($v,true)."<br />\n";
  199. $vals[] = $v;
  200. if (in_array(substr($k,-1,1), array('=', '<', '>'))) // 'field !='=>'value'
  201. $sql .= $combop . ' ' . $k . ' ? ';
  202. else // 'field'=>'value'
  203. $sql .= $combop . ' ' . $k . ' = ? ';
  204. $combop = $comboparg;
  205. }
  206. }
  207. return ' ('.$sql.') ';
  208. }
  209. } else {
  210. echo "ERROR: No array in $w<br />\n";
  211. $is_ok = false;
  212. }
  213. }
  214.  
  215. public function get($table, $where){
  216. return $this->action('SELECT *', $table, $where);
  217. }
  218.  
  219. public function delete($table, $where){
  220. return empty($where) ? false : $this->action('DELETE', $table, $where);
  221. }
  222.  
  223. public function deleteById($table,$id){
  224. return $this->action('DELETE',$table,array('id','=',$id));
  225. }
  226.  
  227. public function insert($table, $fields=[], $update=false) {
  228. $keys = array_keys($fields);
  229. $values = [];
  230. $records = 0;
  231.  
  232. foreach ($fields as $field) {
  233. $count = is_array($field) ? count($field) : 1;
  234.  
  235. if (!isset($first_time) || $count<$records) {
  236. $first_time = true;
  237. $records = $count;
  238. }
  239. }
  240.  
  241. for ($i=0; $i<$records; $i++)
  242. foreach ($fields as $field)
  243. $values[] = is_array($field) ? $field[$i] : $field;
  244.  
  245. $col = ",(" . substr( str_repeat(",?",count($fields)), 1) . ")";
  246. $sql = "INSERT INTO {$table} (`". implode('`,`', $keys)."`) VALUES ". substr( str_repeat($col,$records), 1);
  247.  
  248. if ($update) {
  249. $sql .= " ON DUPLICATE KEY UPDATE";
  250.  
  251. foreach ($keys as $key)
  252. if ($key != "id")
  253. $sql .= " `$key` = VALUES(`$key`),";
  254.  
  255. if (!empty($keys))
  256. $sql = substr($sql, 0, -1);
  257. }
  258.  
  259. return !$this->query($sql, $values)->error();
  260. }
  261.  
  262. public function update($table, $id, $fields){
  263. $sql = "UPDATE {$table} SET " . (empty($fields) ? "" : "`") . implode("` = ? , `", array_keys($fields)) . (empty($fields) ? "" : "` = ? ");
  264. $is_ok = true;
  265.  
  266. if (!is_array($id)) {
  267. $sql .= "WHERE id = ?";
  268. $fields[] = $id;
  269. } else {
  270. if (empty($id))
  271. return false;
  272.  
  273. if ($where_text = $this->_calcWhere($id, $fields, "and", $is_ok))
  274. $sql .= "WHERE $where_text";
  275. }
  276.  
  277. if ($is_ok)
  278. if (!$this->query($sql, $fields)->error())
  279. return true;
  280.  
  281. return false;
  282. }
  283.  
  284. public function results($assoc = false){
  285. if($assoc) return ($this->_resultsArray) ? $this->_resultsArray : [];
  286. return ($this->_results) ? $this->_results : [];
  287. }
  288.  
  289. public function first($assoc = false){
  290. return (!$assoc || $assoc && $this->count()>0) ? $this->results($assoc)[0] : [];
  291. }
  292.  
  293. public function count(){
  294. return $this->_count;
  295. }
  296.  
  297. public function error(){
  298. return $this->_error;
  299. }
  300.  
  301. public function errorInfo() {
  302. return $this->_errorInfo;
  303. }
  304.  
  305. public function errorString() {
  306. return 'ERROR #'.$this->_errorInfo[0].': '.$this->_errorInfo[2];
  307. }
  308.  
  309. public function lastId(){
  310. return $this->_lastId;
  311. }
  312.  
  313. public function getQueryCount(){
  314. return $this->_queryCount;
  315. }
  316.  
  317. private function get_subquery_sql($action, $table, $where, &$values, &$is_ok) {
  318. if (is_array($where))
  319. if ($where_text = $this->_calcWhere($where, $values, "and", $is_ok))
  320. $where_text = " WHERE $where_text";
  321.  
  322. return " (SELECT $action FROM $table$where_text)";
  323. }
  324.  
  325. public function cell($tablecolumn, $id=[]) {
  326. $input = explode(".", $tablecolumn, 2);
  327.  
  328. if (count($input) != 2)
  329. return null;
  330.  
  331. $result = $this->action("SELECT {$input[1]}", $input[0], (is_numeric($id) ? ["id","=",$id] : $id));
  332.  
  333. return ($result && $this->_count>0) ? $this->_resultsArray[0][$input[1]] : null;
  334. }
  335.  
  336. public function getColCount(){
  337. return $this->_query->columnCount();
  338. }
  339.  
  340. public function getColMeta($counter){
  341. return $this->_query->getColumnMeta($counter);
  342. }
  343.  
  344. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!