· 9 years ago · May 03, 2017, 08:22 AM
1<?php
2/**
3 * class.db.php
4 *
5 * @author Mustafa Aydemir
6 * @date 14.10.15
7 */
8
9
10class 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?>