classes/pdo_mysql.php

<?php

/*$host $user,$pass,$table беруться из файла config.class.php */


class pdo_mysql
{
    protected $_host = 'localhost';
    protected $_user = 'sites';
    protected $_pass = '';
    protected $_dbname = 'sites_sklad';
    protected $_init_command = "SET names utf8, lc_time_names = ru_RU, time_zone='Europe/Moscow'";

    protected $_db;
    protected static $_dbInstance;
    protected static $_instance;

    protected function _connect()
    {
        if (null !== self::$_dbInstance) return self::$_dbInstance;

        try {
            # MySQL через PDO_MYSQL
            $driver_options = array(
                PDO::MYSQL_ATTR_INIT_COMMAND => $this->_init_command,
            );
            self::$_dbInstance = new PDO("mysql:host={$this->host};dbname={$this->_dbname}", $this->_user, $this->_pass, $driver_options);
            self::$_dbInstance->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
            self::$_dbInstance->exec("SET names utf8");
        }
        catch(PDOException $e) {
            echo $e->getMessage();
        }
        return self::$_dbInstance;
    }

    public function __destruct()
    {
        self::$_dbInstance = null;
    }

    public static function getDbAdapter()
    {
        if (!self::$_dbInstance) {
            self::$_instance = new self();
            self::$_dbInstance = self::$_instance->_connect();
        }
        return self::$_dbInstance;
    }
}

/* mysql queries
$db = new mysql;
$db->insert( 'magazin', array( 'filter' => 'test', 'id_user' => '0', 'author' => 'robot', 'data' => new mysql_expr('NOW()') ) );
$db->update( 'magazin', array( 'filter' => 'test', 'id_user' => '0', 'author' => 'robot', 'data' => new mysql_expr('NOW()') ), array('id_user' => 0) );
$db->query("SELECT FROM `magazin`")
*/
class mysql
{
    public function __construct()
    {
        $this->_db = pdo_mysql::getDbAdapter();
    }

    public function fetchAll($q, $where = false)
    {
        $STH = $this->_query($q, $where);
        return $STH->fetchAll();
    }

    public function fetch($q, $where = false)
    {
        $STH = $this->_query($q, $where);
        if ($STH->rowCount()) {
            return $STH->fetch();
        }
        return null;
    }

    public function fetchOne($q, $where = false)
    {
        $STH = $this->_query($q, $where);

    }

    public function fetchPairs($q, $where = false)
    {
        $rows = array();
        $STH = $this->_query($q, $where);
        if ($STH->rowCount()) {
            while($row = $STH->fetch()) {
                $row_key1 = current($row);
                $row_key2 = next($row);
                if (!$row_key2) $row_key2 = $row_key1;
                $rows[$row_key1] = $row_key2;
            }
        }
        return $rows;
    }

    function exec($q)
    {
        $this->_db->exec($q);
    }

    // old function, delete please from all code
    function query($q, $where = false)
    {
        return $this->fetchAll($q, $where);
    }

    // old function, delete please from all code
    function query_one($q, $where = false)
    {
        return $this->fetchOne($q, $where);
    }

    // old function, delete please from all code
   function query_clear($q)
    {
        return $this->exec($q);
    }

    protected function _query($sql, $where = false)
    {
        try {
            if ($where) {
                $sql = str_replace('%where%', 'WHERE ' . $this->_where($where), $sql);
            } else {
                $sql = str_replace('%where%', '', $sql);
            }
            $STH = $this->_db->prepare($sql);
            $STH->setFetchMode(PDO::FETCH_ASSOC);
            $STH->execute();
        }
        catch(PDOException $e) {
            $error = $STH->errorInfo();
            trigger_error("Error in mysql->_query: " . var_export($error, true));
        }

        return $STH;
    }

    function insert($table, $insert_vals)
    {
        $keys = array_keys($insert_vals);
        $vals = $insert_vals;
        array_walk($vals, function(&$val, $key, &$insert_vals) {
            if ($val instanceof mysql_expr) {
                $val = $val->__toString();
                unset($insert_vals[$key]);
            } else {
                $val = ':' . $key;
            }
        }, $insert_vals);
        $sql = "INSERT INTO `{$table}` (`" . implode('`, `', $keys) . "`) VALUES (" . implode(', ', $vals) . ")";

        try {
            $STH = $this->_db->prepare($sql);
            $STH = $this->_bindArrayValue($STH, $insert_vals);
            $STH->execute();
        }
        catch(PDOException $e) {
            $error = $STH->errorInfo();
            trigger_error("Error in mysql->insert: " . var_export($error, true));
        }
    }

    // $this->simple_query_clear("UPDATE tovars SET s_from = '$from',s_to = '$to', r_status = '$status', kol = '$kol' WHERE id = '$id' LIMIT 1");
    function update($table, $set_vals, $where = array(), $limit = 1)
    {
        $vals = $set_vals;
        array_walk($vals, function(&$val, $key, &$set_vals) {
            // `key` = ...
            $ret_val = '`'.$key.'`' . ' = ';
            if ($val instanceof mysql_expr) {
                $ret_val .= $val->__toString();
                unset($set_vals[$key]);
            } else {
                $ret_val .= ':' . $key;
            }
            $val = $ret_val;
        }, $set_vals);
        $sql = "UPDATE `{$table}` SET ".implode(', ', $vals);

        if ($where) {
            $sql .= ' WHERE ' . $this->_where($where);
        }
        if ($limit) {
            $sql .= ' LIMIT ' . $limit;
        }
        try {
            $STH = $this->_db->prepare($sql);
            //var_dump($set_vals);die;
            $STH = $this->_bindArrayValue($STH, $set_vals);
            $STH->execute();
        }
        catch(PDOException $e) {
            $error = $STH->errorInfo();
            trigger_error("Error in mysql->update: " . var_export($error, true));
        }
    }

    public function delete($table, $where, $limit = 1)
    {
        $where = $this->_where($where);
        $sql = "DELETE FROM `{$table}` WHERE {$where} LIMIT {$limit}";
        try {
            $STH = $this->_db->prepare($sql);
            $STH->execute();
        }
        catch(PDOException $e) {
            $error = $STH->errorInfo();
            trigger_error("Error in mysql->delete: " . var_export($error, true));
        }
    }

    public function getDbAdapter()
    {
        return $this->_db;
    }

    public function escape($string)
    {
        return $this->getDbAdapter()->quote($string);
    }

    protected function _where($expr)
    {
        if (is_array($expr)) {
            foreach ($expr as $key => $val) {
                if ($val instanceof mysql_expr) {
                    $val = $val->__toString();
                } else if (is_string($key)) {
                    $val = $this->_db->quote($val);
                }
                if (is_int($key)) {
                    $where_vals[] = "({$val})";
                } else {
                    $where_vals[] = "(`{$key}` = {$val})";
                }
            }
            $expr = implode(' AND ', $where_vals);
        }
        return $expr;
    }

    protected function _bindArrayValue($req, $array, $typeArray = false)
    {
        if(is_object($req) && ($req instanceof PDOStatement))
        {
            foreach($array as $key => $value)
            {
                if($typeArray && isset($typeArray[$key])) {
                    $req->bindValue(':' . $key, $value, $typeArray[$key]);
                } else {
                    if(is_int($value))
                        $param = PDO::PARAM_INT;
                    elseif(is_bool($value))
                        $param = PDO::PARAM_BOOL;
                    elseif(is_null($value))
                        $param = PDO::PARAM_NULL;
                    elseif(is_string($value))
                        $param = PDO::PARAM_STR;
                    else
                        $param = FALSE;

                    if($param)
                        $req->bindValue(':' . $key, $value, $param);
                }
            }
            return $req;
        }
    }

}

class mysql_expr
{
    protected $_expression;

    public function __construct($expression)
    {
        $this->_expression = (string) $expression;
    }

    public function __toString()
    {
        return $this->_expression;
    }

}