PDO的封装

PHP PDO封装的增 删 改 查

<?php 
class Mysql{
	static private $db; //数据引擎对象
	private $pdo;	//pdo 引擎对象
	private $table; //表名
	private $where = array("1=1");//条件
	private $data;//源数据


	//定义受保护的构造方法禁止外部使用
	private function __construct()
	{
		$this->pdo = new PDO("mysql:host=127.0.0.1;dbname=book",'root','');
		//$this->pdo->query("set names utf8");
	}


	//初始化mysql类、在类内部实例化mysql类
	static public function init(){
		//自身调用需使用self::调用自身、判断是否被实例化
		if(!self::$db){
			self::$db = new Mysql(); 
		}
		//已被实例化直接返回、外部即可直接使用
		return self::$db;
	}
	//原生SQL语句 直接执行；
	public function query($sql) 
	{
		//echo $sql.'<br>';
		$ch =  $this->pdo->query($sql);


		return $ch;
	}




	//查询多条
	public function select($table='',$data=array(),$limit=array()){
		//表名
		if(!empty($table)) $this->table($table);
		//条件
		if(!empty($data)) $this->where($data);


		//拼接where条件
		$where = implode(' AND ',$this->where);


		$limit_str = '';
		//偏移量
		if($limit){
			$limit_str = ' LIMIT '.$limit[0].','.$limit[1]; 
		}
		$sql = 'SELECT * FROM '.$this->table.' WHERE '.$where.$limit_str;
		//echo $sql;
		//绑定参数
		//dump($this->data);
		$item = $this->bind($sql,$this->data);
		//dump($item);
		$item->setFetchMode(PDO::FETCH_ASSOC);
		$data = $item->fetchAll();
		return $data;
	}


	//添加
	public function add($table,$data=array()) 
	{
		//获取添加的表名
		$this->table = $table;
		//echo $this->table;die;
		$data = $this->redun($data);
       // dump($data);
		//循环数据 生成 $key , $val ;
		$val = '';
		$key = '';
		foreach ($data as $k => $v) {
			$key .= $k.',';
			$val .= ':'.$k.',';
		}
		$val = trim($val,',');
		$key = trim($key,',');
		
		$sql = "INSERT INTO ".$table. '('.$key.') VALUES ('.$val.')';
        //echo $sql;die;
		//绑定参数
		$this->bind($sql,$data);


		//获取新插入的id
		return $this->pdo->lastInsertId();


	}
	public function del($table,$data=array()){
		//获取删除的表名
		$this->table = $table;


		$field=$this->field();


		$data = $this->redun($data);


		$sql = "delete from ".$table." where ". $field[0] ." in ( :".$field[0] .')';
		//绑定参数
		$ch = $this->bind($sql,$data);
		return $ch->rowCount();
		
	}


	public function save($table,$data=array()){
		//获取修改的表名
		$this->table = $table;
		//
		$data = $this->redun($data);


		$field=$this->field();


		//拼接where条件
		if(isset($data[$field[0]])){
			$where = ' where '.$field[0].' = :'.$field[0];
		}
		
		$sql = 'UPDATE '.$this->table . ' SET ';


		foreach ($data as $k => $v) {
			if ($k == $field[0]) continue;


				$sql .= $k ." = :".$k.",";
		}
		//echo $sql;
		$sql = trim($sql,',').$where;
		//绑定参数
		
		 $ch = $this->bind($sql,$data);
		 return $ch->rowCount();
		
	}


	//where条件
	public function where($data = array()){
		if (is_array($data)) {
			//把数据存入 
			$this->data = $data;


			$this->where[] = " 1=1 ";


			foreach ($data as $key => $val){
				$this->where[] = $key.'=:'.$key;
			}
		}
		if (is_string($data) && !empty($data)) {
			$arr = explode(' ',$data);
			$val  = trim($arr[2] , "'");


			$this->where[] = $arr[0].' '.$arr[1].' :'.$arr[0];


			$this->data[$arr[0]] = $val;
		}
		
		return $this;
	}


	//表名
	public function table($table = ''){
		$this->table = $table;
		return $this;
	}


	public function bind($sql, $data=array()) {
	$ch= $this->pdo->prepare($sql);
	if(!empty ($data)){
		foreach($data as $key=>$val){
			$ch->bindValue(':'.$key, $val);
			}
		}
	//执行返回状态
	//dump($sql);
	$ch->execute();
	//$ch->debugDumpParams();
	return $ch;
	}


	//查询表中字段
	public function field(){
        //dump($this->pdo);
		$ch = $this->pdo->query('desc '.$this->table);


		//echo $this->table;
		$data = $ch->fetchAll(PDO::FETCH_ASSOC);


		$arr = array();
		foreach($data as $val){
			$arr[] = $val['Field'];
		}
		return $arr;


	}


	public function redun($data=array()) {
		//获取表字段
		$field=$this->field();
		//循环去除重复字段
		foreach($data as $key=>$val){
			if(!in_array($key, $field)) {
				unset ($data[$key]);
			}
		}
		return $data;
	}
}