Passed
Push — main ( 104144...d23e8a )
by Miaad
10:23
created

mysql::process()   B

Complexity

Conditions 7
Paths 7

Size

Total Lines 16
Code Lines 11

Duplication

Lines 0
Ratio 0 %

Importance

Changes 2
Bugs 0 Features 0
Metric Value
cc 7
eloc 11
c 2
b 0
f 0
nc 7
nop 0
dl 0
loc 16
rs 8.8333
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
    /**
28
     * If you want to use it in standalone mode , you MUST set `auto_process` to `false`
29
     */
30
    public static function init (string $host = 'localhost', string $username = 'root', string $password = '', string $dbname = '', bool $auto_process = null, int $port = 3306): void {
31
        $host = settings::$db['host'] ?? $host;
32
        $port = settings::$db['port'] ?? $port;
33
        $user = settings::$db['user'] ?? settings::$db['username'] ?? $username;
34
        $pass = settings::$db['pass'] ?? settings::$db['password'] ?? $password;
35
        self::$auto_process = $auto_process ?? (!isset(settings::$db['auto_process']) || (isset(settings::$db['auto_process']) && settings::$db['auto_process'] == true));
36
        $dbname = settings::$db['dbname'] ?? $dbname;
37
        self::$connection = new mysqli($host, $user, $pass, $dbname, $port);
38
        if (self::$connection->connect_errno) {
39
            logger::write('SQL connection has problem : ' . self::$connection->connect_error, loggerTypes::ERROR);
40
            throw new bptException('SQL_CONNECTION_PROBLEM');
41
        }
42
        if (self::$auto_process && !lock::exist('BPT-MYSQL')) {
43
            self::install();
44
        }
45
    }
46
47
    private static function install (): void {
48
        self::pureQuery("
49
CREATE TABLE `users`
50
(
51
    `id`           BIGINT(20) NOT NULL,
52
    `username`     VARCHAR(32) NULL DEFAULT NULL,
53
    `lang_code`    VARCHAR(3)  NULL DEFAULT NULL,
54
    `first_active` INT(11) NOT NULL DEFAULT '0',
55
    `last_active`  INT(11) NOT NULL DEFAULT '0',
56
    `referral`     BIGINT(20) NULL DEFAULT NULL,
57
    `blocked`      BOOLEAN     NOT NULL DEFAULT FALSE,
58
    `step`         VARCHAR(64) NOT NULL DEFAULT 'main',
59
    `value`        TEXT NULL DEFAULT NULL,
60
    PRIMARY KEY (`id`)
61
) ENGINE = InnoDB;");
62
        lock::set('BPT-MYSQL');
63
    }
64
65
    /**
66
     * @internal Only for BPT self usage , Don't use it in your source!
67
     */
68
    public static function process (): void {
69
        if (self::$auto_process) {
70
            if (isset(BPT::$update->message)) {
71
                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

71
                self::processMessage(/** @scrutinizer ignore-type */ BPT::$update->message);
Loading history...
72
            }
73
            elseif (isset(BPT::$update->edited_message)) {
74
                self::processMessage(BPT::$update->edited_message);
75
            }
76
            elseif (isset(BPT::$update->callback_query)) {
77
                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

77
                self::processCallbackQuery(/** @scrutinizer ignore-type */ BPT::$update->callback_query);
Loading history...
78
            }
79
            elseif (isset(BPT::$update->inline_query)) {
80
                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

80
                self::processInlineQuery(/** @scrutinizer ignore-type */ BPT::$update->inline_query);
Loading history...
81
            }
82
            elseif (isset(BPT::$update->my_chat_member)) {
83
                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

83
                self::processMyChatMember(/** @scrutinizer ignore-type */ BPT::$update->my_chat_member);
Loading history...
84
            }
85
        }
86
    }
87
88
    private static function processMessage (message $update): void {
89
        $type = $update->chat->type;
90
        if ($type === chatType::PRIVATE) {
91
            $user_id = $update->from->id;
92
            $first_active = $last_active = time();
93
            $referral = null;
94
            $username = $update->from->username;
95
            $lang_code = $update->from->language_code;
96
            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

96
            if (isset($update->command) && isset($update->command_payload) && $update->command === 'start' && str_starts_with(/** @scrutinizer ignore-type */ $update->command_payload, 'ref_')) {
Loading history...
97
                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

97
                if (tools::isShorted(substr(/** @scrutinizer ignore-type */ $update->command_payload, 4))) {
Loading history...
98
                    $referral = tools::shortDecode(substr($update->command_payload, 4));
99
                }
100
            }
101
            self::query("INSERT INTO `users`(`id`, `username`, `lang_code`, `first_active`, `last_active`, `referral`) VALUES (?,?,?,?,?,?) on duplicate key update `last_active` = ?, `username` = ?", [
102
                $user_id,
103
                $username,
104
                $lang_code,
105
                $first_active,
106
                $last_active,
107
                $referral,
108
                $last_active,
109
                $username
110
            ]);
111
        }
112
    }
113
114
    private static function processCallbackQuery (callbackQuery $update): void {
115
        $type = $update->message->chat->type;
116
        if ($type === chatType::PRIVATE) {
117
            $user_id = $update->from->id;
118
            $last_active = time();
119
            $username = $update->from->username;
120
            self::update('users', ['last_active' => $last_active, 'username' => $username], ['id' => $user_id], 1);
121
        }
122
    }
123
124
    private static function processInlineQuery (inlineQuery $update): void {
125
        $type = $update->chat_type;
126
        if ($type === chatType::PRIVATE || $type === chatType::SENDER) {
127
            $user_id = $update->from->id;
128
            $last_active = time();
129
            $username = $update->from->username;
130
            self::update('users', ['last_active' => $last_active, 'username' => $username], ['id' => $user_id], 1);
131
        }
132
    }
133
134
    private static function processMyChatMember (chatMemberUpdated $update): void {
135
        $type = $update->chat->type;
136
        if ($type === chatType::PRIVATE) {
137
            if ($update->new_chat_member->status === chatMemberStatus::MEMBER) {
138
                self::update('users', ['blocked' => false], ['id' => $update->from->id], 1);
139
            }
140
            else {
141
                self::update('users', ['blocked' => true], ['id' => $update->from->id], 1);
142
            }
143
        }
144
    }
145
146
    /**
147
     * Get real mysqli connections
148
     *
149
     * @return mysqli
150
     */
151
    public static function getMysqli (): mysqli {
152
        return self::$connection;
153
    }
154
155
    /**
156
     * Get affected rows
157
     *
158
     * @return int|string
159
     */
160
    public static function affected_rows (): int|string {
161
        return self::$connection->affected_rows;
162
    }
163
164
    /**
165
     * Get inserted id
166
     *
167
     * @return int|string
168
     */
169
    public static function insert_id (): int|string {
170
        return self::$connection->insert_id;
171
    }
172
173
    /**
174
     * Escape string with real_escape_string of mysqli class
175
     *
176
     * @param string $text
177
     *
178
     * @return string
179
     */
180
    public static function escapeString (string $text): string {
181
        return self::$connection->real_escape_string($text);
182
    }
183
184
    /**
185
     * Get last error
186
     *
187
     * @return string
188
     */
189
    public static function error (): string {
190
        return self::$connection->error;
191
    }
192
193
    /**
194
     * Get last error code
195
     *
196
     * @return int
197
     */
198
    public static function errno (): int {
199
        return self::$connection->errno;
200
    }
201
202
    /**
203
     * set database charset
204
     *
205
     * @param string $charset
206
     *
207
     * @return bool
208
     */
209
    public static function setCharset (string $charset): bool {
210
        return self::$connection->set_charset($charset);
211
    }
212
213
    /**
214
     * Run query as what is it
215
     *
216
     * The library doesn't do anything on it
217
     *
218
     * It's like calling mysqli->query();
219
     *
220
     * @param string $query
221
     *
222
     * @return mysqli_result|bool
223
     */
224
    public static function pureQuery (string $query): mysqli_result|bool {
225
        return self::$connection->query($query);
226
    }
227
228
    /**
229
     * Run query with safe execution
230
     *
231
     * Replace inputs with `?` in query to be replaced safely with $vars in order
232
     *
233
     * it will use `pureQuery` if `$vars` be empty
234
     *
235
     * e.g. : mysql::query('select * from `users` where `id` = ? limit 1',[123456789]);
236
     *
237
     * e.g. : mysql::query('update `users` set `step` = ? where `id` = ? limit 1',['main',123456789]);
238
     *
239
     * @param string $query
240
     * @param array  $vars        default [] or empty
241
     * @param bool   $need_result set if you want result be returned, default : true
242
     *
243
     * @return mysqli_result|bool
244
     */
245
    public static function query (string $query, array $vars = [], bool $need_result = true): mysqli_result|bool {
246
        if (empty($vars)) {
247
            return self::pureQuery($query);
248
        }
249
        $prepare = self::$connection->prepare($query);
250
        $types = '';
251
        foreach ($vars as $var) {
252
            if (is_int($var)) {
253
                $types .= 'i';
254
            }
255
            elseif (is_double($var)) {
256
                $types .= 'd';
257
            }
258
            else {
259
                $types .= 's';
260
            }
261
        }
262
        $prepare->bind_param($types,...$vars);
263
        if (!$prepare->execute()) {
264
            logger::write(loggerTypes::WARNING, $prepare->error);
265
            return false;
266
        }
267
        return $need_result ? $prepare->get_result() : true;
268
    }
269
270
    private static function makeArrayReady (string &$query, array $array, string $operator = ' AND ', bool $is_update = false): array {
271
        $first = true;
272
        $values = [];
273
        foreach ($array as $name => $value) {
274
            if ($first) {
275
                $first = false;
276
            }
277
            else {
278
                $query .= $operator;
279
            }
280
            if ($is_update && str_starts_with($value, '.=') && is_numeric(substr($value,2))) {
281
                $query .= " `$name` = `$name` + ?";
282
                $values[] = substr($value,2);
283
            }
284
            else {
285
                $query .= " `$name` = ?";
286
                $values[] = $value;
287
            }
288
289
        }
290
        return $values;
291
    }
292
293
    private static function makeQueryReady (string &$query, array $where = null, int $count = null, int $offset = null): array {
0 ignored issues
show
Unused Code introduced by
The method makeQueryReady() is not used, and could be removed.

This check looks for private methods that have been defined, but are not used inside the class.

Loading history...
294
        $values = [];
295
        if (!empty($where)) {
296
            $query .= " WHERE";
297
            $values = self::makeArrayReady($query, $where);
298
        }
299
        if (!empty($count)) {
300
            $query .= !empty($offset) ? " LIMIT $offset,$count" : " LIMIT $count";
301
        }
302
        elseif (!empty($offset)) {
303
            $query .= " OFFSET $offset";
304
        }
305
        return $values;
306
    }
307
308
    private static function whereBuilder(string &$query, array $where = null): array {
309
        if (empty($where)) {
310
            return [];
311
        }
312
313
        $query .= " WHERE";
314
        $first = true;
315
        $values = [];
316
317
        foreach ($where as $name => $value) {
318
            if ($first) {
319
                $first = false;
320
            }
321
            else {
322
                $query .= ' AND';
323
            }
324
325
            $operator = substr($value,0,2);
326
            $operator_value = substr($value,2);
327
            switch ($operator) {
328
                case '>=':
329
                    $query .= " `$name` >= ?";
330
                    $value = $operator_value;
331
                    break;
332
                case '<=':
333
                    $query .= " `$name` <= ?";
334
                    $value = $operator_value;
335
                    break;
336
                case '> ':
337
                    $query .= " `$name` > ?";
338
                    $value = $operator_value;
339
                    break;
340
                case '< ':
341
                    $query .= " `$name` < ?";
342
                    $value = $operator_value;
343
                    break;
344
                case '% ':
345
                    $query .= " `$name` like ?";
346
                    $value = $operator_value;
347
                    break;
348
                case '!=':
349
                    $query .= " `$name` != ?";
350
                    $value = $operator_value;
351
                    break;
352
                default:
353
                    $query .= " `$name` = ?";
354
                    break;
355
            }
356
357
            $values[] = $value;
358
        }
359
360
        return $values;
361
    }
362
363
    private static function groupByBuilder(string &$query, string|array $group_by = []): void {
364
        if (empty($group_by)) {
365
            return;
366
        }
367
        if (is_string($group_by)) {
0 ignored issues
show
introduced by
The condition is_string($group_by) is always false.
Loading history...
368
            $group_by = [$group_by];
369
        }
370
        $query .= ' GROUP BY `' . implode('`, `',$group_by) . '`';
371
    }
372
373
    private static function orderByBuilder(string &$query, string|array $order_by = []): void {
374
        if (empty($order_by)) {
375
            return;
376
        }
377
        if (is_string($order_by)) {
0 ignored issues
show
introduced by
The condition is_string($order_by) is always false.
Loading history...
378
            $order_by = [$order_by => 'ASC'];
379
        }
380
381
        $query .= ' ORDER BY `';
382
383
        $first = true;
384
        foreach ($order_by as $key => $mode) {
385
            if ($first) {
386
                $first = false;
387
            }
388
            else {
389
                $query .= ', ';
390
            }
391
            if (is_numeric($key)) {
392
                $key = $mode;
393
                $mode = 'ASC';
394
            }
395
            $query .= "$key` $mode";
396
        }
397
    }
398
399
    private static function countBuilder(string &$query, int $count = null, int $offset = null): void {
400
        if (!empty($count)) {
401
            $query .= !empty($offset) ? " LIMIT $offset,$count" : " LIMIT $count";
402
        }
403
        elseif (!empty($offset)) {
404
            $query .= " OFFSET $offset";
405
        }
406
    }
407
408
    private static function updateBuilder(string &$query, array $modify): array {
409
        $first = true;
410
        $values = [];
411
412
        foreach ($modify as $name => $value) {
413
            if ($first) {
414
                $first = false;
415
            }
416
            else {
417
                $query .= ' ,';
418
            }
419
420
            $operator = substr($value,0,2);
421
            $operator_value = substr($value,2);
422
            switch ($operator) {
423
                case '+=':
424
                    $query .= " `$name` = `$name` + ?";
425
                    $value = $operator_value;
426
                    break;
427
                case '-=':
428
                    $query .= " `$name` = `$name` - ?";
429
                    $value = $operator_value;
430
                    break;
431
                case '*=':
432
                    $query .= " `$name` = `$name` * ?";
433
                    $value = $operator_value;
434
                    break;
435
                case '/=':
436
                    $query .= " `$name` = `$name` / ?";
437
                    $value = $operator_value;
438
                    break;
439
                case '%=':
440
                    $query .= " `$name` = `$name` % ?";
441
                    $value = $operator_value;
442
                    break;
443
                default:
444
                    $query .= " `$name` = ?";
445
                    break;
446
            }
447
448
            $values[] = $value;
449
        }
450
451
        return $values;
452
    }
453
454
    private static function insertBuilder(string &$query, string|array $columns, array|string $values): array {
455
        $query .= '(`' . (is_string($columns) ? $columns : implode('`,`', $columns)) . '`) VALUES (';
0 ignored issues
show
introduced by
The condition is_string($columns) is always false.
Loading history...
456
        if (is_string($values)) $values = [$values];
0 ignored issues
show
introduced by
The condition is_string($values) is always false.
Loading history...
457
        $query .= '?' . str_repeat(',?', count($values) - 1) . ')';
458
        return $values;
459
    }
460
461
    private static function selectBuilder(string &$query, string|array $columns): void {
462
        if ($columns == '*') {
0 ignored issues
show
introduced by
The condition $columns == '*' is always false.
Loading history...
463
            $query .= " * ";
464
        }
465
        else {
466
            $query .= ' `' . (is_string($columns) ? $columns : implode('`,`', $columns)) . '` ';
0 ignored issues
show
introduced by
The condition is_string($columns) is always false.
Loading history...
467
        }
468
    }
469
470
    /**
471
     * Run delete query
472
     *
473
     * e.g. : `mysql::delete('users',['id'=>123456789],1);`
474
     *
475
     * @param string     $table  table name
476
     * @param array|null $where  Set your ifs default : null
477
     * @param int|null   $count  Set if you want to delete specific amount of row default : null
478
     * @param int|null   $offset Set if you want to delete rows after specific row default : null
479
     *
480
     * @return mysqli_result|bool
481
     */
482
    public static function delete (string $table, array $where = null, int $count = null, int $offset = null): bool {
0 ignored issues
show
Unused Code introduced by
The parameter $offset is not used and could be removed. ( Ignorable by Annotation )

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

482
    public static function delete (string $table, array $where = null, int $count = null, /** @scrutinizer ignore-unused */ int $offset = null): bool {

This check looks for parameters that have been defined for a function or method, but which are not used in the method body.

Loading history...
Unused Code introduced by
The parameter $count is not used and could be removed. ( Ignorable by Annotation )

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

482
    public static function delete (string $table, array $where = null, /** @scrutinizer ignore-unused */ int $count = null, int $offset = null): bool {

This check looks for parameters that have been defined for a function or method, but which are not used in the method body.

Loading history...
483
        $query = "DELETE FROM `$table`";
484
        $vars = self::whereBuilder($query, $where);
485
        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...
486
    }
487
488
    /**
489
     * Run update query
490
     *
491
     * e.g. : mysql::update('users',['step'=>'panel'],['id'=>123456789],1);
492
     *
493
     * @param string     $table  table name
494
     * @param array      $modify Set the data's you want to modify
495
     * @param array|null $where  Set your ifs default : null
496
     * @param int|null   $count  Set if you want to update specific amount of row default : null
497
     * @param int|null   $offset Set if you want to update rows after specific row default : null
498
     *
499
     * @return mysqli_result|bool
500
     */
501
    public static function update (string $table, array $modify, array $where = null, int $count = null, int $offset = null): bool {
502
        $query = "UPDATE `$table` SET";
503
        $modify_vars = self::updateBuilder($query, $modify);
504
        $where_vars = self::whereBuilder($query, $where);
505
        self::countBuilder($query, $count, $offset);
506
        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...
507
    }
508
509
    /**
510
     * Run insert query
511
     *
512
     * e.g. : `mysql::insert('users',['id','column1','column2','column3'],[123456789,'value1','value2','value3']);`
513
     *
514
     * @param string       $table   table name
515
     * @param string|array $columns sets columns that you want to fill
516
     * @param array|string $values  sets value that you want to set
517
     *
518
     * @return mysqli_result|bool
519
     */
520
    public static function insert (string $table, string|array $columns, array|string $values): bool {
521
        $query = "INSERT INTO `$table`";
522
        $values = self::insertBuilder($query, $columns, $values);
523
        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...
524
    }
525
526
    /**
527
     * Run select query
528
     *
529
     * e.g. : mysql::select('users','*',['id'=>123456789],1);
530
     *
531
     * e.g. : mysql::select('users',['step','referrals'],['id'=>123456789],1);
532
     *
533
     * @param string       $table   table name
534
     * @param array|string $columns sets column that you want to retrieve , set '*' to retrieve all , default : '*'
535
     * @param array|null   $where   Set your ifs default : null
536
     * @param int|null     $count   Set if you want to select specific amount of row default : null
537
     * @param int|null     $offset  Set if you want to select rows after specific row default : null
538
     * @param array|string $group_by group result based on these columns
539
     * @param array|string $order_by order result based on these columns
540
     *
541
     * @return mysqli_result|bool
542
     */
543
    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 = []): mysqli_result|bool {
544
        $query = "SELECT";
545
        self::selectBuilder($query, $columns);
546
        $query .= "FROM `$table`";
547
        $var = self::whereBuilder($query,$where);
548
        self::groupByBuilder($query, $group_by);
549
        self::orderByBuilder($query, $order_by);
550
        self::countBuilder($query,$count,$offset);
551
        return self::query($query, $var);
552
    }
553
554
    /**
555
     * Same as mysql::select but return first result as array
556
     *
557
     * mysql::selectArray('users','*',['id'=>123456789]);
558
     *
559
     * @param string       $table   table name
560
     * @param array|string $columns sets column that you want to retrieve , set '*' to retrieve all , default : '*'
561
     * @param array|null   $where   Set your ifs default : null
562
     *
563
     * @return null|bool|array
564
     */
565
    public static function selectArray (string $table, array|string $columns = '*', array $where = null): bool|array|null {
566
        $res = self::select($table, $columns, $where, 1);
567
        if ($res) {
568
            return $res->fetch_assoc();
569
        }
570
        return $res;
571
    }
572
573
    /**
574
     * Same as mysql::select but return first result as object(stdClass)
575
     *
576
     * mysql::selectObject('users','*',['id'=>123456789]);
577
     *
578
     * @param string       $table   table name
579
     * @param array|string $columns sets column that you want to retrieve , set '*' to retrieve all , default : '*'
580
     * @param array|null   $where   Set your ifs default : null
581
     */
582
    public static function selectObject (string $table, array|string $columns = '*', array $where = null) {
583
        $res = self::select($table, $columns, $where, 1);
584
        if ($res) {
585
            return $res->fetch_object();
586
        }
587
        return $res;
588
    }
589
590
    /**
591
     * Same as mysql::select but return each row as generator
592
     *
593
     * e.g. : mysql::selectEach('users','*',['id'=>123456789],1);
594
     * e.g. : mysql::selectEach('users',['id']);
595
     *
596
     * @param string       $table   table name
597
     * @param array|string $columns sets column that you want to retrieve , set '*' to retrieve all , default : '*'
598
     * @param array|null   $where   Set your ifs default : null
599
     * @param int|null     $count   Set if you want to select specific amount of row default : null
600
     * @param int|null     $offset  Set if you want to select rows after specific row default : null
601
     *
602
     * @return bool|Generator
603
     */
604
    public static function selectEach (string $table, array|string $columns = '*', array $where = null, int $count = null, int $offset = null): bool|Generator {
605
        $res = self::select($table, $columns, $where, $count, $offset);
606
        if ($res) {
607
            while ($row = $res->fetch_assoc()) yield $row;
608
        }
609
        else return $res;
610
    }
611
612
    /**
613
     * get backup from database, you can get full backup or specific table backup
614
     *
615
     * @param array|null $wanted_tables set if you want specific table which exist
616
     * @param bool       $table_data set false if you only want the creation queries(no data)
617
     * @param bool       $save set false if you want to receive sql as string
618
     * @param string     $file_name file name for saving
619
     *
620
     * @return string if save is true , return file name otherwise return sql data
621
     */
622
    public static function backup (array $wanted_tables = null, bool $table_data = true, bool $save = true, string $file_name = ''): string {
623
        self::setCharset('utf8mb4');
624
        $tables = array_column(self::query('SHOW TABLES')->fetch_all(),0);
625
        if (!empty($wanted_tables)) {
626
            $tables = array_intersect($tables, $wanted_tables);
627
        }
628
        $sql = '';
629
        foreach ($tables as $table) {
630
            $sql .= self::query("SHOW CREATE TABLE `$table`")->fetch_row()[1] . ";\n\n";
631
632
            if ($table_data) {
633
                $total_rows = self::query("SELECT COUNT(*) as `cnt` FROM `$table`")->fetch_object()->cnt;
634
                for ($i = 0; $i < $total_rows; $i = $i + 1000) {
635
                    $sql .= "INSERT INTO " . $table . " VALUES";
636
                    $result = self::select($table, '*' , null, 1000, $i);
637
                    $field_count = $result->field_count;
638
                    $affected_rows = self::affected_rows();
639
                    $counter = 1;
640
                    while ($row = $result->fetch_row()) {
641
                        $sql .= "\n(";
642
                        for ($column = 0; $column < $field_count; $column++) {
643
                            $row[$column] = str_replace("\n", "\\n", addslashes($row[$column]));
644
                            $sql .= !empty($row[$column]) ? '"' . $row[$column] . '"' : '""';
645
                            if ($column < $field_count - 1) {
646
                                $sql .= ',';
647
                            }
648
                        }
649
                        $sql .= ')' . ($counter == $affected_rows ? ';' : ',');
650
                        $counter++;
651
                    }
652
                }
653
                if ($total_rows > 0) {
654
                    $sql .= "\n\n";
655
                }
656
            }
657
            $sql .= "\n";
658
        }
659
        if (!$save) {
660
            return $sql;
661
        }
662
        if (empty($file_name)) {
663
            $file_name = self::query('SELECT database() as `db_name`')->fetch_object()->db_name . time() . '.sql';
664
        }
665
        file_put_contents($file_name, $sql);
666
        return $file_name;
667
    }
668
}