Passed
Push — main ( 03f6f3...31e835 )
by Miaad
10:35
created

mysql::insertUpdate()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 6
Code Lines 5

Duplication

Lines 0
Ratio 0 %

Importance

Changes 1
Bugs 0 Features 0
Metric Value
cc 1
eloc 5
c 1
b 0
f 0
nc 1
nop 4
dl 0
loc 6
rs 10
1
<?php
2
3
namespace BPT\database;
4
5
use BPT\BPT;
6
use BPT\constants\chatMemberStatus;
7
use BPT\constants\chatType;
8
use BPT\constants\loggerTypes;
9
use BPT\exception\bptException;
10
use BPT\lock;
11
use BPT\logger;
12
use BPT\settings;
13
use BPT\tools;
14
use BPT\types\callbackQuery;
15
use BPT\types\chatMemberUpdated;
16
use BPT\types\inlineQuery;
17
use BPT\types\message;
18
use Generator;
19
use mysqli;
20
use mysqli_result;
21
22
class mysql {
23
    private static mysqli $connection;
24
25
    private static bool $auto_process = true;
26
27
    private static string $db_name = '';
28
29
    private static array $default_where = [];
30
31
    /**
32
     * If you want to use it in standalone mode , you MUST set `auto_process` to `false`
33
     */
34
    public static function init (string $host = 'localhost', string $username = 'root', string $password = '', string $dbname = '', bool $auto_process = null, int $port = 3306): void {
35
        $host = settings::$db['host'] ?? $host;
36
        $port = settings::$db['port'] ?? $port;
37
        $user = settings::$db['user'] ?? settings::$db['username'] ?? $username;
38
        $pass = settings::$db['pass'] ?? settings::$db['password'] ?? $password;
39
        self::$auto_process = $auto_process ?? (!isset(settings::$db['auto_process']) || (isset(settings::$db['auto_process']) && settings::$db['auto_process'] == true));
40
        $dbname = settings::$db['dbname'] ?? $dbname;
41
        self::$db_name = $dbname;
42
        self::$connection = new mysqli($host, $user, $pass, $dbname, $port);
43
        if (self::$connection->connect_errno) {
44
            logger::write('SQL connection has problem : ' . self::$connection->connect_error, loggerTypes::ERROR);
45
            throw new bptException('SQL_CONNECTION_PROBLEM');
46
        }
47
        if (self::$auto_process && !lock::exist('BPT-MYSQL')) {
48
            self::install();
49
        }
50
    }
51
52
    private static function install (): void {
53
        self::pureQuery("
54
CREATE TABLE `users`
55
(
56
    `id`           BIGINT(20) NOT NULL,
57
    `username`     VARCHAR(32) NULL DEFAULT NULL,
58
    `lang_code`    VARCHAR(3)  NULL DEFAULT NULL,
59
    `first_active` INT(11) NOT NULL DEFAULT '0',
60
    `last_active`  INT(11) NOT NULL DEFAULT '0',
61
    `referral`     BIGINT(20) NULL DEFAULT NULL,
62
    `blocked`      BOOLEAN     NOT NULL DEFAULT FALSE,
63
    `step`         VARCHAR(64) NOT NULL DEFAULT 'main',
64
    `value`        TEXT NULL DEFAULT NULL,
65
    PRIMARY KEY (`id`)
66
) ENGINE = InnoDB;");
67
        lock::set('BPT-MYSQL');
68
    }
69
70
    /**
71
     * @internal Only for BPT self usage , Don't use it in your source!
72
     */
73
    public static function process (): void {
74
        if (self::$auto_process) {
75
            if (isset(BPT::$update->message)) {
76
                self::processMessage(BPT::$update->message);
0 ignored issues
show
Bug introduced by
It seems like BPT\BPT::update->message can also be of type null; however, parameter $update of BPT\database\mysql::processMessage() does only seem to accept BPT\types\message, maybe add an additional type check? ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-type  annotation

76
                self::processMessage(/** @scrutinizer ignore-type */ BPT::$update->message);
Loading history...
77
            }
78
            elseif (isset(BPT::$update->edited_message)) {
79
                self::processMessage(BPT::$update->edited_message);
80
            }
81
            elseif (isset(BPT::$update->callback_query)) {
82
                self::processCallbackQuery(BPT::$update->callback_query);
0 ignored issues
show
Bug introduced by
It seems like BPT\BPT::update->callback_query can also be of type null; however, parameter $update of BPT\database\mysql::processCallbackQuery() does only seem to accept BPT\types\callbackQuery, maybe add an additional type check? ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-type  annotation

82
                self::processCallbackQuery(/** @scrutinizer ignore-type */ BPT::$update->callback_query);
Loading history...
83
            }
84
            elseif (isset(BPT::$update->inline_query)) {
85
                self::processInlineQuery(BPT::$update->inline_query);
0 ignored issues
show
Bug introduced by
It seems like BPT\BPT::update->inline_query can also be of type null; however, parameter $update of BPT\database\mysql::processInlineQuery() does only seem to accept BPT\types\inlineQuery, maybe add an additional type check? ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-type  annotation

85
                self::processInlineQuery(/** @scrutinizer ignore-type */ BPT::$update->inline_query);
Loading history...
86
            }
87
            elseif (isset(BPT::$update->my_chat_member)) {
88
                self::processMyChatMember(BPT::$update->my_chat_member);
0 ignored issues
show
Bug introduced by
It seems like BPT\BPT::update->my_chat_member can also be of type null; however, parameter $update of BPT\database\mysql::processMyChatMember() does only seem to accept BPT\types\chatMemberUpdated, maybe add an additional type check? ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-type  annotation

88
                self::processMyChatMember(/** @scrutinizer ignore-type */ BPT::$update->my_chat_member);
Loading history...
89
            }
90
        }
91
    }
92
93
    private static function processMessage (message $update): void {
94
        $type = $update->chat->type;
95
        if ($type === chatType::PRIVATE) {
96
            $user_id = $update->from->id;
97
            $first_active = $last_active = time();
98
            $referral = null;
99
            $username = $update->from->username;
100
            $lang_code = $update->from->language_code;
101
            if (isset($update->command) && isset($update->command_payload) && $update->command === 'start' && str_starts_with($update->command_payload, 'ref_')) {
0 ignored issues
show
Bug introduced by
It seems like $update->command_payload can also be of type null; however, parameter $haystack of str_starts_with() does only seem to accept string, maybe add an additional type check? ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-type  annotation

101
            if (isset($update->command) && isset($update->command_payload) && $update->command === 'start' && str_starts_with(/** @scrutinizer ignore-type */ $update->command_payload, 'ref_')) {
Loading history...
102
                if (tools::isShorted(substr($update->command_payload, 4))) {
0 ignored issues
show
Bug introduced by
It seems like $update->command_payload can also be of type null; however, parameter $string of substr() does only seem to accept string, maybe add an additional type check? ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-type  annotation

102
                if (tools::isShorted(substr(/** @scrutinizer ignore-type */ $update->command_payload, 4))) {
Loading history...
103
                    $referral = tools::shortDecode(substr($update->command_payload, 4));
104
                }
105
            }
106
            self::query("INSERT INTO `users`(`id`, `username`, `lang_code`, `first_active`, `last_active`, `referral`) VALUES (?,?,?,?,?,?) on duplicate key update `last_active` = ?, `username` = ?", [
107
                $user_id,
108
                $username,
109
                $lang_code,
110
                $first_active,
111
                $last_active,
112
                $referral,
113
                $last_active,
114
                $username
115
            ]);
116
        }
117
    }
118
119
    private static function processCallbackQuery (callbackQuery $update): void {
120
        $type = $update->message->chat->type;
121
        if ($type === chatType::PRIVATE) {
122
            $user_id = $update->from->id;
123
            $last_active = time();
124
            $username = $update->from->username;
125
            self::update('users', ['last_active' => $last_active, 'username' => $username], ['id' => $user_id], 1);
126
        }
127
    }
128
129
    private static function processInlineQuery (inlineQuery $update): void {
130
        $type = $update->chat_type;
131
        if ($type === chatType::PRIVATE || $type === chatType::SENDER) {
132
            $user_id = $update->from->id;
133
            $last_active = time();
134
            $username = $update->from->username;
135
            self::update('users', ['last_active' => $last_active, 'username' => $username], ['id' => $user_id], 1);
136
        }
137
    }
138
139
    private static function processMyChatMember (chatMemberUpdated $update): void {
140
        $type = $update->chat->type;
141
        if ($type === chatType::PRIVATE) {
142
            if ($update->new_chat_member->status === chatMemberStatus::MEMBER) {
143
                self::update('users', ['blocked' => false], ['id' => $update->from->id], 1);
144
            }
145
            else {
146
                self::update('users', ['blocked' => true], ['id' => $update->from->id], 1);
147
            }
148
        }
149
    }
150
151
    /**
152
     * Add default where to mysql queries, it will apply on every query automatically
153
     *
154
     * Note : where parameter in each query has more priority and will rewrite if already exist
155
     *
156
     * Note : only builder method support this(methods with where parameter like select, update and ...)
157
     *
158
     * @param array $where
159
     *
160
     * @return bool
161
     */
162
    public static function addDefaultWhere (array $where): bool {
163
        if (empty(self::$default_where)) {
164
            self::$default_where = $where;
165
        }
166
        else {
167
            self::$default_where = array_merge(self::$default_where, $where);
168
        }
169
        return true;
170
    }
171
172
    /**
173
     * Get real mysqli connections
174
     *
175
     * @return mysqli
176
     */
177
    public static function getMysqli (): mysqli {
178
        return self::$connection;
179
    }
180
181
    /**
182
     * Get affected rows
183
     *
184
     * same as affectedRows
185
     *
186
     * @return int|string
187
     */
188
    public static function affected_rows (): int|string {
189
        return self::$connection->affected_rows;
190
    }
191
192
    /**
193
     * Get affected rows
194
     *
195
     * same as affected_rows
196
     *
197
     * @return int|string
198
     */
199
    public static function affectedRows (): int|string {
200
        return self::$connection->affected_rows;
201
    }
202
203
    /**
204
     * Get inserted id
205
     *
206
     * same as insertId
207
     *
208
     * @return int|string
209
     */
210
    public static function insert_id (): int|string {
211
        return self::$connection->insert_id;
212
    }
213
214
    /**
215
     * Get inserted id
216
     *
217
     * same as insert_id
218
     *
219
     * @return int|string
220
     */
221
    public static function insertId (): int|string {
222
        return self::$connection->insert_id;
223
    }
224
225
    /**
226
     * Escape string with real_escape_string of mysqli class
227
     *
228
     * @param string $text
229
     *
230
     * @return string
231
     */
232
    public static function escapeString (string $text): string {
233
        return self::$connection->real_escape_string($text);
234
    }
235
236
    /**
237
     * Get last error
238
     *
239
     * @return string
240
     */
241
    public static function error (): string {
242
        return self::$connection->error;
243
    }
244
245
    /**
246
     * Get last error code
247
     *
248
     * @return int
249
     */
250
    public static function errno (): int {
251
        return self::$connection->errno;
252
    }
253
254
    /**
255
     * set database charset
256
     *
257
     * @param string $charset
258
     *
259
     * @return bool
260
     */
261
    public static function setCharset (string $charset): bool {
262
        return self::$connection->set_charset($charset);
263
    }
264
265
    /**
266
     * Run query as what is it
267
     *
268
     * The library doesn't do anything on it
269
     *
270
     * It's like calling mysqli->query();
271
     *
272
     * @param string $query
273
     *
274
     * @return mysqli_result|bool
275
     */
276
    public static function pureQuery (string $query): mysqli_result|bool {
277
        return self::$connection->query($query);
278
    }
279
280
    /**
281
     * Run query with safe execution
282
     *
283
     * Replace inputs with `?` in query to be replaced safely with $vars in order
284
     *
285
     * it will use `pureQuery` if `$vars` be empty
286
     *
287
     * e.g. : mysql::query('select * from `users` where `id` = ? limit 1',[123456789]);
288
     *
289
     * e.g. : mysql::query('update `users` set `step` = ? where `id` = ? limit 1',['main',123456789]);
290
     *
291
     * @param string $query
292
     * @param array  $vars        default [] or empty
293
     * @param bool   $need_result set if you want result be returned, default : true
294
     *
295
     * @return mysqli_result|bool
296
     */
297
    public static function query (string $query, array $vars = [], bool $need_result = true): mysqli_result|bool {
298
        if (empty($vars)) {
299
            return self::pureQuery($query);
300
        }
301
        $prepare = self::$connection->prepare($query);
302
        $types = '';
303
        foreach ($vars as $var) {
304
            if (is_int($var)) {
305
                $types .= 'i';
306
            }
307
            elseif (is_double($var)) {
308
                $types .= 'd';
309
            }
310
            else {
311
                $types .= 's';
312
            }
313
        }
314
        $prepare->bind_param($types,...$vars);
315
        if (!$prepare->execute()) {
316
            logger::write(loggerTypes::WARNING, $prepare->error);
317
            return false;
318
        }
319
        return $need_result ? $prepare->get_result() : true;
320
    }
321
322
    private static function whereBuilder(string &$query, array $where = null, bool $ignore_default_where = false): array {
323
        if (!$ignore_default_where) {
324
            $where = array_merge(self::$default_where, $where);
0 ignored issues
show
Bug introduced by
It seems like $where can also be of type null; however, parameter $arrays of array_merge() does only seem to accept array, maybe add an additional type check? ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-type  annotation

324
            $where = array_merge(self::$default_where, /** @scrutinizer ignore-type */ $where);
Loading history...
325
        }
326
327
        if (empty($where)) {
328
            return [];
329
        }
330
331
        $query .= ' WHERE';
332
        $first = true;
333
        $values = [];
334
335
        foreach ($where as $name => $value) {
336
            if ($first) {
337
                $first = false;
338
            }
339
            else {
340
                $query .= ' AND';
341
            }
342
343
            if (empty($value)) {
344
                $query .= " `$name` = ?";
345
                $values[] = $value;
346
                continue;
347
            }
348
349
            if (!is_array($value)) {
350
                $value = [$value];
351
            }
352
353
            $sub_first = true;
354
            foreach ($value as $sub_value) {
355
                if ($sub_first) {
356
                    $sub_first = false;
357
                }
358
                else {
359
                    $query .= ' AND';
360
                }
361
                $operator = substr($sub_value,0,2);
362
                $operator_value = substr($sub_value,2);
363
                switch ($operator) {
364
                    case '>=':
365
                        $query .= " `$name` >= ?";
366
                        $sub_value = $operator_value;
367
                        break;
368
                    case '<=':
369
                        $query .= " `$name` <= ?";
370
                        $sub_value = $operator_value;
371
                        break;
372
                    case '> ':
373
                        $query .= " `$name` > ?";
374
                        $sub_value = $operator_value;
375
                        break;
376
                    case '< ':
377
                        $query .= " `$name` < ?";
378
                        $sub_value = $operator_value;
379
                        break;
380
                    case '% ':
381
                        $query .= " `$name` like ?";
382
                        $sub_value = $operator_value;
383
                        break;
384
                    case '!=':
385
                        $query .= " `$name` != ?";
386
                        $sub_value = $operator_value;
387
                        break;
388
                    case '##':
389
                        $operator = substr($operator_value,0,2);
390
                        $column = substr($operator_value,2);
391
                        $query .= match ($operator) {
392
                            '>=' => " `$name` >= `$column`",
393
                            '<=' => " `$name` <= `$column`",
394
                            '> ' => " `$name` > `$column`",
395
                            '< ' => " `$name` < `$column`",
396
                            '% ' => " `$name` like `$column`",
397
                            '!=' => " `$name` != `$column`",
398
                            default => " `$name` = `$column`",
399
                        };
400
                        continue 2;
401
                    default:
402
                        $query .= " `$name` = ?";
403
                        break;
404
                }
405
406
                $values[] = $sub_value;
407
            }
408
409
        }
410
411
        return $values;
412
    }
413
414
    private static function groupByBuilder(string &$query, string|array $group_by = []): void {
415
        if (empty($group_by)) {
416
            return;
417
        }
418
        if (is_string($group_by)) {
0 ignored issues
show
introduced by
The condition is_string($group_by) is always false.
Loading history...
419
            $group_by = [$group_by];
420
        }
421
        $query .= ' GROUP BY `' . implode('`, `',$group_by) . '`';
422
    }
423
424
    private static function orderByBuilder(string &$query, string|array $order_by = []): void {
425
        if (empty($order_by)) {
426
            return;
427
        }
428
        if (is_string($order_by)) {
0 ignored issues
show
introduced by
The condition is_string($order_by) is always false.
Loading history...
429
            $order_by = [$order_by => 'ASC'];
430
        }
431
432
        $query .= ' ORDER BY `';
433
434
        $first = true;
435
        foreach ($order_by as $key => $mode) {
436
            if ($first) {
437
                $first = false;
438
            }
439
            else {
440
                $query .= ', ';
441
            }
442
            if (is_numeric($key)) {
443
                $key = $mode;
444
                $mode = 'ASC';
445
            }
446
            $query .= "$key` $mode";
447
        }
448
    }
449
450
    private static function countBuilder(string &$query, int $count = null, int $offset = null): void {
451
        if (!empty($count)) {
452
            $query .= !empty($offset) ? " LIMIT $offset, $count" : " LIMIT $count";
453
        }
454
        elseif (!empty($offset)) {
455
            $query .= " OFFSET $offset";
456
        }
457
    }
458
459
    private static function updateBuilder(string &$query, array $modify): array {
460
        $first = true;
461
        $values = [];
462
463
        foreach ($modify as $name => $value) {
464
            if ($first) {
465
                $first = false;
466
            }
467
            else {
468
                $query .= ',';
469
            }
470
471
            if (empty($value)) {
472
                $query .= " `$name` = ?";
473
                $values[] = $value;
474
                continue;
475
            }
476
477
            $operator = substr($value,0,2);
478
            $operator_value = substr($value,2);
479
            switch ($operator) {
480
                case '+=':
481
                    $query .= " `$name` = `$name` + ?";
482
                    $value = $operator_value;
483
                    break;
484
                case '-=':
485
                    $query .= " `$name` = `$name` - ?";
486
                    $value = $operator_value;
487
                    break;
488
                case '*=':
489
                    $query .= " `$name` = `$name` * ?";
490
                    $value = $operator_value;
491
                    break;
492
                case '/=':
493
                    $query .= " `$name` = `$name` / ?";
494
                    $value = $operator_value;
495
                    break;
496
                case '%=':
497
                    $query .= " `$name` = `$name` % ?";
498
                    $value = $operator_value;
499
                    break;
500
                default:
501
                    $query .= " `$name` = ?";
502
                    break;
503
            }
504
505
            $values[] = $value;
506
        }
507
508
        return $values;
509
    }
510
511
    private static function insertBuilder(string &$query, string|array $columns, array|string $values): array {
512
        $query .= '(`' . (is_string($columns) ? $columns : implode('`, `', $columns)) . '`) VALUES (';
0 ignored issues
show
introduced by
The condition is_string($columns) is always false.
Loading history...
513
        if (is_string($values)) $values = [$values];
0 ignored issues
show
introduced by
The condition is_string($values) is always false.
Loading history...
514
        $query .= '?' . str_repeat(', ?', count($values) - 1) . ')';
515
        return $values;
516
    }
517
518
    private static function selectBuilder (string &$query, string|array $columns): void {
519
        if ($columns == '*') {
0 ignored issues
show
introduced by
The condition $columns == '*' is always false.
Loading history...
520
            $query .= ' * ';
521
            return;
522
        }
523
        if (is_string($columns)) {
0 ignored issues
show
introduced by
The condition is_string($columns) is always false.
Loading history...
524
            $query .= " `$columns` ";
525
            return;
526
        }
527
        $query .= ' ';
528
        foreach ($columns as $key => $column) {
529
            if (is_array($column)) {
530
                $function = array_key_first($column);
531
                $column = $column[$function];
532
                $formatted = "`$column`";
533
                if ($column == '*') {
534
                    $formatted = '*';
535
                    $column = 'all';
536
                }
537
                $query .= strtoupper($function) . "($formatted) as `{$function}_$column`";
538
            }
539
            else {
540
                $query .= "`$column`";
541
            }
542
543
            if ($key != array_key_last($columns)) {
544
                $query .= ', ';
545
            }
546
        }
547
        $query .= ' ';
548
    }
549
550
    /**
551
     * Run delete query
552
     *
553
     * e.g. : `mysql::delete('users',['id'=>123456789],1);`
554
     *
555
     * @param string     $table  table name
556
     * @param array|null $where  Set your ifs default : null
557
     * @param int|null   $count  Set if you want to delete specific amount of row default : null
558
     * @param int|null   $offset Set if you want to delete rows after specific row default : null
559
     *
560
     * @return bool
561
     */
562
    public static function delete (string $table, array $where = null, int $count = null, int $offset = null, bool $ignore_default_where = false): bool {
563
        $query = "DELETE FROM `$table`";
564
        $vars = self::whereBuilder($query, $where, $ignore_default_where);
565
        self::countBuilder($query, $count, $offset);
566
        return self::query($query, $vars, false);
0 ignored issues
show
Bug Best Practice introduced by
The expression return self::query($query, $vars, false) could return the type mysqli_result which is incompatible with the type-hinted return boolean. Consider adding an additional type-check to rule them out.
Loading history...
567
    }
568
569
    /**
570
     * Run update query
571
     *
572
     * e.g. : mysql::update('users',['step'=>'panel'],['id'=>123456789],1);
573
     *
574
     * @param string     $table  table name
575
     * @param array      $modify Set the data's you want to modify
576
     * @param array|null $where  Set your ifs default : null
577
     * @param int|null   $count  Set if you want to update specific amount of row default : null
578
     * @param int|null   $offset Set if you want to update rows after specific row default : null
579
     *
580
     * @return bool
581
     */
582
    public static function update (string $table, array $modify, array $where = null, int $count = null, int $offset = null, bool $ignore_default_where = false): bool {
583
        $query = "UPDATE `$table` SET";
584
        $modify_vars = self::updateBuilder($query, $modify);
585
        $where_vars = self::whereBuilder($query, $where, $ignore_default_where);
586
        self::countBuilder($query, $count, $offset);
587
        return self::query($query, array_merge($modify_vars, $where_vars), false);
0 ignored issues
show
Bug Best Practice introduced by
The expression return self::query($quer...s, $where_vars), false) could return the type mysqli_result which is incompatible with the type-hinted return boolean. Consider adding an additional type-check to rule them out.
Loading history...
588
    }
589
590
    /**
591
     * Run insert query
592
     *
593
     * e.g. : `mysql::insert('users',['id','column1','column2','column3'],[123456789,'value1','value2','value3']);`
594
     *
595
     * @param string       $table   table name
596
     * @param string|array $columns sets columns that you want to fill
597
     * @param array|string $values  sets value that you want to set
598
     *
599
     * @return bool
600
     */
601
    public static function insert (string $table, string|array $columns, array|string $values): bool {
602
        $query = "INSERT INTO `$table`";
603
        $values = self::insertBuilder($query, $columns, $values);
604
        return self::query($query, $values, false);
0 ignored issues
show
Bug Best Practice introduced by
The expression return self::query($query, $values, false) could return the type mysqli_result which is incompatible with the type-hinted return boolean. Consider adding an additional type-check to rule them out.
Loading history...
605
    }
606
607
    /**
608
     * Run insert query with update on duplicate key
609
     *
610
     * These kind of query need to act on a key(primary key, unique key, ...)
611
     *
612
     * So your table must have a key column and your query must use it somehow(in insert part)
613
     *
614
     * e.g.(date is our key) : `mysql::insertUpdate('stats', ['date', 'new_users'], ['2020/04/08', 1], ['new_users' => '+=1']);`
615
     *
616
     *
617
     * @param string       $table   table name
618
     * @param string|array $columns sets columns that you want to fill
619
     * @param array|string $values  sets value that you want to set
620
     * @param array        $modify Set the data's you want to modify
621
     *
622
     * @return bool
623
     */
624
    public static function insertUpdate (string $table, string|array $columns, array|string $values, array $modify): bool {
625
        $query = "INSERT INTO `$table`";
626
        $values = self::insertBuilder($query, $columns, $values);
627
        $query .= ' ON DUPLICATE KEY UPDATE';
628
        $modify_vars = self::updateBuilder($query, $modify);
629
        return self::query($query, array_merge($values, $modify_vars), false);
0 ignored issues
show
Bug Best Practice introduced by
The expression return self::query($quer..., $modify_vars), false) could return the type mysqli_result which is incompatible with the type-hinted return boolean. Consider adding an additional type-check to rule them out.
Loading history...
630
    }
631
632
    /**
633
     * Run select query
634
     *
635
     * e.g. : mysql::select('users','*',['id'=>123456789],1);
636
     *
637
     * e.g. : mysql::select('users',['step','referrals'],['id'=>123456789],1);
638
     *
639
     * @param string       $table   table name
640
     * @param array|string $columns sets column that you want to retrieve , set '*' to retrieve all , default : '*'
641
     * @param array|null   $where   Set your ifs default : null
642
     * @param int|null     $count   Set if you want to select specific amount of row default : null
643
     * @param int|null     $offset  Set if you want to select rows after specific row default : null
644
     * @param array|string $group_by group result based on these columns
645
     * @param array|string $order_by order result based on these columns
646
     *
647
     * @return mysqli_result|bool
648
     */
649
    public static function select (string $table, array|string $columns = '*', array $where = null, int $count = null, int $offset = null, array|string $group_by = [], array|string $order_by = [], bool $ignore_default_where = false): mysqli_result|bool {
650
        $query = 'SELECT';
651
        self::selectBuilder($query, $columns);
652
        $query .= "FROM `$table`";
653
        $var = self::whereBuilder($query,$where, $ignore_default_where);
654
        self::groupByBuilder($query, $group_by);
655
        self::orderByBuilder($query, $order_by);
656
        self::countBuilder($query,$count,$offset);
657
        return self::query($query, $var);
658
    }
659
660
    /**
661
     * Same as mysql::select but return first result as array
662
     *
663
     * mysql::selectArray('users','*',['id'=>123456789]);
664
     *
665
     * @param string       $table   table name
666
     * @param array|string $columns sets column that you want to retrieve , set '*' to retrieve all , default : '*'
667
     * @param array|null   $where   Set your ifs default : null
668
     * @param array|string $group_by group result based on these columns
669
     * @param array|string $order_by order result based on these columns
670
     *
671
     * @return null|bool|array
672
     */
673
    public static function selectArray (string $table, array|string $columns = '*', array $where = null, array|string $group_by = [], array|string $order_by = [], bool $ignore_default_where = false): bool|array|null {
674
        $res = self::select($table, $columns, $where, 1, 0, $group_by, $order_by, ignore_default_where: $ignore_default_where);
675
        if ($res) {
676
            return $res->fetch_assoc();
677
        }
678
        return $res;
679
    }
680
681
    /**
682
     * Same as mysql::select but return first result as object(stdClass)
683
     *
684
     * mysql::selectObject('users','*',['id'=>123456789]);
685
     *
686
     * @param string       $table   table name
687
     * @param array|string $columns sets column that you want to retrieve , set '*' to retrieve all , default : '*'
688
     * @param array|null   $where   Set your ifs default : null
689
     * @param array|string $group_by group result based on these columns
690
     * @param array|string $order_by order result based on these columns
691
     */
692
    public static function selectObject (string $table, array|string $columns = '*', array $where = null, array|string $group_by = [], array|string $order_by = [], bool $ignore_default_where = false) {
693
        $res = self::select($table, $columns, $where, 1, 0, $group_by, $order_by, ignore_default_where: $ignore_default_where);
694
        if ($res) {
695
            return $res->fetch_object();
696
        }
697
        return $res;
698
    }
699
700
    /**
701
     * Same as mysql::select but return each row as generator
702
     *
703
     * e.g. : mysql::selectEach('users','*',['id'=>123456789],1);
704
     * e.g. : mysql::selectEach('users',['id']);
705
     *
706
     * @param string       $table   table name
707
     * @param array|string $columns sets column that you want to retrieve , set '*' to retrieve all , default : '*'
708
     * @param array|null   $where   Set your ifs default : null
709
     * @param int|null     $count   Set if you want to select specific amount of row default : null
710
     * @param int|null     $offset  Set if you want to select rows after specific row default : null
711
     * @param array|string $group_by group result based on these columns
712
     * @param array|string $order_by order result based on these columns
713
     *
714
     * @return bool|Generator
715
     */
716
    public static function selectEach (string $table, array|string $columns = '*', array $where = null, int $count = null, int $offset = null, array|string $group_by = [], array|string $order_by = [], bool $ignore_default_where = false): bool|Generator {
717
        $res = self::select($table, $columns, $where, $count, $offset, $group_by, $order_by, ignore_default_where: $ignore_default_where);
718
        if ($res) {
719
            while ($row = $res->fetch_assoc()) yield $row;
720
        }
721
        else return $res;
722
    }
723
724
    /**
725
     * get backup from database, you can get full backup or specific table backup
726
     *
727
     * @param array|null $wanted_tables set if you want specific table which exist
728
     * @param bool       $table_data set false if you only want the creation queries(no data)
729
     * @param bool       $save set false if you want to receive sql as string
730
     * @param string     $file_name file name for saving
731
     *
732
     * @return string if save is true , return file name otherwise return sql data
733
     */
734
    public static function backup (array $wanted_tables = null, bool $table_data = true, bool $save = true, string $file_name = ''): string {
735
        self::setCharset('utf8mb4');
736
737
        $tables = array_column(self::query('SHOW TABLES')->fetch_all(),0);
738
        if (!empty($wanted_tables)) {
739
            $tables = array_intersect($tables, $wanted_tables);
740
        }
741
742
        $sql = '';
743
744
        if (empty($tables)) {
745
            logger::write('No table founded for backup, if your database has table : check $wanted_tables argument', loggerTypes::WARNING);
746
        }
747
        foreach ($tables as $table) {
748
            $sql .= self::query("SHOW CREATE TABLE `$table`")->fetch_row()[1] . ";\n\n";
749
            if ($table_data) {
750
                $total_rows = self::query("SELECT COUNT(*) as `cnt` FROM `$table`")->fetch_object()->cnt;
751
                for ($i = 0; $i < $total_rows; $i = $i + 1000) {
752
                    $sql .= 'INSERT INTO ' . $table . ' VALUES';
753
                    $result = self::select($table, '*' , null, 1000, $i);
754
                    $field_count = $result->field_count;
755
                    $affected_rows = self::affected_rows();
756
                    $counter = 1;
757
                    while ($row = $result->fetch_row()) {
758
                        $sql .= "\n(";
759
                        for ($column = 0; $column < $field_count; $column++) {
760
                            $row[$column] = str_replace("\n", "\\n", addslashes($row[$column]));
761
                            $sql .= !empty($row[$column]) ? '"' . $row[$column] . '"' : '""';
762
                            if ($column < $field_count - 1) {
763
                                $sql .= ',';
764
                            }
765
                        }
766
                        $sql .= ')' . ($counter == $affected_rows ? ';' : ',');
767
                        $counter++;
768
                    }
769
                }
770
                if ($total_rows > 0) {
771
                    $sql .= "\n\n";
772
                }
773
            }
774
            $sql .= "\n";
775
        }
776
777
        if (!$save) {
778
            return $sql;
779
        }
780
781
        if (empty($file_name)) {
782
            $file_name = self::$db_name . time() . '.sql';
783
        }
784
        file_put_contents($file_name, $sql);
785
        return $file_name;
786
    }
787
}