§ Программный код

class db {

    /** @var mysqli $db */
    protected $db;

    protected $host;
    protected $user;
    protected $pass;
    protected $name;
    protected $port;
    protected $codepage;

    /**
     * db constructor.
     * @param $connection @desc "-h <host> -u <user> -p<pass> <database>"
     * @param $codepage   @desc "utf8 | utf8mb4"
     */
    public function __construct($connection, $codepage = 'utf8') {

        if (preg_match('~\-h\s+([^\s+]+)\s+\-u\s+([^\s+]+)\s+\-p([^\s+]*)\s+([^\s+]+)~', $connection, $c)) {

            $this->port = null;
            list(, $this->host, $this->user, $this->pass, $this->name) = $c;
        }
        // SphinxConnect: 127.0.0.1:9
        else if (preg_match('~^([0-9\.]+):(\d+)$~', $connection, $c)) {
            $this->host = $c[1];
            $this->user = '';
            $this->pass = '';
            $this->name = '';
            $this->port = $c[2];
        }
        else {
            die('['.date('Y-m-d H:i:s')." MYSQL: Can't connect via string]\n");
        }

        $this->codepage = $codepage;
    }

    // Подключиться к БД
    public function connect() {

        if (empty($this->db)) {

            $this->db = new mysqli($this->host, $this->user, $this->pass, $this->name, $this->port);
            if ($this->db->connect_error) {
                die('['.date('Y-m-d H:i:s')." Invalid credentials: $this->host, $this->user, $this->name, $this->port]\n");
            }

            $this->db->query("set names $this->codepage");
        }
    }

    // Отключить БД
    public function close() {

        if ($this->db) {
            $this->db->close();
            $this->db = null;
        }
    }

    // Выполнить запрос $sql
    public function query($sql) {

        $this->connect();
        $q = $this->db->query($sql);

        if ($this->db->errno) {
            throw new Exception("MYSQL ERROR: " . $this->db->error . " [$sql]");
        }

        return $q;
    }

    /** @return mixed */
    public function affected_rows() {
        return $this->db->affected_rows;
    }

    /** @return mysqli */
    public function db() {
        return $this->db;
    }

    // Обезопасить массив входящих данных
    public function escape_array(array $data) {

        foreach ($data as $id => $value) {

            if (is_null($value)) $data[ $id ] = 'NULL';
            else if (is_integer($value)) $data[ $id ] = (int) $value;
            else if (is_float($value)) $data[ $id ] = (float) $value;
            else if (is_double($value)) $data[ $id ] = (double) $value;
            else if (is_object($value) || is_array($value)) $data[ $id ] = json_encode($value);
            else $data[ $id ] = "'".$this->esc($value)."'";
        }

        return $data;
    }

    // Вставить новую строку $data в $table
    public function insert($table, array $data, $ignore = 'ignore') {

        $data = $this->escape_array($data);

        $sql = "INSERT $ignore INTO `$table` (`".join("`, `", array_keys($data))."`) VALUES (".join(", ", array_values($data)).")";
        $this->query($sql);

        return (int) $this->db->insert_id;
    }

    // Обновить данные $data в $where
    public function update($table, array $data, array $where) {

        $fields = [];
        $clause = [];

        foreach ($data as $id => $value) {
            $fields[] = "`$id` = ".(is_null($value) ? 'NULL' : "'".$this->esc($value)."'");
        }

        foreach ($where as $id => $value) {
            $clause[] = "`$id` = ".(is_null($value) ? 'NULL' : "'".$this->esc($value)."'");
        }

        $fields = join(', ', $fields);
        $clause = join(' AND ', $clause);

        $sql = "update `$table` set $fields where $clause";
        $this->query($sql);
    }

    // Запрос на выбор строк по условию $where из таблицы $table
    public function select($table, array $where = [], $usefield = '*') {

        $fields = [];
        foreach ($where as $id => $value) {
            $fields[] = "$id = '".$this->esc($value)."'";
        }

        $sql = "select $usefield from `$table` where 1";

        if ($fields) {
            $sql .= " AND " . join(" AND ", $fields);
        }

        return $this->query($sql);
    }

    // Вставка или обновление $ins по ключу $where в таблицу $table
    public function insupdate($table, array $ins, array $where) {

        if ($this->select($table, $where)->fetch_assoc()) {
            $this->update($table, $ins, $where);
            return 0;
        } else {

            foreach ($where as $n => $v) $ins[$n] = $v;
            return (int) $this->insert($table, $ins);
        }
    }

    // Обезопасить строку
    public function esc($s) {
        return $this->db->real_escape_string($s);
    }

    // Получение выдачи
    public function get_rows($sql, $by_field = '') {

        $out = [];
        foreach ($this->query($sql) as $row) {

            if ($by_field) {
                $out[] = $row[$by_field];
            } else {
                $out[] = $row;
            }
        }

        return $out;
    }
}
29 ноя, 2020
© 2007-2022 Психическая чашка мешала