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();
/
 finalclass 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);
    }
}
    

原文：http://www.cnblogs.com/lonelyxmas/p/4220634.html