game2004api/webapp/phpcommon/mysql.php

<?php

namespace phpcommon;

class Mysql
{
    private $_host = '';
    private $_port = 3306;
    private $_user = '';
    private $_passwd = '';
    private $_dbname = '';

    private $_conn = null;
    private $_result = null;

    public function __construct($configs){
        $this->_host = $configs['host'];
        $this->_user = $configs['user'];
        $this->_passwd = $configs['passwd'];
        $this->_dbname = $configs['dbname'];
        $conn_str = sprintf("mysql:host=%s;port=%d;dbname=%s;charset=utf8",
                            $this->_host,
                            $this->_port,
                            $this->_dbname);
        $this->_conn =  new \PDO($conn_str,
                                $this->_user,
                                $this->_passwd
        );
    }

    public function __destruct(){
        $this->_conn = null;
    }

    public function execQuery($querystr, $params = null){
        $params = $params ? $params : array();
        $statement = $this->_conn->prepare($querystr);
        $ret = $statement->execute($params);
        if(!$ret){
            error_log($statement->errorInfo()[2]);
            error_log('callstack:' . json_encode(debug_backtrace(), JSON_PRETTY_PRINT));
            die();
            return null;
        }else{
            return $statement->fetchAll(\PDO::FETCH_ASSOC);
        }
    }

    public function execQueryAsArray($querystr, $params = null){
        $params = $params ? $params : array();
        $statement = $this->_conn->prepare($querystr);
        $ret = $statement->execute($params);
        if(!$ret){
            error_log($statement->errorInfo()[2]);
            error_log('callstack:' . json_encode(debug_backtrace(), JSON_PRETTY_PRINT));
            die();
            return null;
        }else{
            return $statement->fetchAll(\PDO::FETCH_NUM);
        }
    }

    public function execQueryOne($querystr, $params = null){
        $params = $params ? $params : array();
        $statement = $this->_conn->prepare($querystr);
        $ret = $statement->execute($params);
        if(!$ret){
            error_log($statement->errorInfo()[2]);
            error_log('callstack:' . json_encode(debug_backtrace(), JSON_PRETTY_PRINT));
            die();
            return null;
        }else{
            $rows = $statement->fetchAll();
            return count($rows) > 0 ? $rows[0] : null;
        }
    }

    public function execQueryEx($querystr, $params, $condexper, $orderexper=null){
        $params = $params ? $params : array();
        $querystr = "SELECT * FROM ($querystr) a WHERE 1=1";
        foreach($condexper as $fieldname=>$paramname){
            if(array_key_exists($paramname, $_REQUEST) && $_REQUEST[$paramname] != ''){
                $quoted_str = $this->_conn->quote('%' . $_REQUEST[$paramname] . '%');
                $querystr = $querystr . " AND $fieldname LIKE $quoted_str";
            }
        }
        if($orderexper && count($orderexper) > 0){
            $querystr = $querystr . ' ORDER BY ';
            foreach($orderexper as $expr){
                $querystr = $querystr . " $expr[0] $expr[1],";
            }
            $querystr = substr($querystr, 0, strlen($querystr) - 1);
        }
        if(array_key_exists('start', $_REQUEST) && array_key_exists('limit', $_REQUEST)){
            $querystr .= " LIMIT $_REQUEST[start], $_REQUEST[limit]";
        }
        //		error_log($querystr);
        $statement = $this->_conn->prepare($querystr);
        $ret = $statement->execute($params);
        if(!$ret){
            error_log($statement->errorInfo()[2]);
            error_log('callstack:' . json_encode(debug_backtrace(), JSON_PRETTY_PRINT));
        }
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }

    public function execQueryTotalCount($querystr, $params, $condexper, $orderexper){
        $params = $params ? $params : array();
        $querystr = "SELECT COUNT(*) FROM ($querystr) a WHERE 1=1";
        foreach($condexper as $fieldname=>$paramname){
            if(array_key_exists($paramname, $_REQUEST) && $_REQUEST[$paramname] != ''){
                $quoted_str = $this->_conn->quote('%' . $_REQUEST[$paramname] . '%');
                $querystr = $querystr . " AND $fieldname LIKE $quoted_str";
            }
        }
        if($orderexper && count($orderexper) > 0){
            $querystr = $querystr . ' ORDER BY ';
            foreach($orderexper as $expr){
                $querystr = $querystr . " $expr[0] $expr[1],";
            }
            $querystr = substr($querystr, 0, strlen($querystr) - 1);
        }
        $statement = $this->_conn->prepare($querystr);
        $ret = $statement->execute($params);
        if(!$ret){
            error_log($statement->errorInfo()[2]);
            error_log('callstack:' . json_encode(debug_backtrace(), JSON_PRETTY_PRINT));
            die();
            return -1;
        }
        $rows = $statement->fetchAll();
        return count($rows) > 0 ? $rows[0][0] : -1;
    }

    public function execQueryPage($querystr, $params, $condexper, $orderexper, &$totalcount){
        $totalcount = $this->execQueryTotalCount($querystr, $params, $condexper, $orderexper);
        return $this->execQueryEx($querystr, $params, $condexper, $orderexper);
    }

    public function execQueryRowCount($querystr, $params = null){
        $params = $params ? $params : array();
        $statement = $this->_conn->prepare($querystr);
        $ret = $statement->execute($params);
        if(!$ret){
            error_log($statement->errorInfo()[2]);
            error_log('callstack:' . json_encode(debug_backtrace(), JSON_PRETTY_PRINT));
            die();
        }
        return $statement->rowCount();
    }

    public function execScript($querystr, $params = null){
        $params = $params ? $params : array();
        $statement = $this->_conn->prepare($querystr);
        $ret = $statement->execute($params);
        if(!$ret){
            error_log('execScript： ' . $statement->errorInfo()[2]);
            error_log('callstack:' . json_encode(debug_backtrace(), JSON_PRETTY_PRINT));
            die();
        }
        return $ret;
    }

}