<?php

class MySQL {
    # link
    private $link;
    # connection info
    private $hostname, $port, $user, $password, $db_name, $table_prefix, $charset, $collation;
    # query results
    private $affected_rows, $num_rows, $insert_id;
    # query log
    private $count_queries = 0;
    private $queries = array();

    /**
     * @param $hostname
     * @param $port
     * @param $user
     * @param $password
     * @param $db_name
     * @param $table_prefix
     * @param $charset
     * @param $collation
     */
    public function __construct($hostname, $port, $user, $password, $db_name, $table_prefix, $charset, $collation) {
        $this->hostname = $hostname;
        $this->port = $port;
        $this->user = $user;
        $this->password = $password;
        $this->db_name = $db_name;
        $this->table_prefix = $table_prefix;
        $this->charset = $charset;
        $this->collation = $collation;
    }

    /**
     * Destructor
     */
    function __destruct() {
        (isset($this->link)) ? @mysql_close($this->link) : false;
    }

    /**
     * Open database connection
     * @return  bool
     */
    public function connect() {
        if (empty($this->hostname)) {
            return false;
        }

        $this->link = @mysql_connect($this->hostname . ':' . $this->port, $this->user, $this->password);

        if (!$this->link) {
            $this->_error('Connection error: ' . mysql_error());
        }

        if (@mysql_select_db($this->db_name, $this->link) === FALSE) {
            $this->_error('Database selection error: ' . mysql_error($this->link));
        }


        $this->query('SET NAMES \'' . $this->charset . '\' COLLATE \'' . $this->collation . '\' ;');

        return true;
    }

    /**
     * Exec database query
     *
     * @param string query string
     * @return  mixed
     */
    function query($sql) {
        # Benchmark start
        $query_time = microtime(TRUE);


        $return = TRUE;
        if (($result = @mysql_query($sql, $this->link)) === FALSE) {
            $this->_error('Query error - ' . mysql_error($this->link) . ' (' . $sql . ')');
        }
        if (preg_match("/^\\s*(insert|delete|update|replace|alter|set) /i", $sql)) {
            $this->affected_rows = @mysql_affected_rows($this->link);

            if (preg_match("/^\\s*(insert|replace) /i", $sql)) {
                $this->insert_id = @mysql_insert_id($this->link);
            }
        } else {
            $this->num_rows = mysql_num_rows($result);
            $return = array();

            if ($this->num_rows != 0) {
                while ($row = @mysql_fetch_assoc($result)) {
                    $return[] = $row;
                }
            }
            @mysql_free_result($result);
        }

        # Query logging
        $this->count_queries++;
        $this->queries[] = array('sql' => $sql, 'result' => $return, # benchmark end
            'time' => number_format((microtime(true) - $query_time), 6) . 'sec');

        return $return;
    }

    /**
     * Count values
     * @param string table name
     * @param array Associative array row=>value
     * @return int
     */
    public function count($table, $where = array()) {
        $table = $this->prefix() . $table;

        $query = 'SELECT COUNT(*) FROM `' . $table . '` ';
        if (!empty($where)) {
            $query .= 'WHERE ' . $this->prepare_where($where);
        }

        $result = mysql_query($query, $this->link);
        return mysql_result($result, 0);
    }

    /**
     * Select record from table (simple WHERE)
     * @param $table
     * @param array $where
     * @param bool $limit
     * @param bool $offset
     * @return mixed
     */
    public function select($table, $where = array(), $limit = FALSE, $offset = FALSE) {
        $table = $this->prefix() . $table;
        if (($where = $this->prepare_where($where)) === FALSE) {
            $where = '1=1';
        }
        return $this->query('SELECT * FROM `' . $table . '` WHERE ' . $where . ' ' . $this->prepare_limit($limit, $offset) . ' ;');
    }

    /**
     * Insert record
     * @param   string
     * @param   array
     * @return  bool
     */
    public function insert($table, $data = array()) {
        $table = $this->prefix() . $table;
        if (($set = $this->prepare_set($data)) === FALSE) {
            return FALSE;
        }
        return $this->query('INSERT INTO `' . $table . '` SET ' . $set . ' ;');
    }

    /**
     * Update record in table (simple WHERE)
     * @param $table
     * @param array $set
     * @param array $where
     * @param int $limit
     * @param bool $offset
     * @return bool|mixed
     */
    public function update($table, $set = array(), $where = array(), $limit = 1, $offset = FALSE) {
        $table = $this->prefix() . $table;
        if (($set = $this->prepare_set($set)) === FALSE) {
            return FALSE;
        }
        if (($where = $this->prepare_where($where)) === FALSE) {
            $where = '1=1';
        }
        return $this->query('UPDATE `' . $table . '` SET ' . $set . ' WHERE ' . $where . ' ' . $this->prepare_limit($limit, $offset) . ' ;');
    }

    /**
     * Delete records from table (simple WHERE)
     * @param $table
     * @param array $where
     * @param int $limit
     * @param bool $offset
     * @return mixed
     */
    public function delete($table, $where = array(), $limit = 1, $offset = FALSE) {
        $table = $this->prefix() . $table;
        if (($where = $this->prepare_where($where)) === FALSE) {
            $where = '1=1';
        }
        return $this->query('DELETE FROM `' . $table . '` WHERE ' . $where . ' ' . $this->prepare_limit($limit, $offset) . ' ;');
    }

    /**
     * Escape string
     * @param   mixed
     * @return  mixed
     */
    public function escape($str) {
        if (is_array($str)) {
            foreach ($str as $key => $value) {
                $str[$key] = $this->escape($value);
            }
            return $str;
        }

        if (function_exists('mysql_real_escape_string') && is_resource($this->link)) {
            $str = mysql_real_escape_string($str, $this->link);
        } else {
            $str = addslashes($str);
        }
        return $str;
    }

    /**
     * Get affected rows
     * @return  int
     */
    function getAffectedRows() {
        return $this->affected_rows;
    }

    /**
     * Get num selected rows
     * @return  int
     */
    public function getNumRows() {
        return $this->num_rows;
    }

    /**
     * Get last inserted id
     * @return  int
     */
    public function getInsertId() {
        return $this->insert_id;
    }

    /**
     * Return query history
     * @return array
     */
    public function getQueries() {
        return $this->queries;
    }

    /**
     * Return count queries
     * @return int
     */
    public function getCountQueries() {
        return $this->count_queries;
    }

    /**
     * Get table prefix
     * @return string
     */
    public function prefix() {
        return $this->table_prefix;
    }

    /**
     *
     *
     * @param   string
     * @return  string
     */
    private function prepare_query($sql) {
        // "DELETE FROM TABLE" возвращает 0 в затронутых строка
        // это решение исправляет этот недостаток
        if (preg_match('/^\s*DELETE\s+FROM\s+(\S+)\s*$/i', $sql)) {
            $sql = preg_replace("/^\s*DELETE\s+FROM\s+(\S+)\s*$/", "DELETE FROM \\1 WHERE 1=1", $sql);
        }
        return $sql;
    }

    /**
     * Подготавливает данные для SET и UPDATE
     *
     * @param   array
     * @return  string
     */
    private function prepare_set($data = array()) {
        if (!is_array($data) || count($data) == 0) {
            return FALSE;
        }

        $bits = array();
        foreach ($data as $key => $value) {
            $bits[] = "`$key` = '" . $this->escape($value) . "'";
        }
        return implode(', ', $bits);
    }

    /**
     * Подготавливает данные для WHERE
     *
     * @param   array
     * @return  string
     */
    private function prepare_where($data = array()) {
        if (!is_array($data) || count($data) == 0) {
            return FALSE;
        }

        $bits = array();
        foreach ($data as $key => $value) {

            if (!$this->_has_operator($key)) {
                $key = '`' . $key . '` =';
            } else {
                $alias = '';
                if (strpos($key, ' ') !== FALSE) {
                    $alias = strstr($key, ' ');
                    $key = substr($key, 0, -strlen($alias));
                }
                $key = '`' . $key . '`' . $alias;
            }

            $escape = TRUE;
            if (strpos($key, '#') !== FALSE) {
                $escape = FALSE;
                $key = str_replace('#', '', $key);
            }

            $bits[] = $key . ' ' . ($escape ? '\'' . $this->escape($value) . '\'' : $value); //;
        }
        return implode(' AND ', $bits);
    }

    /**
     * Prepare data for LIMIT
     *
     * @param   int
     * @param   int
     * @return  string
     */
    private function prepare_limit($limit = FALSE, $offset = FALSE) {
        if ($limit === FALSE) {
            return '';
        }
        $offset = ($offset === FALSE) ? '' : ', ' . $offset;
        return 'LIMIT ' . $limit . $offset;
    }

    /**
     *
     *
     * @param   string
     * @return  bool
     */
    private function _has_operator($str) {
        $str = trim($str);
        if (!preg_match("/(\s|<?|!|=|is null|is not null)/i", $str)) {
            return FALSE;
        }
        return TRUE;
    }

    /**
     * Show database error
     * @param string Error message
     * @access private
     * @return void
     */
    private function _error($message) {
        throw new Exception('MySQL Error: ' . $message);
    }

}