php pdo mysql資料庫操作類

標籤：

原文:php pdo mysql資料庫操作類

 

findAll(array( ‘field‘=>‘ID,albumName,albumImage, mainActor,directors,tags,info,area, keywords,wflag,year,mod_version,totalDuration‘, ‘where‘=>$where, ‘order‘=>‘flag desc,hit_count desc‘, ‘limit‘=>"{$limit[‘offset‘]},{$limit[‘pagesize‘]}" )); $res=$db->field(‘ID,stitle,jisu,liuchang,gaoqing,chaoqing, totalDuration,bTime,eTime‘) ->where($where) ->limit("{$limit[‘offset‘]},{$limit[‘pagesize‘]}") ->findAll();/ final class MyDb { private $pdo; public $tableName;//表名private $sql;//當前執行的SQL語句public $error=‘‘;//當前執行sql的錯誤訊息public $prefix=‘‘;//首碼public $charset=‘utf8‘; public $sqlQuery=array( ‘field‘=>‘‘, ‘where‘=>‘1‘, ‘join‘=>‘‘, ‘group‘=>‘‘, ‘having‘=>‘‘, ‘order‘=>‘‘, ‘limit‘=>‘‘, ‘union‘=>‘‘, ‘params‘=>array() ); public $fields=array();//得到當前表所有的欄位名稱

 

private static $_instance=array();protected $dbName;//使用者名稱protected $username;private $k;//密碼protected $password;//主機名稱protected $host;//連接埠號碼protected $port;//一個時間戳記，表示當前連結在什麼時候到期，到期後，將重建一個對象。protected $expireTime;//PDO連結屬性數組protected $attr=array(    //這個逾時參數，實際上mysql伺服器上的配置為準的。這裡用於什麼時候重建對象    //說明如是設定了這個參數，如果不顯式的將pdo設為null，可能造成串連資源在mysql上不被釋放。    \PDO::ATTR_TIMEOUT=>30,    \PDO::ATTR_ERRMODE=>\PDO::ERRMODE_SILENT,    \PDO::ATTR_ORACLE_NULLS=>\PDO::NULL_NATURAL,        //如果設定了這個參數為true,inndob，需要手動commit    //\PDO::ATTR_AUTOCOMMIT=>false,    \PDO::ATTR_DEFAULT_FETCH_MODE=>\PDO::FETCH_ASSOC,    \PDO::ATTR_PERSISTENT=>FALSE);/** * 構造方法 * @param array $config　設定檔 * @param array $attr 數組選項   */private  function __construct($config,$attr){    //索引數組合并不能使用merge    foreach ($this->attr as $key=>$row)    {    if(isset($attr[$key]))        $this->attr[$key]=$attr[$key];    }$this->pdo=new \PDO($config[‘dsn‘], $config[‘username‘],     $config[‘password‘],$this->attr);if(isset($config[‘prefix‘]) && $config[‘prefix‘]!=‘‘)$this->prefix=$config[‘prefix‘];if(isset($config[‘charset‘]) && $config[‘charset‘]!=‘‘)$this->charset=$config[‘charset‘];$this->pdo->exec("set names {$this->charset}");//儲存當前資料庫名稱，主機，連接埠。preg_match(‘/dbname=(\w+)/‘, $config[‘dsn‘],$ma);preg_match(‘/host=(.*?);/‘, $config[‘dsn‘],$ma1);preg_match(‘/port=(\w+)/‘, $config[‘dsn‘],$ma2);$this->dbName=$ma[1];$this->host=$ma1[1];$this->port=$ma2[1]?$ma2[1]:3306;$this->username=$config[‘username‘];$this->password=$config[‘password‘];//設定連結到期時間$timeout=$this->attr[\PDO::ATTR_TIMEOUT];$this->expireTime=time()+$timeout;}private function __clone(){}    /**     * @param $config     * @param array $attr     * @return \iphp\core\MyDb     */    static public function  getInstance($config,$attr=array()){    if(!is_array($config))    $config=App::getApp()->getConfig($config);    $k=md5(implode(‘‘, $config));            //如果串連沒有建立，或者串連已經失效if( !(static::$_instance[$k] instanceof self)){    static::$_instance[$k]=new self($config,$attr);    static::$_instance[$k]->k=$k;}//如果連線逾時。        elseif(time()>static::$_instance[$k]->expireTime)        {            static::$_instance[$k]->close();            static::$_instance[$k]=new self($config,$attr);            static::$_instance[$k]->k=$k;        }return static::$_instance[$k];}/** *  * @param unknown_type $tableName * @return $this */public function tableName($tableName){$this->tableName=$this->prefix.$tableName;//$this->setField();return $this;}    /**     * @return \PDO     */    public function getPdo(){return $this->pdo;}/** * 得到當前sql語句，並完成參數替換 * @return string */public function getSql(){return $this->sql;}    /**     * @param string $sql     * @return bool     */    public function findAll($sql=‘‘){$stmt=$this->query($sql);if(!$stmt)return false;return $stmt->fetchAll();}    /**     * @param string $sql     * @return bool     */    public function findOne($sql=‘‘){$this->sqlQuery[‘limit‘]=1;$stmt=$this->query($sql);if($stmt===false)return false;return $stmt->fetch();}/** * 根據主鍵尋找記錄 * @param mixed $ids  * @return 返回一維或二維數組 */public function find($ids){$num=count(explode(‘,‘, $ids));$this->setField();if($num==1){$res= $this->where("{$this->fields[$this->tableName][‘pk‘]}=‘{$ids}‘")->findOne();}else{//如果是字串主鍵，要加引號$tmp=explode(‘,‘, $ids);$tmp=array_map(function($item){return "‘".$item."‘";}, $tmp);$ids=implode(‘,‘, $tmp);$res= $this->where("{$this->fields[$this->tableName][‘pk‘]} in ({$ids})")->findAll();}return $res;}/** * 插入資料的方法，自動完成參數綁定 * @param  array $data 一維數組 array(Field=>value) * @return boolean | int */public function insert($data){$this->setField();$params=array();$field=array();$placeholder=array();foreach($data as $key=>$row){//刪除非法欄位資訊if(!in_array($key, $this->fields[$this->tableName]))continue;$params[‘:‘.$key]=$row;$field[]=$key;$placeholder[]=‘:‘.$key;}//插入目前記錄$sql="insert into {$this->tableName} (".implode(‘, ‘, $field).‘) values (‘.implode(‘, ‘, $placeholder).‘)‘;$this->sqlQuery[‘params‘]=$params;$this->sql=$sql;return $this->exec($sql,$this->sqlQuery[‘params‘]);}/** * 刪除記錄 * @param string $where where條件 * @param array $params 綁定參數     * @return bool */public function delete($where = ‘‘,$params = array()){if($where!=‘‘)$this->sqlQuery[‘where‘]=$where;if($params!=‘‘)$this->sqlQuery[‘params‘]=$params;$sql="delete from {$this->tableName} where {$this->sqlQuery[‘where‘]}";$this->sql=$sql;return $this->exec($sql,$this->sqlQuery[‘params‘]);}/** * 簡化的delete()方法，基於主鍵的刪除 */public function del($ids){$this->setField();$tmp=explode(‘,‘, $ids);$tmp=array_map(function($item){return "‘".$item."‘";}, $tmp);$ids=implode(‘,‘, $tmp);$sql="delete from {$this->tableName} where {$this->fields[$this->tableName][‘pk‘]}"." in ($ids)";$this->sql=$sql;return $this->exec($sql);}/** * 得到插入的最後ID號 */public function lastId(){return $this->pdo->lastInsertId();}/** * 修改資料 update 支援參數綁定 只支援where參數 * @param array $data 要改變的列的值數組 array(列名=>值) * @param  string $where where條件 * @param  array $params 綁定參數 * @return boolean ｜ int 受影響的行數  */public function update($data,$where=‘‘,$params= array()){$this->setField();if(!is_array($data))return false;if($where!=‘‘)$this->sqlQuery[‘where‘]=$where;if($params!=‘‘)$this->sqlQuery[‘params‘]=$params;$updateField=array();foreach($data as $key=>$value){//不合法的欄位不要if(!in_array($key, $this->fields[$this->tableName]))continue;$updateField[]="{$key}=:{$key}";$this->sqlQuery[‘params‘][":{$key}"]=$value;}$sql="update {$this->tableName} set ".implode(‘,‘, $updateField)." where {$this->sqlQuery[‘where‘]}";$this->sql=$sql;return $this->exec($sql,$this->sqlQuery[‘params‘]);}/** * 得到資料表的所有欄位資訊 */public function setField(){if(is_array($this->fields[$this->tableName]))return;$sql="desc {$this->tableName} ";$res=$this->findAll($sql);foreach ($res as $row){if($row[‘Key‘]==‘PRI‘)$this->fields[$this->tableName][‘pk‘]=$row[‘Field‘];$this->fields[$this->tableName][]=$row[‘Field‘];}}//得到當前動作表的欄位資訊public function getField(){    if(!$this->fields[$this->tableName])        $this->setField();return $this->fields[$this->tableName];}//得到記錄總數public function count($sql=‘‘){$this->sqlQuery[‘field‘]=‘count(*) as c‘;$stmt=$this->query($sql);if(!$stmt)return false;$res=$stmt->fetch();//執行完之後要重設查詢欄位return $res[‘c‘];}//得到sql執行錯誤public function getError(){return $this->error;}public function setError($error){$this->error=$error;}/** * 掃行有結果集的查詢，支援參數綁定 * 如果你需要遍曆資料庫，請使用query方法，然後foreach 返回的stmt對象便可。 * @param mixed $sql  * @return boolean|PDOStatement */public function query($sql=‘‘){$sql=$this->joinSql($sql);$stmt=$this->pdo->prepare($sql);$errorInfo=$stmt->errorInfo();$stmt->setFetchMode(\PDO::FETCH_ASSOC);$stmt->execute($this->sqlQuery[‘params‘]);//清除sql條件值，desc類部執行的sql語句，不用清楚緩衝if(strpos($sql,‘desc‘)!==0)$this->clearSqlQuery();$errorInfo=$stmt->errorInfo();if($errorInfo[0]!=‘00000‘){$this->setError($errorInfo[2]);return false;}return $stmt;}/** * 執行沒有結果集的查詢,支援參數綁定 * @param string $sql * @param array $params * @return 返回受影響行數或false */public function exec($sql,$params = array()){$stmt=$this->pdo->prepare($sql);if($params!=‘‘)$this->sqlQuery[‘params‘]=$params;$stmt->execute($this->sqlQuery[‘params‘]);$this->clearSqlQuery();$errorInfo=$stmt->errorInfo();if($errorInfo[0]!=‘00000‘){$this->setError($errorInfo[2]);return false;}return $stmt->rowCount();}//設定綁定參數public function params($params){$this->sqlQuery[‘params‘]=empty($params)?‘‘:$params;return $this;}    /**     * 自動綁定參數     * @param $params     * @return $this     */    public function autoParams($params)    {        $this->setField();        foreach ($params as $key => $row) {            if(in_array($key, $this->fields[$this->tableName])) {                $this->sqlQuery[‘params‘][":{$key}"] = $row;            }        }        return $this;    }/** * 組合sql語句 * @param mixed $sql * @return 返回組合的sql語句 */public function joinSql($sql){if(is_string($sql) && $sql!=‘‘){$this->sql=$sql;return $sql;}elseif(is_array($sql) && $sql!=‘‘){foreach ($sql as $key=>$row){if(!array_key_exists($key, $this->sqlQuery))continue;$this->sqlQuery[$key]=$row;}}else {}$this->sql="select {$this->sqlQuery[‘field‘]} from {$this->tableName}\n";if($this->sqlQuery[‘join‘]!=‘‘)$this->sql.="{$this->sqlQuery[‘join‘]} ";$this->sql.="where {$this->sqlQuery[‘where‘]}\n";if($this->sqlQuery[‘group‘]!=‘‘)$this->sql.="group by {$this->sqlQuery[‘group‘]}\n";if($this->sqlQuery[‘having‘]!=‘‘)$this->sql.="having {$this->sqlQuery[‘having‘]}\n";if($this->sqlQuery[‘order‘]!=‘‘)$this->sql.="order by {$this->sqlQuery[‘order‘]}\n";if($this->sqlQuery[‘limit‘]!=‘‘)$this->sql.="limit {$this->sqlQuery[‘limit‘]}\n";if($this->sqlQuery[‘union‘]!=‘‘)$this->sql.="union {$this->sqlQuery[‘union‘]}\n";return $this->sql;}//設定欄位的方法public function field($field){$this->sqlQuery[‘field‘]=empty($field)?‘*‘:$field;return $this;}/** *  * @param unknown_type $where * @return \iphp\core\MyDb */public function where($where){$this->sqlQuery[‘where‘]=empty($where)?‘1‘:$where;return $this;}    /**     * @param $tableName     * @param $condition     * @return $this     */    public function join($tableName,$condition){$this->sqlQuery[‘join‘].="join {$tableName} on {$condition}\n";return $this;}    /**     * @param $tableName     * @param $condition     * @return $this     */    public function leftjoin($tableName,$condition){$this->sqlQuery[‘join‘].="left join {$tableName} on {$condition}\n";return $this;}    /**     * @param $tableName     * @param $condition     * @return $this     */    public function rightjoin($tableName,$condition){$this->sqlQuery[‘join‘].="right join {$tableName} on {$condition}\n";return $this;}    /**     * @param $group     * @return $this     */    public function group($group){$this->sqlQuery[‘group‘]=empty($group)?‘‘:$group;return $this;}    /**     * @param $having     * @return $this     */    public function having($having){$this->sqlQuery[‘having‘]=empty($having)?‘‘:$having;return $this;}    /**     * @param $order     * @return $this     */    public function order($order){$this->sqlQuery[‘order‘]=empty($order)?‘‘:$order;return $this;}    /**     * @param $limit     * @return $this     */    public function limit($limit){$this->sqlQuery[‘limit‘]=empty($limit)?‘‘:$limit;return $this;}    /**     * @param $union     * @return $this     */    public function union($union){$this->sqlQuery[‘union‘]=empty($union)?‘‘:$union;return $this;}/** * 清除sql緩衝 */public function clearSqlQuery(){//清除緩衝前，先儲存當前sql語句。if(!empty($this->sqlQuery[‘params‘])){foreach ($this->sqlQuery[‘params‘] as $key=>$param)$this->sql=str_replace($key, ‘"‘.$param.‘"‘, $this->sql);}$this->sql=nl2br($this->sql);foreach ($this->sqlQuery as $key=>$row){if($key==‘where‘)$this->sqlQuery[$key]=‘1‘;elseif ($key==‘field‘)$this->sqlQuery[$key]=‘*‘;elseif ($key==‘params‘)$this->sqlQuery[$key]=array();else $this->sqlQuery[$key]=‘‘;}}//再執行findone findall方法之前，得到當前要執行的sql語句,public function getSqlCache(){$sql=$this->joinSql(‘‘);if(!empty($this->sqlQuery[‘params‘])){foreach ($this->sqlQuery[‘params‘] as $key=>$param)$sql=str_replace($key, ‘"‘.$param.‘"‘, $sql);}return $sql;}/** * 得到當前資料庫名稱 */public function getDbName(){return $this->dbName;}/** * 得到使用者名稱 */public function getUser(){return $this->username;}/** * 得到密碼 */public function getPass(){return $this->password;}public function  getHost(){return $this->host;}public function getPort(){return $this->port;}/** * 得到串連相關的詳細資料。 */public function getConnInfo(){return array(    ‘host‘=>$this->host,    ‘port‘=>$this->port,    ‘username‘=>$this->username,    ‘password‘=>$this->password,    ‘dbname‘=>$this->dbName,);}    /**     * 開啟事務，並設定錯誤模式為異常     * 使用try cacth 來復原或提交     * beginTransaction()方法將會關閉自動認可（autocommit）模式，     * 直到事務提交或者復原以後才能恢複為pdo設定的模式     */    public function beginTransaction()    {        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);        return $this->pdo->beginTransaction();    }    /**     * 提交     * @return bool     */    public function commit()    {        return $this->pdo->commit();    }    /**     * 復原事務     * @return bool     */    public function rollBack()    {        return $this->pdo->rollBack();    }/** * 關閉串連 */public function close(){$this->pdo=null;}/** * 關閉所有串連 */public static function closeAll(){foreach(static::$_instance as $o){    if($o instanceof self)        $o->close();}}/** * 得到當前表的下一次自增長ID */public function getNextAutoIncrement($tableName){    $sql="show table status where name =‘{$tableName}‘";    $res=$this->findOne($sql);    return $res[‘Auto_increment‘];    }    /**     * 為一個表增加一個TIMESTAMP欄位     * @param $tableName　表名     * @param $name　欄位名     * @return bool|int     */    public function addTIMESTAMP($tableName,$name=‘utime‘)    {        $addSql="alter table {$tableName} add {$name} TIMESTAMP                 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;";        $addSql.="ALTER TABLE {$tableName} ADD index {$name}($name)";        return $this->exec($addSql);    }}

php pdo mysql資料庫操作類