API tools faq
paste
Advertisement
Guest User

Untitled

a guest
May 3rd, 2017
78
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 17.11 KB | None | 0 0
raw download clone embed print report
  1. <?php
  2. /**
  3. * class.db.php
  4. *
  5. * @author Mustafa Aydemir
  6. * @date 14.10.15
  7. */
  8.  
  9.  
  10. class db
  11. {
  12.  
  13. public static $user = null;
  14. public static $pass = null;
  15. public static $name = null;
  16. public static $host = null;
  17. public static $port = null;
  18. public static $charset = null;
  19. public static $prefix = null;
  20.  
  21. public static $conn = null;
  22.  
  23.  
  24.  
  25. /**
  26. * @param array $params
  27. *
  28. * @throws Exception
  29. */
  30. public static function connect($params = [])
  31. {
  32. if(self::$user == null) throw_exception('[Database] No user.');
  33. if(self::$name == null) throw_exception('[Database] No database name.');
  34.  
  35. if(self::$host == null) throw_exception('[Database] No host.');
  36. if(self::$charset == null) throw_exception('[Database] No charset.');
  37.  
  38. // Connect
  39. self::$conn = mysqli_connect(self::$host, self::$user, self::$pass, self::$name);
  40. if(!self::$conn)
  41. {
  42. throw_exception('[Database] Connection error.');
  43. }
  44. else
  45. {
  46. mysqli_set_charset(self::$conn, "UTF8");
  47. mysqli_query(self::$conn, "SET NAMES '" . self::$charset . "'");
  48. mysqli_query(self::$conn, "SET character_set_connection = '" . self::$charset . "'");
  49. mysqli_query(self::$conn, "SET character_set_client = '" . self::$charset . "'");
  50. mysqli_query(self::$conn, "SET character_set_results = '" . self::$charset . "'");
  51. }
  52. }
  53.  
  54.  
  55. public static function close()
  56. {
  57. mysqli_close(self::$conn);
  58. }
  59.  
  60.  
  61. public static function error()
  62. {
  63. return mysqli_error(self::$conn);
  64. }
  65.  
  66.  
  67.  
  68. /**
  69. * @param null $query
  70. *
  71. * @return bool|mysqli_result
  72. */
  73. public static function query($query = null)
  74. {
  75. if($query == null) return false;
  76.  
  77. // logger::add($query, 'db');
  78.  
  79. return mysqli_query(self::$conn, $query);
  80. }
  81.  
  82.  
  83.  
  84. /**
  85. * @param null $query
  86. *
  87. * @return array|bool|null
  88. */
  89. public static function fetch ($query = null)
  90. {
  91. if($query == null) return false;
  92.  
  93. return mysqli_fetch_assoc(self::query($query));
  94. }
  95.  
  96.  
  97.  
  98. /**
  99. * @param $q
  100. *
  101. * @return array|null
  102. */
  103. public static function fetch_array ($q)
  104. {
  105. return mysqli_fetch_array($q);
  106. }
  107.  
  108.  
  109.  
  110. /**
  111. * @param $q
  112. *
  113. * @return array|null
  114. */
  115. public static function fetch_assoc ($q)
  116. {
  117. return mysqli_fetch_assoc($q);
  118. }
  119.  
  120.  
  121. public static function insert_id()
  122. {
  123. return mysqli_insert_id(self::$conn);
  124. }
  125.  
  126.  
  127.  
  128. /**
  129. * @param null $table_name
  130. *
  131. * @return string
  132. */
  133. public static function add_prefix($table_name = null)
  134. {
  135. return _config('database.prefix') . $table_name;
  136. }
  137.  
  138.  
  139. /**
  140. * @param null $table_name
  141. * @param array $params
  142. *
  143. * @return array|bool|null
  144. */
  145. public static function get ($table_name = null, $params = [])
  146. {
  147. if($table_name == null) return false;
  148.  
  149. $params['columns'] = array_key_exists('columns', $params) ? $params['columns'] : null;
  150. $params['where'] = array_key_exists('where', $params) ? $params['where'] : null;
  151. $params['order_by'] = array_key_exists('order_by', $params) ? $params['order_by'] : null;
  152. $params['limit'] = array_key_exists('limit', $params) ? $params['limit'] : 1;
  153.  
  154. $columns = $params['columns'] == null ? '*' : '`' . implode('`, `', $params['columns']) . '`';
  155.  
  156. $sql = [];
  157. $sql[] = "SELECT {$columns} FROM `{$table_name}`";
  158. if($params['where'] != null) $sql[] = "WHERE {$params['where']}";
  159. if($params['order_by'] != null) $sql[] = "ORDER BY {$params['order_by']}";
  160. if($params['limit'] != null) $sql[] = "LIMIT {$params['limit']}";
  161.  
  162. $sql_query = implode(' ', $sql);
  163.  
  164. $query = self::query($sql_query);
  165. $result = self::fetch_assoc($query);
  166.  
  167. return $result;
  168. }
  169.  
  170.  
  171.  
  172. /**
  173. * @param null $table_name
  174. * @param array $params
  175. *
  176. * @return array|bool
  177. */
  178. public static function get_all ($table_name = null, $params = [])
  179. {
  180. if($table_name == null) return false;
  181.  
  182. $params['columns'] = array_key_exists('columns', $params) ? $params['columns'] : null;
  183. $params['where'] = array_key_exists('where', $params) ? $params['where'] : null;
  184. $params['order_by'] = array_key_exists('order_by', $params) ? $params['order_by'] : null;
  185. $params['group_by'] = array_key_exists('group_by', $params) ? $params['group_by'] : null;
  186. $params['limit'] = array_key_exists('limit', $params) ? $params['limit'] : null;
  187.  
  188. $params['page_no'] = array_key_exists('page_no', $params) ? $params['page_no'] : 1;
  189. $params['page'] = array_key_exists('page', $params) ? $params['page'] : false;
  190.  
  191. if($params['page'])
  192. {
  193. $page = (int)input::get($params['page']);
  194. if($page) $params['page_no'] = $page;
  195. }
  196.  
  197. $limit = ($params['page_no'] - 1) * $params['limit'];
  198. $sql_limit = $limit . ', ' . $params['limit'];
  199.  
  200. $columns = $params['columns'] == null ? '*' : '`' . implode('`, `', $params['columns']) . '`';
  201.  
  202. $sql = [];
  203. $sql[] = "SELECT {$columns} FROM `{$table_name}`";
  204. if($params['where'] != null) $sql[] = "WHERE {$params['where']}";
  205. if($params['group_by'] != null) $sql[] = "GROUP BY {$params['group_by']}";
  206. if($params['order_by'] != null) $sql[] = "ORDER BY {$params['order_by']}";
  207. if($params['limit'] != null) $sql[] = "LIMIT {$sql_limit}";
  208.  
  209. $sql_query = implode(' ', $sql);
  210.  
  211. $r = [];
  212. $q = self::query($sql_query);
  213.  
  214. while($ql = self::fetch_assoc($q))
  215. $r[] = $ql;
  216.  
  217. return $r;
  218. }
  219.  
  220.  
  221.  
  222. /**
  223. * @param null $table_name
  224. * @param array $params
  225. *
  226. * @return array|bool
  227. */
  228. public static function group ($table_name = null, $params = [])
  229. {
  230. if($table_name == null) return false;
  231.  
  232. $params['columns'] = array_key_exists('columns', $params) ? $params['columns'] : null;
  233. $params['where'] = array_key_exists('where', $params) ? $params['where'] : null;
  234. $params['order_by'] = array_key_exists('order_by', $params) ? $params['order_by'] : null;
  235. $params['group_by'] = array_key_exists('group_by', $params) ? $params['group_by'] : null;
  236. $params['limit'] = array_key_exists('limit', $params) ? $params['limit'] : 100;
  237. $params['page_no'] = array_key_exists('page_no', $params) ? $params['page_no'] : 1;
  238.  
  239. $limit = ($params['page_no'] - 1) * $params['limit'];
  240. $sql_limit = $limit . ', ' . $params['limit'];
  241.  
  242. $columns = $params['columns'] == null ? '*' : '`' . implode('`, `', $params['columns']) . '`';
  243.  
  244. $sql = [];
  245. $sql[] = "SELECT {$columns}, COUNT(*) FROM `{$table_name}`";
  246. if($params['where'] != null) $sql[] = "WHERE {$params['where']}";
  247. if($params['group_by'] != null) $sql[] = "GROUP BY {$params['group_by']}";
  248. if($params['order_by'] != null) $sql[] = "ORDER BY {$params['order_by']}";
  249. if($params['limit'] != null) $sql[] = "LIMIT {$sql_limit}";
  250.  
  251. $sql_query = implode(' ', $sql);
  252.  
  253. $r = [];
  254. $q = self::query($sql_query);
  255.  
  256. while($ql = self::fetch_assoc($q))
  257. $r[] = $ql;
  258.  
  259. return $r;
  260. }
  261.  
  262.  
  263. /**
  264. * @param null $table_name
  265. * @param null $where
  266. * @param null $others
  267. *
  268. * @return bool
  269. * @throws Exception
  270. */
  271. public static function count_rows ($table_name = null, $params = [])
  272. {
  273. if($table_name == null) return false;
  274.  
  275. $params['where'] = array_key_exists('where', $params) ? $params['where'] : null;
  276. $params['order_by'] = array_key_exists('order_by', $params) ? $params['order_by'] : null;
  277. $params['group_by'] = array_key_exists('group_by', $params) ? $params['group_by'] : null;
  278. $params['limit'] = array_key_exists('limit', $params) ? $params['limit'] : null;
  279.  
  280. $sql = [];
  281. $sql[] = "SELECT COUNT(*) FROM `{$table_name}`";
  282. if($params['where'] != null) $sql[] = "WHERE {$params['where']}";
  283. if($params['group_by'] != null) $sql[] = "GROUP BY {$params['group_by']}";
  284. if($params['order_by'] != null) $sql[] = "ORDER BY {$params['order_by']}";
  285. if($params['limit'] != null) $sql[] = "LIMIT {$params['limit']}";
  286.  
  287. $sql_query = implode(' ', $sql);
  288.  
  289. $num = self::fetch_array(self::query($sql_query));
  290.  
  291. return $num[0];
  292. }
  293.  
  294.  
  295. /**
  296. * @param null $table_name
  297. * @param array $data
  298. *
  299. * @return bool|int|string
  300. */
  301. public static function insert($table_name = null, $data = [])
  302. {
  303. hook::listen("$table_name:db.insert", 'before', $data);
  304.  
  305. if($table_name == null) return false;
  306.  
  307. $insert_data_arr = [];
  308.  
  309. foreach($data as $k => $v)
  310. {
  311. if($v == null)
  312. {
  313. $insert_data_arr[] = "`{$k}`=NULL";
  314. }
  315. else
  316. {
  317. $v = trim($v, "'");
  318.  
  319. if(is_int($v))
  320. {
  321. $insert_data_arr[] = "`{$k}`={$v}";
  322. }
  323. else
  324. {
  325. $insert_data_arr[] = "`{$k}`='{$v}'";
  326. }
  327. }
  328. }
  329.  
  330. $insert_data = implode(', ', $insert_data_arr);
  331. $sql_query = "INSERT INTO `{$table_name}` SET {$insert_data}";
  332.  
  333. $insert = self::query($sql_query);
  334.  
  335. hook::listen("$table_name:db.insert", 'after', $data);
  336.  
  337. if($insert)
  338. {
  339. return self::insert_id();
  340. }
  341. else
  342. {
  343. logger::add("[Insert Data] {$sql_query}", 'db');
  344. return false;
  345. }
  346. }
  347.  
  348.  
  349.  
  350. /**
  351. * @param null $table_name
  352. * @param array $data
  353. * @param array $params
  354. *
  355. * @return bool
  356. */
  357. public static function update($table_name = null, $data = [], $params = [])
  358. {
  359. hook::listen("$table_name:db.update", 'before', $data);
  360.  
  361. if($table_name == null) return false;
  362.  
  363. $update_data_arr = [];
  364. $sql = [];
  365.  
  366. $params['where'] = array_key_exists('where', $params) ? $params['where'] : null;
  367. $params['order_by'] = array_key_exists('order_by', $params) ? $params['order_by'] : null;
  368. $params['limit'] = array_key_exists('limit', $params) ? $params['limit'] : null;
  369.  
  370. foreach($data as $k => $v)
  371. {
  372. if($v == null)
  373. {
  374. $update_data_arr[] = "`{$k}`=NULL";
  375. }
  376. else
  377. {
  378. $v = trim($v, "'");
  379.  
  380. if(is_int($v))
  381. {
  382. $update_data_arr[] = "`{$k}`={$v}";
  383. }
  384. else
  385. {
  386. $update_data_arr[] = "`{$k}`='{$v}'";
  387. }
  388. }
  389. }
  390.  
  391. $update_data = implode(', ', $update_data_arr);
  392. $sql[] = "UPDATE `{$table_name}` SET {$update_data}";
  393. if($params['where'] != null) $sql[] = "WHERE {$params['where']}";
  394. if($params['order_by'] != null) $sql[] = "ORDER BY {$params['order_by']}";
  395. if($params['limit'] != null) $sql[] = "LIMIT {$params['limit']}";
  396.  
  397. $sql_query = implode(' ', $sql);
  398.  
  399. $update = self::query($sql_query);
  400.  
  401. hook::listen("$table_name:db.update", 'after', $data);
  402.  
  403. if($update)
  404. {
  405. return self::affected_rows();
  406. }
  407. else
  408. {
  409. logger::add("[Updated Data] {$sql_query}", 'db');
  410. return false;
  411. }
  412. }
  413.  
  414.  
  415.  
  416. /**
  417. * @param null $table_name
  418. * @param array $params
  419. *
  420. * @return bool
  421. */
  422. public static function delete ($table_name = null, $params = [])
  423. {
  424. hook::listen("$table_name:db.delete", 'before', $params);
  425.  
  426. if($table_name == null) return false;
  427.  
  428. $params['where'] = array_key_exists('where', $params) ? $params['where'] : null;
  429. $params['limit'] = array_key_exists('limit', $params) ? $params['limit'] : null;
  430.  
  431. $sql = [];
  432. $sql[] = "DELETE FROM `{$table_name}`";
  433. if($params['where'] != null) $sql[] = "WHERE {$params['where']}";
  434. if($params['limit'] != null) $sql[] = "LIMIT {$params['limit']}";
  435.  
  436. $sql_query = implode(' ', $sql);
  437.  
  438. if( self::query($sql_query) )
  439. {
  440. hook::listen("$table_name:db.delete", 'after', $params);
  441.  
  442. return true;
  443. }
  444. else
  445. {
  446. return false;
  447. }
  448. }
  449.  
  450.  
  451. /**
  452. * @param null $table_name
  453. *
  454. * @return array|null
  455. */
  456. public static function get_columns($table_name = null)
  457. {
  458. if($table_name == null) return null;
  459.  
  460. $db_name = self::$name;
  461. $columns = [];
  462.  
  463. $query = self::query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{$db_name}' AND TABLE_NAME = '{$table_name}'");
  464.  
  465. while($ql = self::fetch_array($query)) $columns[] = $ql['COLUMN_NAME'];
  466.  
  467. return $columns;
  468. }
  469.  
  470.  
  471.  
  472. /**
  473. * @param null $table_name
  474. * @param null $columns
  475. *
  476. * @return bool
  477. *
  478. * $columns = [
  479. *
  480. * [
  481. * 'name' => 'column_name',
  482. * 'type' => 'int(11)' // int(11), varchar(255), float, text
  483. * ],
  484. * [
  485. * 'name' => 'column_name',
  486. * 'type' => 'int(11)' // int(11), varchar(255), float, text
  487. * ]
  488. * ]
  489. */
  490. public static function create_table($table_name = null, $columns = null)
  491. {
  492. if($table_name == null) return false;
  493.  
  494. $table_name = _config('database.prefix') . $table_name;
  495.  
  496. if($columns != null)
  497. {
  498. $columns_sql = [];
  499.  
  500. foreach($columns as $column)
  501. {
  502. if($column['name'] == 'id') continue;
  503. $columns_sql[] = "`{$column['name']}` {$column['type']} DEFAULT NULL";
  504. }
  505.  
  506. $cols = implode(',', $columns_sql);
  507. }
  508.  
  509.  
  510. $sql = "
  511.  
  512. CREATE TABLE IF NOT EXISTS `$table_name` (
  513. `id` INT(11) NOT NULL AUTO_INCREMENT,
  514. `created_at` int DEFAULT NULL,
  515. `updated_at` int DEFAULT NULL,
  516.  
  517. PRIMARY KEY (`id`)
  518. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  519.  
  520. ";
  521.  
  522. return self::query($sql);
  523. }
  524.  
  525.  
  526. public static function rename_table($table_name = null, $new_name = null)
  527. {
  528. $table_name = _config('database.prefix') . $table_name;
  529.  
  530. return self::query("ALTER TABLE $table_name RENAME TO $new_name;");
  531. }
  532.  
  533.  
  534. public static function add_column($table_name = null, $column_name = null, $type = null)
  535. {
  536. $table_name = _config('database.prefix') . $table_name;
  537.  
  538. return self::query("ALTER TABLE $table_name ADD $column_name $type;");
  539. }
  540.  
  541.  
  542. public static function delete_column($table_name = null, $column_name = null)
  543. {
  544. $table_name = _config('database.prefix') . $table_name;
  545.  
  546. return self::query("ALTER TABLE $table_name DROP COLUMN $column_name;");
  547. }
  548.  
  549.  
  550. public static function delete_table($table_name = null)
  551. {
  552. $table_name = _config('database.prefix') . $table_name;
  553.  
  554. return self::query("DROP TABLE IF EXISTS $table_name;");
  555. }
  556.  
  557.  
  558. public static function edit_column($table_name = null, $column_name = null, $new_column_name = false, $type = 'text')
  559. {
  560. if($column_name == $new_column_name)
  561.  
  562. $table_name = _config('database.prefix') . $table_name;
  563.  
  564. $new_column_name = $new_column_name ? $new_column_name : $column_name;
  565. return self::query("ALTER TABLE $table_name CHANGE $column_name $new_column_name $type;");
  566. }
  567.  
  568.  
  569. public static function search_where_query($table_name = null, $string = null)
  570. {
  571. if($string == null) return null;
  572.  
  573. $columns = self::get_columns(_config('database.prefix') . $table_name);
  574. $where_column = [];
  575.  
  576. foreach($columns as $column) $where_column[] = "`$column` LIKE '%$string%'";
  577.  
  578. return implode(' OR ', $where_column);
  579. }
  580.  
  581.  
  582. public static function get_info()
  583. {
  584. $info = [];
  585. $info['user'] = self::$user;
  586. $info['pass'] = self::$pass;
  587. $info['name'] = self::$name;
  588. $info['host'] = self::$host;
  589. $info['port'] = self::$port;
  590. $info['charset'] = self::$charset;
  591. $info['prefix'] = self::$prefix;
  592.  
  593. return $info;
  594. }
  595.  
  596.  
  597. public static function dump()
  598. {
  599. $user = self::$user;
  600. $pass = self::$pass;
  601. $host = self::$host;
  602. $name = self::$name;
  603.  
  604. $path = SYSDATA_PATH . DS . 'backups' . DS . 'backup_' . date('d-m-Y_H-i') . '.sql';
  605.  
  606. sys::exec("mysqldump --user=$user --password=$pass --host=$host $name > $path");
  607. }
  608.  
  609.  
  610. public static function affected_rows()
  611. {
  612. return mysqli_affected_rows(self::$conn);
  613. }
  614.  
  615.  
  616. public static function export()
  617. {
  618. $file = BACKUPS_PATH . DS . date('d-m-Y_H-i-s') . '.backup';
  619. $datas = item::get_all();
  620. $datas = json::encode($datas);
  621.  
  622. $write = [];
  623. $write['file'] = $file;
  624. $write['data'] = $datas;
  625.  
  626. return sys::write($write);
  627. }
  628.  
  629.  
  630. }
  631. ?>
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!