pdo db 操作類

<?php namespace CommonDrive;  use PDO;  use Exception;  use PDOException;  /**   * 查詢   */  //$sss = $db->getAll("SELECT?*?FROM?live_userinfo_base?limit?10");  /**  ?*?插入  ?*/  //$sss?=?$db-&gt;insert('live_user_online',?[  //????'userid'?=&gt;?1111,  //????'name'?=&gt;?'aaaaaaa',  //????'roomid'?=&gt;?1006,  //????'role'??=&gt;?3,  //????'level'?=&gt;?3,  //????'ltime'?=&gt;?21312,  //????'regIp'?=&gt;?23432432  //]);  /**  ?*?更新  ?*/  //$sss?=?$db-&gt;update('live_user_online',?[  //????'name'?=&gt;?'aaaaaaasdfsdfsdfaaaaaaaaaaa',  //????'roomid'?=&gt;?1006,  //????'role'??=&gt;?3,  //????'level'?=&gt;?3,  //????'ltime'?=&gt;?21312,  //????'regIp'?=&gt;?23432432  //],?'userid?=?1111',?false);  /**  ?*?刪除  ?*/  //$sss?=?$db-&gt;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-&gt;dsn?=?'mysql:host='.$dbHost.';dbname='.$dbName;  ????????????$this-&gt;dbh?=?new?PDO($this-&gt;dsn,?$dbUser,?$dbPasswd);  ????????????$this-&gt;dbh-&gt;exec('SET?character_set_connection='.$dbCharset.',?character_set_results='.$dbCharset.',?character_set_client=binary');  ????????}?catch?(PDOException?$e)?{  ????????????$this-&gt;outputError($e-&gt;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-&gt;query($strSql,?$queryMode?=?'Row',?$debug?=?false);  ????}  ????public?function?getAll($strSql,?$debug?=?false)  ????{  ????????return?$this-&gt;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-&gt;debug($strSql);  ????????$recordset?=?$this-&gt;dbh-&gt;query($strSql);  ????????$this-&gt;getPDOError();  ????????if?($recordset)?{  ????????????$recordset-&gt;setFetchMode(PDO::FETCH_ASSOC);  ????????????if?($queryMode?==?'All')?{  ????????????????$result?=?$recordset-&gt;fetchAll();  ????????????}?elseif?($queryMode?==?'Row')?{  ????????????????$result?=?$recordset-&gt;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-&gt;checkFields($table,?$arrayDataValue);  ????????if?($where)?{  ????????????$strSql?=?'';  ????????????foreach?($arrayDataValue?as?$key?=&gt;?$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-&gt;debug($strSql);  ????????$result?=?$this-&gt;dbh-&gt;exec($strSql);  ????????$this-&gt;getPDOError();  ????????return?$result;  ????}  ????/**  ?????*?Insert?插入  ?????*  ?????*?@param?String?$table?表名  ?????*?@param?Array?$arrayDataValue?字段與值  ?????*?@param?Boolean?$debug  ?????*?@return?Int  ?????*/  ????public?function?insert($table,?$arrayDataValue,?$debug?=?false)  ????{  ????????$this-&gt;checkFields($table,?$arrayDataValue);  ????????$strSql?=?"INSERT?INTO?`$table`?(`".implode('`,`',?array_keys($arrayDataValue))."`)?VALUES?('".implode("','",?$arrayDataValue)."')";  ????????if?($debug?===?true)?$this-&gt;debug($strSql);  ????????$result?=?$this-&gt;dbh-&gt;exec($strSql);  //????????$this-&gt;dbh-&gt;query($strSql);  ????????$this-&gt;getPDOError();  ????????return?$this-&gt;dbh-&gt;lastInsertId();  ????}  ????/**  ?????*?Replace?覆蓋方式插入  ?????*  ?????*?@param?String?$table?表名  ?????*?@param?Array?$arrayDataValue?字段與值  ?????*?@param?Boolean?$debug  ?????*?@return?Int  ?????*/  ????public?function?replace($table,?$arrayDataValue,?$debug?=?false)  ????{  ????????$this-&gt;checkFields($table,?$arrayDataValue);  ????????$strSql?=?"REPLACE?INTO?`$table`(`".implode('`,`',?array_keys($arrayDataValue))."`)?VALUES?('".implode("','",?$arrayDataValue)."')";  ????????if?($debug?===?true)?$this-&gt;debug($strSql);  ????????$result?=?$this-&gt;dbh-&gt;exec($strSql);  ????????$this-&gt;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-&gt;outputError("'WHERE'?is?Null");  ????????}?else?{  ????????????$strSql?=?"DELETE?FROM?`$table`?WHERE?$where";  ????????????if?($debug?===?true)?$this-&gt;debug($strSql);  ????????????$result?=?$this-&gt;dbh-&gt;exec($strSql);  ????????????$this-&gt;getPDOError();  ????????????return?$result;  ????????}  ????}  ????/**  ?????*?execSql?執行SQL語句  ?????*  ?????*?@param?String?$strSql  ?????*?@param?Boolean?$debug  ?????*?@return?Int  ?????*/  ????public?function?execSql($strSql,?$debug?=?false)  ????{  ????????if?($debug?===?true)?$this-&gt;debug($strSql);  ????????$result?=?$this-&gt;dbh-&gt;exec($strSql);  ????????$this-&gt;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-&gt;debug($strSql);  ????????$arrTemp?=?$this-&gt;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-&gt;debug($strSql);  ????????$arrTemp?=?$this-&gt;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-&gt;query($strSql);  ????????$this-&gt;getPDOError();  ????????return?$arrayTableInfo[0]['Engine'];  ????}  ????/**  ?????*?beginTransaction?事務開始  ?????*/  ????private?function?beginTransaction()  ????{  ????????$this-&gt;dbh-&gt;beginTransaction();  ????}  ????/**  ?????*?commit?事務提交  ?????*/  ????private?function?commit()  ????{  ????????$this-&gt;dbh-&gt;commit();  ????}  ????/**  ?????*?rollback?事務回滾  ?????*/  ????private?function?rollback()  ????{  ????????$this-&gt;dbh-&gt;rollback();  ????}  ????/**  ?????*?transaction?通過事務處理多條SQL語句  ?????*?調用前需通過getTableEngine判斷表引擎是否支持事務  ?????*  ?????*?@param?array?$arraySql  ?????*?@return?Boolean  ?????*/  ????public?function?execTransaction($arraySql)  ????{  ????????$retval?=?1;  ????????$this-&gt;beginTransaction();  ????????foreach?($arraySql?as?$strSql)?{  ????????????if?($this-&gt;execSql($strSql)?==?0)?$retval?=?0;  ????????}  ????????if?($retval?==?0)?{  ????????????$this-&gt;rollback();  ????????????return?false;  ????????}?else?{  ????????????$this-&gt;commit();  ????????????return?true;  ????????}  ????}  ????public?function?quote($sqlVal)  ????{  ????????return?$this-&gt;dbh-&gt;quote($sqlVal);  ????}  ????/**  ?????*?checkFields?檢查指定字段是否在指定數據表中存在  ?????*  ?????*?@param?String?$table  ?????*?@param?array?$arrayField  ?????*/  ????private?function?checkFields($table,?$arrayFields)  ????{  ????????$fields?=?$this-&gt;getFields($table);  ????????foreach?($arrayFields?as?$key?=&gt;?$value)?{  ????????????if?(!in_array($key,?$fields))?{  ????????????????$this-&gt;outputError("Unknown?column?`$key`?in?field?list.");  ????????????}  ????????}  ????}  ????/**  ?????*?getFields?獲取指定數據表中的全部字段名  ?????*  ?????*?@param?String?$table?表名  ?????*?@return?array  ?????*/  ????private?function?getFields($table)  ????{  ????????$fields?=?array();  ????????$recordset?=?$this-&gt;dbh-&gt;query("SHOW?COLUMNS?FROM?$table");  ????????$this-&gt;getPDOError();  ????????$recordset-&gt;setFetchMode(PDO::FETCH_ASSOC);  ????????$result?=?$recordset-&gt;fetchAll();  ????????foreach?($result?as?$rows)?{  ????????????$fields[]?=?$rows['Field'];  ????????}  ????????return?$fields;  ????}  ????/**  ?????*?getPDOError?捕獲PDO錯誤信息  ?????*/  ????private?function?getPDOError()  ????{  ????????if?($this-&gt;dbh-&gt;errorCode()?!=?'00000')?{  ????????????$arrayError?=?$this-&gt;dbh-&gt;errorInfo();  ????????????$this-&gt;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-&gt;dbh?=?null;  ????}  ????/**  ?????*?析構函數  ?????*/  ????public?function?__destruct()  ????{  ????????$this-&gt;destruct();  ????}  }</jasonwei06>
? 版權聲明
THE END
喜歡就支持一下吧
點贊6 分享