Query Builder

public static function table(string $table): \Illuminate\Database\Query\Builder

// Простое использование
$builder = SqlConnection::table('users');

// С условиями
$users = SqlConnection::table('users')
    ->where('active', 1)
    ->get();

// Выборка полей
$user = SqlConnection::table('users')
    ->select('id', 'name', 'email')
    ->where('id', 1)
    ->first();

// Пагинация
$pages = SqlConnection::table('posts')
    ->where('status', 'published')
    ->orderBy('created_at', 'desc')
    ->paginate(25);

public static function connection(string $name = null): \Illuminate\Database\Connection

// Получить подключение по умолчанию
$conn = SqlConnection::connection();

// Получить именованное подключение
$conn = SqlConnection::connection('mysql');

// Использовать Schema Builder через connection
SqlConnection::connection()->getSchemaBuilder()
    ->table('users', function (\Illuminate\Database\Schema\Blueprint $table): void {
        $table->index('email');
    });

// Получить PDO через connection
$pdo = SqlConnection::connection()->getPdo();

public static function create_conn(
    string $server,
    string $user,
    string $pass,
    string $db = '',
    string $driver = 'mysql'
): self

// MySQL подключение
$db = SqlConnection::create_conn('localhost', 'root', 'password', 'mydb');
$db->tryconnect();

// PostgreSQL подключение
$db = SqlConnection::create_conn('localhost', 'postgres', 'secret', 'mydb', 'pgsql');
$db->tryconnect();

// Повторный вызов — вернёт тот же экземпляр
$db1 = SqlConnection::create_conn('localhost', 'root', 'pass', 'mydb');
$db2 = SqlConnection::create_conn('localhost', 'root', 'pass', 'mydb');
// $db1 === $db2

public static function escape(string $msg): string

// Экранирование одинарной кавычки
$safe = SqlConnection::escape("O'Brien");
// Результат: O\'Brien

// Экранирование спецсимволов
$safe = SqlConnection::escape("Строка\nс переносом");
// Результат: Строка\nс переносом

// Использование в raw-запросе
$name = SqlConnection::escape($userName);
$db->query("INSERT INTO users (name) VALUES ('$name')");

public static function escape2(string $field): string

// Простое имя
$escaped = SqlConnection::escape2('users');
// Результат: `users`

// Составное имя (таблица.поле)
$escaped = SqlConnection::escape2('users.name');
// Результат: `users`.`name`

// Префикс # — отключает экранирование
$escaped = SqlConnection::escape2('#COUNT(*)');
// Результат: COUNT(*)

// Префикс * — возвращает как есть
$escaped = SqlConnection::escape2('*ALL');
// Результат: *ALL

public static function getWhereFromFilter(?array $filter = null): string

// Простой фильтр
$where = SqlConnection::getWhereFromFilter(['status' => 1]);
// Результат: `status` = '1'

// Поиск подстроки
$where = SqlConnection::getWhereFromFilter(['%name' => 'Иван']);
// Результат: `name` like '%Иван%'

// NULL-проверка
$where = SqlConnection::getWhereFromFilter(['~deleted_at' => null]);
// Результат: `deleted_at` is null

// Логический OR
$where = SqlConnection::getWhereFromFilter([
    '$or' => [
        ['role' => 'admin'],
        ['role' => 'moderator'],
    ],
]);
// Результат:  ( `role` = 'admin' or `role` = 'moderator' )

// Сложный фильтр
$where = SqlConnection::getWhereFromFilter([
    'status'   => 1,
    '%name'    => 'Иван',
    '!~deleted_at' => null,
]);
// Результат: `status` = '1' and `name` like '%Иван%' and `deleted_at` is not null

// Использование в raw-запросе
$where = SqlConnection::getWhereFromFilter(['active' => 1, '>age' => 18]);
$rows = $db->fetchAll($db->query("SELECT * FROM users WHERE $where"));

public static function applyFilterToQuery(
    \Illuminate\Database\Query\Builder $query,
    ?array $filter
): \Illuminate\Database\Query\Builder

// Простое использование
$query = SqlConnection::table('users');
$query = SqlConnection::applyFilterToQuery($query, [
    'status' => 1,
    '%name'  => 'Иван',
]);
$users = $query->get();

// С логическим OR
$query = SqlConnection::table('users');
$query = SqlConnection::applyFilterToQuery($query, [
    'status' => 1,
    '$or' => [
        ['role' => 'admin'],
        ['role' => 'manager'],
    ],
]);
$users = $query->get();

// В цепочке с другими методами
$users = SqlConnection::table('users')
    ->select('id', 'name', 'email')
    ->orderBy('name', 'asc');
$users = SqlConnection::applyFilterToQuery($users, $filter);
$results = $users->paginate(25);

public static function __callStatic(string $method, array $args)

// Очистка кэша
SqlConnection::purge();

// Вызов любого метода Capsule
SqlConnection::beginTransaction();
SqlConnection::rollBack();

public function __construct(string $driver = 'mysql')

// MySQL
$db = new SqlConnection('mysql');

// PostgreSQL
$db = new SqlConnection('pgsql');

// По умолчанию (MySQL)
$db = new SqlConnection();

public function tryconnect(): int

$db = new SqlConnection('mysql');
$db->server = 'localhost';
$db->user = 'root';
$db->pass = 'password';
$db->db = 'mydb';

if ($db->tryconnect() == 1) {
    echo "Подключение установлено";
} else {
    echo "Ошибка подключения";
}

public function connect(
    string $server,
    string $user,
    string $pass,
    ?string $db = null
): int

$db = new SqlConnection('mysql');
$result = $db->connect('localhost', 'root', 'password', 'mydb');

if ($result == 1) {
    $db->query("SELECT * FROM users");
}

public function is_valid(): bool

if ($db->is_valid()) {
    $db->query("SELECT 1");
} else {
    $db->tryconnect();
}

public function ping(): bool

if (!$db->ping()) {
    $db->tryconnect();
}

public function usedb(string $dbname): void

$db->usedb('another_database');

public function connected(): bool

if ($db->connected()) {
    echo "Подключение ранее установлено";
}

public function query(string $query = '', ?array $arr = null, bool $save_log = true)

// Простой запрос
$r = $db->query("SELECT * FROM users");

// С подстановкой параметров
$r = $db->query(
    "SELECT * FROM users WHERE name = :name AND status = :status",
    [':name' => 'Ivan', ':status' => 1]
);

// Без логирования
$r = $db->query("SELECT 1", null, false);

// INSERT
$db->query("INSERT INTO logs (msg, level) VALUES ('test', 'info')");

public function query_params(string $query = '', ?array $arr = null, bool $save_log = true)

$r = $db->query_params(
    "SELECT * FROM users WHERE login = :login",
    [':login' => 'admin']
);

public function free_result(?\PDOStatement $result): bool

$r = $db->query("SELECT * FROM users");
// ... обработка
$db->free_result($r);

public function fetch(?\PDOStatement $result)

$r = $db->query("SELECT * FROM users");
while ($row = $db->fetch($r)) {
    echo $row['name'] . "\n";
}
$db->free_result($r);

public function fetch_assoc(?\PDOStatement $result)

$r = $db->query("SELECT * FROM users WHERE id = 1");
$row = $db->fetch_assoc($r);
echo $row['name'];

public function fetchAll(?\PDOStatement $result)

$r = $db->query("SELECT * FROM users WHERE active = 1");
$rows = $db->fetchAll($r);
foreach ($rows as $row) {
    echo $row['name'] . "\n";
}
$db->free_result($r);

public function error(): string

$r = $db->query("SELECT * FROM non_existing_table");
if ($db->error() != '00000') {
    echo "Ошибка: " . $db->errorstr();
}

public function errorstr(): string

$r = $db->query("INVALID SQL");
if ($db->error() != '00000') {
    echo "Ошибка: " . $db->errorstr();
}

public function num_rows(?\PDOStatement $res): int

$r = $db->query("SELECT * FROM users WHERE active = 1");
echo "Найдено: " . $db->num_rows($r);

public function last_insert_id()

$db->insert('users', ['name' => 'Ivan', 'email' => 'ivan@example.com']);
$id = $db->last_insert_id();
echo "Создан пользователь с ID: $id";

public function get_var(string $field, string $q, array $arr = [])

$name = $db->get_var('name', "SELECT name FROM users WHERE id = 1");

$count = $db->get_var('cnt', "SELECT COUNT(*) as cnt FROM users WHERE active = 1");

// С параметрами
$email = $db->get_var('email', "SELECT email FROM users WHERE login = ?", ['admin']);

public function get_count(string $q, string $field = 'count')

$count = $db->get_count("SELECT COUNT(*) as count FROM users WHERE active = 1");

// С произвольным именем поля
$total = $db->get_count("SELECT COUNT(*) as total FROM orders", 'total');

public function affected_rows()

$db->update('users', ['pkid' => 1], ['active' => 0]);
echo "Затронуто строк: " . $db->affected_rows();

public function found_rows()

$db->query("SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT 10");
$count = $db->found_rows();
echo "Всего записей: $count";

public function insert(string $table, array $data, ?array $data2 = null): int

// Простая вставка
$db->insert('users', [
    'name'  => 'Ivan',
    'email' => 'ivan@example.com',
    'role'  => 'user',
]);

// Вставка с null-значениями
$db->insert('users', [
    'name'    => 'Ivan',
    'email'   => null,
    'phone'   => null,
]);

// Проверка результата
$result = $db->insert('users', ['name' => 'Ivan']);
if ($result == 1) {
    $id = $db->last_insert_id();
}

public function update(string $table, array $filter, array $data, ?array $data2 = null): int

// Обновление одной записи по ID
$db->update('users', ['pkid' => 1], ['name' => 'Ivan Updated']);

// Обновление с составным фильтром
$db->update('users', ['status' => 1, 'role' => 'user'], ['role' => 'moderator']);

// Обновление с IO-операторами
$db->update('orders', ['status' => 'pending', '<created_at' => '2024-01-01'], ['status' => 'expired']);

// Обновление нескольких записей
$db->update('users', ['role' => 'user'], ['active' => 1]);

public function delete(string $table, array $filter, ?int $count = null): int

// Удаление одной записи
$db->delete('users', ['pkid' => 1]);

// Удаление по фильтру
$db->delete('users', ['status' => 'banned']);

// Удаление с лимитом
$db->delete('logs', ['level' => 'debug'], 100);

// Удаление с IO-операторами
$db->delete('sessions', ['<expires_at' => date('Y-m-d H:i:s')]);

// Удаление soft-deleted записей
$db->delete('users', ['!~deleted_at' => null]);

public function insert_or_update(string $table, array $data, ?array $data2 = null): int

// Upsert по primary key
$db->insert_or_update('users', [
    'pkid' => 1,
    'name' => 'Ivan Updated',
    'email' => 'ivan@example.com',
]);

// Upsert по уникальному полю
$db->insert_or_update('settings', [
    'key'   => 'site_name',
    'value' => 'Новый сайт',
]);

public function insert_or_ignore(string $table, array $data, ?array $data2 = null): int

// Игнорирование дубликата
$db->insert_or_ignore('users', [
    'login' => 'admin',
    'name'  => 'Администратор',
]);

// Безопасная вставка в цикле
$tags = ['php', 'mysql', 'laravel'];
foreach ($tags as $tag) {
    $db->insert_or_ignore('tags', ['name' => $tag]);
}

public function search(string $table, array $params): array

// Простой поиск
$result = $db->search('users', [
    'filter' => ['status' => 1],
]);
foreach ($result['res'] as $user) {
    echo $user['name'] . "\n";
}
echo "Всего: {$result['count']}\n";

// Поиск с фильтрами, сортировкой и лимитом
$result = $db->search('users', [
    'filter' => [
        'status'    => 1,
        '%name'     => 'Иван',
        '>age'      => 18,
        '$or' => [
            ['role' => 'admin'],
            ['role' => 'manager'],
        ],
    ],
    'fields'  => ['id', 'name', 'email', 'role'],
    'order'   => ['name' => 'asc'],
    'limit'   => 25,
    'start'   => 0,
]);

// С JOIN
$result = $db->search('orders', [
    'filter' => ['status' => 'pending'],
    'fields' => ['orders.*', 'users.name as user_name'],
    'join'   => 'JOIN users ON users.id = orders.user_id',
    'order'  => ['orders.created_at' => 'desc'],
    'limit'  => 50,
]);

// С GROUP BY
$result = $db->search('orders', [
    'fields'  => ['user_id', '#COUNT(*) as order_count'],
    'groupby' => ['user_id'],
    'filter'  => ['>order_count' => 5],
]);

// DISTINCT
$result = $db->search('users', [
    'fields'   => ['role'],
    'distinct' => true,
]);

public function dbsearch2(string $table, array $params): array

$result = $db->dbsearch2('users', [
    'filter' => ['status' => 1],
    'order'  => ['name' => 'asc'],
]);

public function sync_items(string $table, array $params): void

$db->sync_items('categories', [
    'new_items' => $importedCategories,
    'filter'    => ['parent_id' => 0],
    'find'      => function(array $old, array $new): bool {
        return $old['code'] === $new['code'];
    },
    'create' => function(array $new) use ($db): void {
        $db->insert('categories', $new);
    },
    'update' => function(array $old, array $new) use ($db): void {
        $db->update('categories', ['pkid' => $old['pkid']], $new);
    },
    'delete' => function(array $old) use ($db): void {
        $db->delete('categories', ['pkid' => $old['pkid']]);
    },
]);

public function beginTransaction(): bool

public function commit(): bool

public function rollBack(): bool

$db->beginTransaction();
try {
    $db->update('accounts', ['pkid' => 1], ['balance' => '-100']);
    $db->update('accounts', ['pkid' => 2], ['balance' => '+100']);
    $db->commit();
} catch (\Exception $e) {
    $db->rollBack();
    throw $e;
}

// Или через Laravel Query Builder
\DB::beginTransaction();
try {
    SqlConnection::table('accounts')
        ->where('pkid', 1)
        ->decrement('balance', 100);
    SqlConnection::table('accounts')
        ->where('pkid', 2)
        ->increment('balance', 100);
    \DB::commit();
} catch (\Exception $e) {
    \DB::rollBack();
    throw $e;
}

public function information_schema_load_current_db(): void

$db->information_schema_load_current_db();
print_r($db->columns);

// where
$users = SqlConnection::table('users')
    ->where('active', 1)
    ->where('age', '>=', 18)
    ->get();

// orWhere
$admins = SqlConnection::table('users')
    ->where('role', 'admin')
    ->orWhere('role', 'superadmin')
    ->get();

// join
$orders = SqlConnection::table('orders')
    ->join('users', 'users.id', '=', 'orders.user_id')
    ->select('orders.*', 'users.name')
    ->get();

// groupBy + having
$stats = SqlConnection::table('orders')
    ->select('user_id', \DB::raw('COUNT(*) as cnt'))
    ->groupBy('user_id')
    ->having('cnt', '>=', 5)
    ->get();

// paginate
$users = SqlConnection::table('users')
    ->where('active', 1)
    ->orderBy('name')
    ->paginate(25);

// aggregate
$count = SqlConnection::table('users')->where('active', 1)->count();
$total = SqlConnection::table('orders')->sum('total');

$filter = [
    'status' => 1,                    // WHERE status = 1
    'role'   => 'admin',              // WHERE role = 'admin'
    'active' => true,                 // WHERE active = 1
];

$filter = [
    '%name'  => 'Иван',              // WHERE name LIKE '%Иван%'
    '%email' => '@example.com',       // WHERE email LIKE '%@example.com%'
];

$filter = [
    '>age'      => 18,                // WHERE age > 18
    '<=price'   => 1000,              // WHERE price <= 1000
    '>=rating'  => 4.5,              // WHERE rating >= 4.5
    '!status'   => 'banned',          // WHERE status != 'banned'
    '!=role'    => 'deleted',         // WHERE role != 'deleted'
];

$filter = [
    '~deleted_at'  => null,           // WHERE deleted_at IS NULL
    '!~deleted_at' => null,           // WHERE deleted_at IS NOT NULL
];

$filter = [
    'role' => ['admin', 'moderator'], // WHERE role IN ('admin', 'moderator')
];

$filter = [
    '!role' => ['banned', 'deleted'], // WHERE role NOT IN ('banned', 'deleted')
];

$filter = [
    '$or' => [
        ['type' => 'premium'],
        ['status' => 'active'],
    ],
];
// WHERE (type = 'premium') OR (status = 'active')

$filter = [
    '$and' => [
        ['status' => 1],
        ['>age' => 18],
    ],
];
// WHERE (status = 1) AND (age > 18)

$filter = [
    'status'   => 1,
    '%name'    => 'Иван',
    '$or'      => [
        ['role' => 'admin'],
        ['role' => 'manager'],
    ],
    '!~deleted_at' => null,
];
// WHERE status = 1
//   AND name LIKE '%Иван%'
//   AND (role = 'admin' OR role = 'manager')
//   AND deleted_at IS NOT NULL

$filter = [
    'tags' => [],                     // WHERE 1 = 0 (ничего не вернёт)
];

$filter = [
    '%tags' => ['php', 'mysql'],      // WHERE tags LIKE '%php%' AND tags LIKE '%mysql%'
];

$result = $db->search('users', [
    'filter' => [
        'status'    => 1,
        '%name'     => 'Иван',
        '>age'      => 18,
        '$or' => [
            ['role' => 'admin'],
            ['role' => 'manager'],
        ],
    ],
    'fields'  => ['id', 'name', 'email', 'role'],
    'order'   => ['name' => 'asc'],
    'limit'   => 25,
    'start'   => 0,
]);

foreach ($result['res'] as $user) {
    echo "{$user['name']} ({$user['role']})\n";
}
echo "Всего: {$result['count']}\n";

$result = $db->search('orders', [
    'fields' => [
        'orders.id',
        'users.name as user_name',
        'products.name as product_name',
        'orders.total',
    ],
    'join'  => [
        'JOIN users ON users.id = orders.user_id',
        'JOIN products ON products.id = orders.product_id',
    ],
    'filter' => ['orders.status' => 'pending'],
    'order'  => ['orders.created_at' => 'desc'],
    'limit'  => 50,
]);

$orders = SqlConnection::table('orders')
    ->join('users', 'users.id', '=', 'orders.user_id')
    ->join('products', 'products.id', '=', 'orders.product_id')
    ->select(
        'orders.id',
        'users.name as user_name',
        'products.name as product_name',
        'orders.total'
    )
    ->where('orders.status', 'pending')
    ->orderBy('orders.created_at', 'desc')
    ->limit(50)
    ->get();

$stats = SqlConnection::table('orders')
    ->select(
        'user_id',
        \DB::raw('COUNT(*) as order_count'),
        \DB::raw('SUM(total) as total_spent')
    )
    ->groupBy('user_id')
    ->having('order_count', '>=', 5)
    ->orderBy('total_spent', 'desc')
    ->get();

$page = request('page', 1);
$perPage = 25;

$users = SqlConnection::table('users')
    ->where('active', 1)
    ->orderBy('name')
    ->paginate($perPage);

// В шаблоне
echo $users->links();

$count = SqlConnection::table('users')->where('active', 1)->count();
$total = SqlConnection::table('orders')->where('status', 'completed')->sum('total');
$avg = SqlConnection::table('orders')->avg('total');
$min = SqlConnection::table('products')->min('price');
$max = SqlConnection::table('products')->max('price');

$activeUserIds = SqlConnection::table('users')
    ->where('active', 1)
    ->pluck('id');

$orders = SqlConnection::table('orders')
    ->whereIn('user_id', $activeUserIds)
    ->get();

$admins = SqlConnection::table('users')->where('role', 'admin');
$managers = SqlConnection::table('users')->where('role', 'manager');
$all = $admins->union($managers)->get();

// selectRaw
$results = SqlConnection::table('orders')
    ->selectRaw('DATE(created_at) as date, COUNT(*) as count')
    ->groupByRaw('DATE(created_at)')
    ->get();

// whereRaw
$results = SqlConnection::table('users')
    ->whereRaw('YEAR(created_at) = ?', [2024])
    ->get();

// havingRaw
$results = SqlConnection::table('orders')
    ->select('user_id', \DB::raw('COUNT(*) as cnt'))
    ->groupBy('user_id')
    ->havingRaw('COUNT(*) > ?', [5])
    ->get();

// SELECT
$r = $db->query("SELECT * FROM users WHERE active = 1");
$rows = $db->fetchAll($r);
$db->free_result($r);

// INSERT
$db->query("INSERT INTO logs (msg, level) VALUES ('test', 'info')");

// UPDATE
$db->query("UPDATE users SET active = 0 WHERE id = 1");

// DELETE
$db->query("DELETE FROM logs WHERE created_at < '2024-01-01'");

// Через raw PDO
$db->beginTransaction();
try {
    $db->update('accounts', ['pkid' => 1], ['balance' => '-100']);
    $db->update('accounts', ['pkid' => 2], ['balance' => '+100']);
    $db->commit();
} catch (\Exception $e) {
    $db->rollBack();
    throw $e;
}

// Через Laravel Query Builder
\DB::beginTransaction();
try {
    SqlConnection::table('accounts')
        ->where('pkid', 1)
        ->decrement('balance', 100);
    SqlConnection::table('accounts')
        ->where('pkid', 2)
        ->increment('balance', 100);
    \DB::commit();
} catch (\Exception $e) {
    \DB::rollBack();
    throw $e;
}

$where = SqlConnection::getWhereFromFilter([
    'status' => 1,
    '%name'  => 'Иван',
    '$or' => [
        ['role' => 'admin'],
        ['role' => 'manager'],
    ],
]);

$r = $db->query("SELECT * FROM users WHERE $where");
$rows = $db->fetchAll($r);

$filter = [
    'status'   => 1,
    '%name'    => 'Иван',
    '!~deleted_at' => null,
];

$users = SqlConnection::table('users')
    ->select('id', 'name', 'email')
    ->orderBy('name');

$users = SqlConnection::applyFilterToQuery($users, $filter);
$results = $users->paginate(25);

// IS NULL
->whereNull('deleted_at')

// IS NOT NULL
->whereNotNull('email')

// Через PDO
$r = $db->query("SELECT * FROM users WHERE id = 1");
$row = $db->fetch_assoc($r);

// Через query_params
$r = $db->query_params(
    "SELECT * FROM users WHERE login = :login",
    [':login' => 'admin']
);

SqlConnection::connection()->getSchemaBuilder()
    ->table('users', function (\Illuminate\Database\Schema\Blueprint $table): void {
        $table->index('email');
        $table->unique('login');
    });

use Illuminate\Support\Facades\DB;

SqlConnection::table('orders')
    ->select(DB::raw('SUM(total) as sum_total'))
    ->groupBy('status')
    ->get();

$r = $db->query_params(
    "SELECT * FROM users WHERE name = :name AND age > :age",
    [':name' => $name, ':age' => $age]
);

$safeName = SqlConnection::escape($name);
$r = $db->query("SELECT * FROM users WHERE name = '$safeName'");