原文: php pdo mysql數(shù)據(jù)庫操作類
?
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
;
//
當(dāng)前執(zhí)行的SQL語句
public
$error
='';
//
當(dāng)前執(zhí)行sql的錯誤消息
public
$prefix
='';
//
前綴
public
$charset
='utf8'
;
public
$sqlQuery
=
array
(
'field'=>'',
'where'=>'1',
'join'=>'',
'group'=>'',
'having'=>'',
'order'=>'',
'limit'=>'',
'union'=>'',
'params'=>
array
()
);
public
$fields
=
array
();
//
得到當(dāng)前表所有的字段名稱
?
private static $_instance=array();
protected $dbName;
//用戶名
protected $username;
private $k;
//密碼
protected $password;
//主機(jī)名
protected $host;
//端口號
protected $port;
//一個時間戳，表示當(dāng)前鏈接在什么時候過期，過期后，將重建一個對象。
protected $expireTime;
//PDO鏈接屬性數(shù)組
protected $attr=array(
//這個超時參數(shù)，實際上mysql服務(wù)器上的配置為準(zhǔn)的。這里用于什么時候重建對象
//說明如是設(shè)置了這個參數(shù)，如果不顯式的將pdo設(shè)為null，可能造成連接資源在mysql上不被釋放。
\PDO::ATTR_TIMEOUT=>30,
\PDO::ATTR_ERRMODE=>\PDO::ERRMODE_SILENT,
\PDO::ATTR_ORACLE_NULLS=>\PDO::NULL_NATURAL,
//如果設(shè)置了這個參數(shù)為true,inndob，需要手動commit
//\PDO::ATTR_AUTOCOMMIT=>false,
\PDO::ATTR_DEFAULT_FETCH_MODE=>\PDO::FETCH_ASSOC,
\PDO::ATTR_PERSISTENT=>FALSE
);
/**
* 構(gòu)造方法
* @param array $config 配置文件
* @param array $attr 數(shù)組選項
*/
private function __construct($config,$attr)
{
//索引數(shù)組合并不能使用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}");
//保存當(dāng)前數(shù)據(jù)庫名稱，主機(jī)，端口。
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'];
//設(shè)置鏈接過期時間
$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));
//如果連接沒有創(chuàng)建，或者連接已經(jīng)失效
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;
}
/**
* 得到當(dāng)前sql語句，并完成參數(shù)替換
* @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();
}
/**
* 根據(jù)主鍵查找記錄
* @param mixed $ids
* @return 返回一維或二維數(shù)組
*/
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;
}
/**
* 插入數(shù)據(jù)的方法，自動完成參數(shù)綁定
* @param array $data 一維數(shù)組 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;
}
//插入當(dāng)前記錄
$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 綁定參數(shù)
* @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();
}
/**
* 修改數(shù)據(jù) update 支持參數(shù)綁定 只支持where參數(shù)
* @param array $data 要改變的列的值數(shù)組 array(列名=>值)
* @param string $where where條件
* @param array $params 綁定參數(shù)
* @return boolean ｜ int 受影響的行數(shù)
*/
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']);
}
/**
* 得到數(shù)據(jù)表的所有字段信息
*/
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'];
}
}
//得到當(dāng)前操作表的字段信息
public function getField()
{
if(!$this->fields[$this->tableName])
$this->setField();
return $this->fields[$this->tableName];
}
//得到記錄總數(shù)
public function count($sql='')
{
$this->sqlQuery['field']='count(*) as c';
$stmt=$this->query($sql);
if(!$stmt)
return false;
$res=$stmt->fetch();
//執(zhí)行完之后要重置查詢字段
return $res['c'];
}
//得到sql執(zhí)行錯誤
public function getError()
{
return $this->error;
}
public function setError($error)
{
$this->error=$error;
}
/**
* 掃行有結(jié)果集的查詢，支持參數(shù)綁定
* 如果你需要遍歷數(shù)據(jù)庫，請使用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類部執(zhí)行的sql語句，不用清楚緩存
if(strpos($sql,'desc')!==0)
$this->clearSqlQuery();
$errorInfo=$stmt->errorInfo();
if($errorInfo[0]!='00000')
{
$this->setError($errorInfo[2]);
return false;
}
return $stmt;
}
/**
* 執(zhí)行沒有結(jié)果集的查詢,支持參數(shù)綁定
* @param string $sql
* @param array $params
* @return 返回受影響行數(shù)或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();
}
//設(shè)定綁定參數(shù)
public function params($params)
{
$this->sqlQuery['params']=empty($params)?'':$params;
return $this;
}
/**
* 自動綁定參數(shù)
* @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;
}
//設(shè)定字段的方法
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()
{
//清除緩存前，先保存當(dāng)前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]='';
}
}
//再執(zhí)行findone findall方法之前，得到當(dāng)前要執(zhí)行的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;
}
/**
* 得到當(dāng)前數(shù)據(jù)庫名稱
*/
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;
}
/**
* 得到連接相關(guān)的詳細(xì)信息。
*/
public function getConnInfo()
{
return array(
'host'=>$this->host,
'port'=>$this->port,
'username'=>$this->username,
'password'=>$this->password,
'dbname'=>$this->dbName,
);
}
/**
* 開啟事務(wù)，并設(shè)置錯誤模式為異常
* 使用try cacth 來回滾或提交
* beginTransaction()方法將會關(guān)閉自動提交（autocommit）模式，
* 直到事務(wù)提交或者回滾以后才能恢復(fù)為pdo設(shè)置的模式
*/
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();
}
/**
* 回滾事務(wù)
* @return bool
*/
public function rollBack()
{
return $this->pdo->rollBack();
}
/**
* 關(guān)閉連接
*/
public function close()
{
$this->pdo=null;
}
/**
* 關(guān)閉所有連接
*/
public static function closeAll()
{
foreach(static::$_instance as $o)
{
if($o instanceof self)
$o->close();
}
}
/**
* 得到當(dāng)前表的下一次自增長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);
}
}
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
