<?php namespace CommonDrive; use PDO; use Exception; use PDOException; /** * 查詢 */ //$sss = $db->getAll("SELECT?*?FROM?live_userinfo_base?limit?10"); /** ?*?插入 ?*/ //$sss?=?$db->insert('live_user_online',?[ //????'userid'?=>?1111, //????'name'?=>?'aaaaaaa', //????'roomid'?=>?1006, //????'role'??=>?3, //????'level'?=>?3, //????'ltime'?=>?21312, //????'regIp'?=>?23432432 //]); /** ?*?更新 ?*/ //$sss?=?$db->update('live_user_online',?[ //????'name'?=>?'aaaaaaasdfsdfsdfaaaaaaaaaaa', //????'roomid'?=>?1006, //????'role'??=>?3, //????'level'?=>?3, //????'ltime'?=>?21312, //????'regIp'?=>?23432432 //],?'userid?=?1111',?false); /** ?*?刪除 ?*/ //$sss?=?$db->delete('live_user_online',?'userid?=?1111',?false); //print_r($sss); /** ?*?Db?簡單數據庫操作類 ?*/ /** ?*?MyPDO ?*?@author?Jason.Wei?<jasonwei06> ?*?@license?http://www.sunbloger.com/ ?*?@version?5.0?utf8 ?*/ class?Db { ????protected?static?$_instance?=?null; ????protected?$dbName?=?''; ????protected?$dsn; ????protected?$dbh; ????/** ?????*?構造 ?????* ?????*?@return?MyPDO ?????*/ ????private?function?__construct($dbHost,?$dbUser,?$dbPasswd,?$dbName,?$dbCharset) ????{ ????????try?{ ????????????$this->dsn?=?'mysql:host='.$dbHost.';dbname='.$dbName; ????????????$this->dbh?=?new?PDO($this->dsn,?$dbUser,?$dbPasswd); ????????????$this->dbh->exec('SET?character_set_connection='.$dbCharset.',?character_set_results='.$dbCharset.',?character_set_client=binary'); ????????}?catch?(PDOException?$e)?{ ????????????$this->outputError($e->getMessage()); ????????} ????} ????/** ?????*?防止克隆 ?????* ?????*/ ????private?function?__clone()?{} ????/** ?????*?Singleton?instance ?????* ?????*?@return?Object ?????*/ ????public?static?function?getInstance($dbHost,?$dbUser,?$dbPasswd,?$dbName,?$dbCharset) ????{ ????????if?(self::$_instance?===?null)?{ ????????????self::$_instance?=?new?self($dbHost,?$dbUser,?$dbPasswd,?$dbName,?$dbCharset); ????????} ????????return?self::$_instance; ????} ????public?function?getOne($strSql,?$debug?=?false) ????{ ????????return?$this->query($strSql,?$queryMode?=?'Row',?$debug?=?false); ????} ????public?function?getAll($strSql,?$debug?=?false) ????{ ????????return?$this->query($strSql,?$queryMode?=?'All',?$debug?=?false); ????} ????/** ?????*?Query?查詢 ?????* ?????*?@param?String?$strSql?SQL語句 ?????*?@param?String?$queryMode?查詢方式(All?or?Row) ?????*?@param?Boolean?$debug ?????*?@return?Array ?????*/ ????private?function?query($strSql,?$queryMode?=?'All',?$debug?=?false) ????{ ????????if?($debug?===?true)?$this->debug($strSql); ????????$recordset?=?$this->dbh->query($strSql); ????????$this->getPDOError(); ????????if?($recordset)?{ ????????????$recordset->setFetchMode(PDO::FETCH_ASSOC); ????????????if?($queryMode?==?'All')?{ ????????????????$result?=?$recordset->fetchAll(); ????????????}?elseif?($queryMode?==?'Row')?{ ????????????????$result?=?$recordset->fetch(); ????????????} ????????}?else?{ ????????????$result?=?null; ????????} ????????return?$result; ????} ????/** ?????*?Update?更新 ?????* ?????*?@param?String?$table?表名 ?????*?@param?Array?$arrayDataValue?字段與值 ?????*?@param?String?$where?條件 ?????*?@param?Boolean?$debug ?????*?@return?Int ?????*/ ????public?function?update($table,?$arrayDataValue,?$where?=?'',?$debug?=?false) ????{ ????????$this->checkFields($table,?$arrayDataValue); ????????if?($where)?{ ????????????$strSql?=?''; ????????????foreach?($arrayDataValue?as?$key?=>?$value)?{ ????????????????$strSql?.=?",?`$key`='$value'"; ????????????} ????????????$strSql?=?substr($strSql,?1); ????????????$strSql?=?"UPDATE?`$table`?SET?$strSql?WHERE?$where"; ????????}?else?{ ????????????$strSql?=?"REPLACE?INTO?`$table`?(`".implode('`,`',?array_keys($arrayDataValue))."`)?VALUES?('".implode("','",?$arrayDataValue)."')"; ????????} ????????if?($debug?===?true)?$this->debug($strSql); ????????$result?=?$this->dbh->exec($strSql); ????????$this->getPDOError(); ????????return?$result; ????} ????/** ?????*?Insert?插入 ?????* ?????*?@param?String?$table?表名 ?????*?@param?Array?$arrayDataValue?字段與值 ?????*?@param?Boolean?$debug ?????*?@return?Int ?????*/ ????public?function?insert($table,?$arrayDataValue,?$debug?=?false) ????{ ????????$this->checkFields($table,?$arrayDataValue); ????????$strSql?=?"INSERT?INTO?`$table`?(`".implode('`,`',?array_keys($arrayDataValue))."`)?VALUES?('".implode("','",?$arrayDataValue)."')"; ????????if?($debug?===?true)?$this->debug($strSql); ????????$result?=?$this->dbh->exec($strSql); //????????$this->dbh->query($strSql); ????????$this->getPDOError(); ????????return?$this->dbh->lastInsertId(); ????} ????/** ?????*?Replace?覆蓋方式插入 ?????* ?????*?@param?String?$table?表名 ?????*?@param?Array?$arrayDataValue?字段與值 ?????*?@param?Boolean?$debug ?????*?@return?Int ?????*/ ????public?function?replace($table,?$arrayDataValue,?$debug?=?false) ????{ ????????$this->checkFields($table,?$arrayDataValue); ????????$strSql?=?"REPLACE?INTO?`$table`(`".implode('`,`',?array_keys($arrayDataValue))."`)?VALUES?('".implode("','",?$arrayDataValue)."')"; ????????if?($debug?===?true)?$this->debug($strSql); ????????$result?=?$this->dbh->exec($strSql); ????????$this->getPDOError(); ????????return?$result; ????} ????/** ?????*?Delete?刪除 ?????* ?????*?@param?String?$table?表名 ?????*?@param?String?$where?條件 ?????*?@param?Boolean?$debug ?????*?@return?Int ?????*/ ????public?function?delete($table,?$where?=?'',?$debug?=?false) ????{ ????????if?($where?==?'')?{ ????????????$this->outputError("'WHERE'?is?Null"); ????????}?else?{ ????????????$strSql?=?"DELETE?FROM?`$table`?WHERE?$where"; ????????????if?($debug?===?true)?$this->debug($strSql); ????????????$result?=?$this->dbh->exec($strSql); ????????????$this->getPDOError(); ????????????return?$result; ????????} ????} ????/** ?????*?execSql?執行SQL語句 ?????* ?????*?@param?String?$strSql ?????*?@param?Boolean?$debug ?????*?@return?Int ?????*/ ????public?function?execSql($strSql,?$debug?=?false) ????{ ????????if?($debug?===?true)?$this->debug($strSql); ????????$result?=?$this->dbh->exec($strSql); ????????$this->getPDOError(); ????????return?$result; ????} ????/** ?????*?獲取字段最大值 ?????* ?????*?@param?string?$table?表名 ?????*?@param?string?$field_name?字段名 ?????*?@param?string?$where?條件 ?????*/ ????public?function?getMaxValue($table,?$field_name,?$where?=?'',?$debug?=?false) ????{ ????????$strSql?=?"SELECT?MAX(".$field_name.")?AS?MAX_VALUE?FROM?$table"; ????????if?($where?!=?'')?$strSql?.=?"?WHERE?$where"; ????????if?($debug?===?true)?$this->debug($strSql); ????????$arrTemp?=?$this->query($strSql,?'Row'); ????????$maxValue?=?$arrTemp["MAX_VALUE"]; ????????if?($maxValue?==?""?||?$maxValue?==?null)?{ ????????????$maxValue?=?0; ????????} ????????return?$maxValue; ????} ????/** ?????*?獲取指定列的數量 ?????* ?????*?@param?string?$table ?????*?@param?string?$field_name ?????*?@param?string?$where ?????*?@param?bool?$debug ?????*?@return?int ?????*/ ????public?function?getCount($table,?$field_name,?$where?=?'',?$debug?=?false) ????{ ????????$strSql?=?"SELECT?COUNT($field_name)?AS?NUM?FROM?$table"; ????????if?($where?!=?'')?$strSql?.=?"?WHERE?$where"; ????????if?($debug?===?true)?$this->debug($strSql); ????????$arrTemp?=?$this->query($strSql,?'Row'); ????????return?$arrTemp['NUM']; ????} ????/** ?????*?獲取表引擎 ?????* ?????*?@param?String?$dbName?庫名 ?????*?@param?String?$tableName?表名 ?????*?@param?Boolean?$debug ?????*?@return?String ?????*/ ????public?function?getTableEngine($dbName,?$tableName) ????{ ????????$strSql?=?"SHOW?TABLE?STATUS?FROM?$dbName?WHERE?Name='".$tableName."'"; ????????$arrayTableInfo?=?$this->query($strSql); ????????$this->getPDOError(); ????????return?$arrayTableInfo[0]['Engine']; ????} ????/** ?????*?beginTransaction?事務開始 ?????*/ ????private?function?beginTransaction() ????{ ????????$this->dbh->beginTransaction(); ????} ????/** ?????*?commit?事務提交 ?????*/ ????private?function?commit() ????{ ????????$this->dbh->commit(); ????} ????/** ?????*?rollback?事務回滾 ?????*/ ????private?function?rollback() ????{ ????????$this->dbh->rollback(); ????} ????/** ?????*?transaction?通過事務處理多條SQL語句 ?????*?調用前需通過getTableEngine判斷表引擎是否支持事務 ?????* ?????*?@param?array?$arraySql ?????*?@return?Boolean ?????*/ ????public?function?execTransaction($arraySql) ????{ ????????$retval?=?1; ????????$this->beginTransaction(); ????????foreach?($arraySql?as?$strSql)?{ ????????????if?($this->execSql($strSql)?==?0)?$retval?=?0; ????????} ????????if?($retval?==?0)?{ ????????????$this->rollback(); ????????????return?false; ????????}?else?{ ????????????$this->commit(); ????????????return?true; ????????} ????} ????public?function?quote($sqlVal) ????{ ????????return?$this->dbh->quote($sqlVal); ????} ????/** ?????*?checkFields?檢查指定字段是否在指定數據表中存在 ?????* ?????*?@param?String?$table ?????*?@param?array?$arrayField ?????*/ ????private?function?checkFields($table,?$arrayFields) ????{ ????????$fields?=?$this->getFields($table); ????????foreach?($arrayFields?as?$key?=>?$value)?{ ????????????if?(!in_array($key,?$fields))?{ ????????????????$this->outputError("Unknown?column?`$key`?in?field?list."); ????????????} ????????} ????} ????/** ?????*?getFields?獲取指定數據表中的全部字段名 ?????* ?????*?@param?String?$table?表名 ?????*?@return?array ?????*/ ????private?function?getFields($table) ????{ ????????$fields?=?array(); ????????$recordset?=?$this->dbh->query("SHOW?COLUMNS?FROM?$table"); ????????$this->getPDOError(); ????????$recordset->setFetchMode(PDO::FETCH_ASSOC); ????????$result?=?$recordset->fetchAll(); ????????foreach?($result?as?$rows)?{ ????????????$fields[]?=?$rows['Field']; ????????} ????????return?$fields; ????} ????/** ?????*?getPDOError?捕獲PDO錯誤信息 ?????*/ ????private?function?getPDOError() ????{ ????????if?($this->dbh->errorCode()?!=?'00000')?{ ????????????$arrayError?=?$this->dbh->errorInfo(); ????????????$this->outputError($arrayError[2]); ????????} ????} ????/** ?????*?debug ?????* ?????*?@param?mixed?$debuginfo ?????*/ ????private?function?debug($debuginfo) ????{ ????????var_dump($debuginfo); ????????exit(); ????} ????/** ?????*?輸出錯誤信息 ?????* ?????*?@param?String?$strErrMsg ?????*/ ????private?function?outputError($strErrMsg) ????{ ????????throw?new?Exception('MySQL?Error:?'.$strErrMsg); ????} ????/** ?????*?destruct?關閉數據庫連接 ?????*/ ????public?function?destruct() ????{ ????????$this->dbh?=?null; ????} ????/** ?????*?析構函數 ?????*/ ????public?function?__destruct() ????{ ????????$this->destruct(); ????} }</jasonwei06>
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
