PHP db mysql

db.php

<?php

use PDO;
use PDOStatement;

/**
 * Class Db
 * Small helper for working with native PDO
 *
 * $pdo = new Db(new \PDO(
 *     "mysql:dbname=dbname;host=localhost",
 *     'root',
 *     'password',
 *     [PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\'']
 * ));
 *
 * $user = $db->select('SELECT * FROM users WHERE id = ?', 13);
 * $users = $db->select('SELECT * FROM users WHERE id IN (?,?,?)', [13, 14, 15]);
 *
 * @package Plariumed\Utils
 */
class Db
{
    /**
     * @var PDO
     */
    private $pdo;

    /**
     * Db constructor.
     * @param PDO $pdo
     */
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    public function getConnection(): PDO
    {
        return $this->pdo;
    }

    /**
     * @param string $sql
     * @param array|mixed $params
     * @return array
     */
    public function select(string $sql, $params = []): array
    {
        return $this->execute($sql, $params)->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * @param string $sql
     * @param array|mixed $params
     * @return array
     */
    public function selectColumn(string $sql, $params = []): array
    {
        return $this->execute($sql, $params)->fetchAll(PDO::FETCH_COLUMN);
    }

    /**
     * @param string $sql
     * @param array|mixed $params
     * @return PDOStatement
     */
    private function execute(string $sql, $params = []): PDOStatement
    {
        if (!is_array($params)) {
            $params = [$params];
        }

        $statement = $this->pdo->prepare($sql);

        if ($this->isList($params)) {
            $statement->execute($params);
        } else {
            foreach ($params as $key => $param) {
                if (is_integer($param)) {
                    $statement->bindValue($key, $param, PDO::PARAM_INT);
                } else {
                    $statement->bindValue($key, $param);
                }
            }

            $statement->execute();
        }

        return $statement;
    }

    /**
     * Return single row
     * @param string $sql
     * @param array $params
     * @return array|bool
     */
    public function selectRow($sql, $params = [])
    {
        $row = $this->select($sql, $params);

        return $row[0] ?? false;
    }

    /**
     * Return one cell
     * @param string $sql
     * @param array $params
     * @return string|int|bool
     */
    public function selectCell($sql, $params = [])
    {
        $row = $this->selectRow($sql, $params);

        return (is_array($row) ? array_pop($row) : false);
    }

    /**
     * @param string $index
     * @param string $sql
     * @param array $params
     * @return array
     */
    public function selectWithKey($index, $sql, $params = [])
    {
        $select = $this->select($sql, $params);

        $result = [];
        foreach ($select as $row) {
            $result[$row[$index]] = $row;
        }

        return $result;
    }

    /**
     * @param string $sql
     * @param array $params
     * @return mixed
     */
    public function query($sql, $params = null)
    {
        if (!is_array($params) && $params !== null) {
            $params = [$params];
        }

        return $this->pdo->prepare($sql)->execute($params);
    }

    /**
     * $db->insert('table', ['field' => 'value']);
     *
     * @param string $table
     * @param array $params
     * @return PDOStatement
     */
    public function insert(string $table, array $params): PDOStatement
    {
        $columns = implode(', ', array_map(function ($column) {
            return "`$column`";
        }, array_keys($params)));

        $placeholders = implode(', ', array_fill(0, count($params), '?'));

        return $this->execute("INSERT INTO $table ($columns) values ($placeholders)", array_values($params));
    }

    /**
     * $db->update('table', ['field' => 'value'], ['id' => 1]);
     *
     * @param string $table
     * @param array $params
     * @param array $where
     * @return PDOStatement
     */
    public function update(string $table, array $params, array $where): PDOStatement
    {
        $sqlWhere = function ($params, $delimiter) {
            $sqlParams = [];
            foreach ($params as $name => $value) {
                $sqlParams[] = "`$name` = ?";
            }

            return implode($delimiter, $sqlParams);
        };

        $sqlParams = $sqlWhere($params, ', ');
        $whereParams = $sqlWhere($where, ' AND ');

        $sql = "UPDATE {$table} SET {$sqlParams} WHERE {$whereParams}";

        $values = array_merge(array_values($params), array_values($where));

        return $this->execute($sql, $values);
    }

    private function isList(array $array): bool
    {
        return array_values($array) === $array;
    }
}