搜尋
首頁php教程PHP源码使用php中的PDO方式做到数据库的增删改查及类的单例花

使用php中的PDO方式做到数据库的增删改查及类的单例花

PHP中文网
PHP中文网
May 23, 2016 pm 05:10 PM

跳至

class DPDO{
	private $DSN;
	private $DBUser;
	private $DBPwd;
	private $longLink;
	private $pdo;
	//私有构造函数 防止被直接实例化
	private function __construct($dsn, $DBUser, $DBPwd, $longLink = false) {
		$this->DSN = $dsn;
		$this->DBUser = $DBUser;
		$this->DBPwd = $DBPwd;
		$this->longLink = $longLink;
		$this->connect();
	}
	//私有 空克隆函数 防止被克隆
	private function __clone(){}
	//静态 实例化函数 返回一个pdo对象
	static public function instance($dsn, $DBUser, $DBPwd, $longLink = false){
		static $singleton = array();//静态函数 用于存储实例化对象
		$singIndex = md5($dsn . $DBUser . $DBPwd . $longLink);
		if (empty($singleton[$singIndex])) {
			$singleton[$singIndex] = new self($dsn, $DBUser, $DBPwd, $longLink = false);
		}
		return $singleton[$singIndex]->pdo;
	}
	
	private function connect(){
		try{
			if($this->longLink){
				$this->pdo = new PDO($this->DSN, $this->DBUser, $this->DBPwd, array(PDO::ATTR_PERSISTENT => true));
			}else{
				$this->pdo = new PDO($this->DSN, $this->DBUser, $this->DBPwd);
			}
			$this->pdo->query('SET NAMES UTF-8');
		} catch(PDOException $e) {
			die('Error:' . $e->getMessage() . '
');
		}
	}
}

                       

           

2. [代码]用于处理字段映射，使用pdo的字段映射，可以有效避免sql注入   

//字段关联数组处理， 主要用于写入和更新数据、同and 或 or 的查询条件，产生sql语句和映射字段的数组
	public function FDFields($data, $link = ',', $judge = array(), $aliasTable = ''){
		$sql = '';
		$mapData = array();
		foreach($data as $key => $value) {
			$mapIndex = ':' . ($link != ',' ? 'c' : '') . $aliasTable . $key;
			$sql .= ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $key . '` ' . ($judge[$key] ? $judge[$key] : '=') . ' ' . $mapIndex . ' ' . $link;
			$mapData[$mapIndex] = $value;
		}
		$sql = trim($sql, $link);
		return array($sql, $mapData);
	}
	//用于处理单个字段处理
	public function FDField($field, $value, $judge = '=', $preMap = 'cn', $aliasTable = '') {
		$mapIndex = ':' . $preMap . $aliasTable . $field;
		$sql = ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $field . '`' . $judge . $mapIndex;
		$mapData[$mapIndex] = $value;
		return array($sql, $mapData);
	}
	//使用刚方法可以便捷产生查询条件及对应数据数组
	public function FDCondition($condition, $mapData) {
		if(is_string($condition)) {
				$where = $condition;
		} else if (is_array($condition)) {
			if($condition['str']) {
				if (is_string($condition['str'])) {
					$where = $condition['str'];
				} else {
					return false;
				}
			}
			if(is_array($condition['data'])) {
				$link = $condition['link'] ? $condition['link'] : 'and';
				list($conSql, $mapConData) = $this->FDFields($condition['data'], $link, $condition['judge']);
				if ($conSql) {
					$where .= ($where ? ' ' . $link : '') . $conSql;
					$mapData = array_merge($mapData, $mapConData);
				}
			}
		}
		return array($where, $mapData);
	}

           

           

           

3. [代码]增删改查的具体实现     

public function fetch($sql, $searchData = array(), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) {
		if ($sql) {
			$sql .= ' limit 1';
			$pdoStatement = $this->pdo->prepare($sql, $preType);
			$pdoStatement->execute($searchData);
			return $data = $pdoStatement->fetch($dataMode);
		} else {
			return false;
		}
	}
	
	public function fetchAll($sql, $searchData = array(), $limit = array(0, 10), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) {
		if ($sql) {
			$sql .= ' limit ' . (int) $limit[0] . ',' . (intval($limit[1]) > 0 ? intval($limit[1]) : 10);
			$pdoStatement = $this->pdo->prepare($sql, $preType);
			$pdoStatement->execute($searchData);
			return $data = $pdoStatement->fetchAll($dataMode);
		} else {
			return false;
		}
	}
	
	public function insert($tableName, $data, $returnInsertId = false, $replace = false) {
		if(!empty($tableName) && count($data) > 0){
			$sql = $replace ? 'REPLACE INTO ' : 'INSERT INTO ';
			list($setSql, $mapData) = $this->FDFields($data);
			$sql .= $tableName . ' set ' . $setSql;
			$pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
			$execRet = $pdoStatement->execute($mapData);
			return $execRet ? ($returnInsertId ? $this->pdo->lastInsertId() : $execRet) : false;
		} else {
			return false;
		}
	}
	
	public function update($tableName, $data, $condition, $mapData = array(), $returnRowCount = true) {
		if(!empty($tableName) && count($data) > 0) {
			$sql = 'UPDATE ' . $tableName . ' SET ';
			list($setSql, $mapSetData) = $this->FDFields($data);
			$sql .= $setSql;
			$mapData = array_merge($mapData, $mapSetData);
			list($where, $mapData) = $this->FDCondition($condition, $mapData);
			$sql .= $where ? ' WHERE ' . $where : '';
			$pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
			$execRet = $pdoStatement->execute($mapData);
			return $execRet ? ($returnRowCount ? $pdoStatement->rowCount() : $execRet) : false;
		} else {
			return false;
		}
	}
	
	public function delete($tableName, $condition, $mapData = array()) {
		if(!empty($tableName) && $condition){
			$sql = 'DELETE FROM ' . $tableName;
			list($where, $mapData) = $this->FDCondition($condition, $mapData);
			$sql .= $where ? ' WHERE ' . $where : '';
			$pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
			$execRet = $pdoStatement->execute($mapData);
			return $execRet;
		}
	}

           

           

           

4. [代码]测试文件     

header("Content-type: text/html; charset=utf-8");
define('APP_DIR', dirname(__FILE__));

if (function_exists('spl_autoload_register')) {
	spl_autoload_register('autoClass');
} else {
	function __auto_load($className){
		autoClass($className);
	}
}

function autoClass($className){
	try{
		require_once APP_DIR.'/class/'.$className.'.php';
	} catch (Exception $e) {
		die('Error:' . $e->getMessage() . '
');
	}
}
$DB = new DB();
//插入
$inData['a'] = rand(1, 100);
$inData['b'] = rand(1, 1000);
$inData['c'] = rand(1,200) . '.' . rand(1,100);
$ret = $DB->insert('a', $inData);
echo '插入' . ($ret ? '成功' : '失败') . '
';
//更新
$upConData['a'] = 100;
$upConJudge['a'] = '

           

           

                   

5. [文件] test.php ~ 2KB        

                

				   				            	            	




	           	


	   				                	



6. [文件]					DB.php ~ 4KB        	



		               	
debugDumpParams() 可以查看sql语句错误
 */
class DB {
	protected $pdo;
	
	public function __construct($dbType = 'mysql'){
		if($dbType == 'mysql') {
			$dsn = "mysql:host=localhost;port=3306;dbname=test";
		}
		$this->pdo = DPDO::instance($dsn, 'root', '');
	}
	
	public function fetch($sql, $searchData = array(), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) {
		if ($sql) {
			$sql .= ' limit 1';
			$pdoStatement = $this->pdo->prepare($sql, $preType);
			$pdoStatement->execute($searchData);
			return $data = $pdoStatement->fetch($dataMode);
		} else {
			return false;
		}
	}
	
	public function fetchAll($sql, $searchData = array(), $limit = array(0, 10), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) {
		if ($sql) {
			$sql .= ' limit ' . (int) $limit[0] . ',' . (intval($limit[1]) > 0 ? intval($limit[1]) : 10);
			$pdoStatement = $this->pdo->prepare($sql, $preType);
			$pdoStatement->execute($searchData);
			return $data = $pdoStatement->fetchAll($dataMode);
		} else {
			return false;
		}
	}
	
	public function insert($tableName, $data, $returnInsertId = false, $replace = false) {
		if(!empty($tableName) && count($data) > 0){
			$sql = $replace ? 'REPLACE INTO ' : 'INSERT INTO ';
			list($setSql, $mapData) = $this->FDFields($data);
			$sql .= $tableName . ' set ' . $setSql;
			$pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
			$execRet = $pdoStatement->execute($mapData);
			return $execRet ? ($returnInsertId ? $this->pdo->lastInsertId() : $execRet) : false;
		} else {
			return false;
		}
	}
	
	public function update($tableName, $data, $condition, $mapData = array(), $returnRowCount = true) {
		if(!empty($tableName) && count($data) > 0) {
			$sql = 'UPDATE ' . $tableName . ' SET ';
			list($setSql, $mapSetData) = $this->FDFields($data);
			$sql .= $setSql;
			$mapData = array_merge($mapData, $mapSetData);
			list($where, $mapData) = $this->FDCondition($condition, $mapData);
			$sql .= $where ? ' WHERE ' . $where : '';
			$pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
			$execRet = $pdoStatement->execute($mapData);
			return $execRet ? ($returnRowCount ? $pdoStatement->rowCount() : $execRet) : false;
		} else {
			return false;
		}
	}
	
	public function delete($tableName, $condition, $mapData = array()) {
		if(!empty($tableName) && $condition){
			$sql = 'DELETE FROM ' . $tableName;
			list($where, $mapData) = $this->FDCondition($condition, $mapData);
			$sql .= $where ? ' WHERE ' . $where : '';
			$pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
			$execRet = $pdoStatement->execute($mapData);
			return $execRet;
		}
	}
	
	
	//字段关联数组处理
	public function FDFields($data, $link = ',', $judge = array(), $aliasTable = ''){
		$sql = '';
		$mapData = array();
		foreach($data as $key => $value) {
			$mapIndex = ':' . ($link != ',' ? 'c' : '') . $aliasTable . $key;
			$sql .= ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $key . '` ' . ($judge[$key] ? $judge[$key] : '=') . ' ' . $mapIndex . ' ' . $link;
			$mapData[$mapIndex] = $value;
		}
		$sql = trim($sql, $link);
		return array($sql, $mapData);
	}
	
	public function FDField($field, $value, $judge = '=', $preMap = 'cn', $aliasTable = '') {
		$mapIndex = ':' . $preMap . $aliasTable . $field;
		$sql = ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $field . '`' . $judge . $mapIndex;
		$mapData[$mapIndex] = $value;
		return array($sql, $mapData);
	}
	
	public function FDCondition($condition, $mapData) {
		if(is_string($condition)) {
				$where = $condition;
		} else if (is_array($condition)) {
			if($condition['str']) {
				if (is_string($condition['str'])) {
					$where = $condition['str'];
				} else {
					return false;
				}
			}
			if(is_array($condition['data'])) {
				$link = $condition['link'] ? $condition['link'] : 'and';
				list($conSql, $mapConData) = $this->FDFields($condition['data'], $link, $condition['judge']);
				if ($conSql) {
					$where .= ($where ? ' ' . $link : '') . $conSql;
					$mapData = array_merge($mapData, $mapConData);
				}
			}
		}
		return array($where, $mapData);
	}
}
?>
				   				            	            	




	           	


	   				                	



7. [文件]					DPDO.php ~ 1KB    
				   				            	            	




	       	





			
陳述
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn
熱AI工具
Undresser.AI Undress
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
Undress AI Tool
免費脫衣圖片
Clothoff.io
Clothoff.io
AI脫衣器
AI Hentai Generator
AI Hentai Generator
免費產生 AI 無盡。
顯示更多
熱門文章
R.E.P.O.能量晶體解釋及其做什麼（黃色晶體）
1 個月前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳圖形設置
1 個月前By尊渡假赌尊渡假赌尊渡假赌
刺客信條陰影：貝殼謎語解決方案
3 週前ByDDD
Windows 11 KB5054979中的新功能以及如何解決更新問題
2 週前ByDDD
威爾R.E.P.O.有交叉遊戲嗎？
1 個月前By尊渡假赌尊渡假赌尊渡假赌
顯示更多
熱工具
PhpStorm Mac 版本
PhpStorm Mac 版本
最新（2018.2.1 ）專業的PHP整合開發工具
SublimeText3 Linux新版
SublimeText3 Linux新版
SublimeText3 Linux最新版
VSCode Windows 64位元 下載
VSCode Windows 64位元 下載
微軟推出的免費、功能強大的一款IDE編輯器
ZendStudio 13.5.1 Mac
ZendStudio 13.5.1 Mac
強大的PHP整合開發環境
記事本++7.3.1
記事本++7.3.1
好用且免費的程式碼編輯器
顯示更多
熱門話題
gmail信箱登陸入口在哪裡
7559
15
CakePHP 教程
1384
52
steam的賬戶名稱是什麼格式
84
11
win11激活密鑰永久
59
19
NYT連接提示和答案
28
98
顯示更多