PDO链式调用的封装类-PHP源码

1. [代码][PHP]代码

 &＃39;127.0.0.1&＃39;,
		&＃39;username&＃39; => &＃39;root&＃39;,
		&＃39;password&＃39; => &＃39;&＃39;,
		&＃39;database&＃39; => &＃39;test&＃39;,
		&＃39;charset&＃39; => &＃39;utf8&＃39;,
		&＃39;prefix&＃39; => &＃39;&＃39;,
		&＃39;persistent&＃39; => false,
		&＃39;debug&＃39;=>true
));

//多行插入
$db->Insert( &＃39;t&＃39;, array (
		array (
				&＃39;cid&＃39; => $cid,
				&＃39;content&＃39; => "c1" 
		),
		array (
				&＃39;cid&＃39; => $cid,
				&＃39;content&＃39; => "c2" 
		) 
) )
->Execute();

//单行插入并获取id
$id = $db->Insert( &＃39;t&＃39;, array (
		&＃39;cid&＃39; => $cid,
		&＃39;content&＃39; => $content
) )
->LastId();

//查询1：最简查询
$result = $db->Select( &＃39;t&＃39; )->FetchAll();

//查询2：带条件查询
$result = $db->Select( &＃39;t&＃39;, array (	&＃39;id&＃39;, &＃39;cid&＃39;, &＃39;content&＃39;) )
->Where( &＃39;cid=? and id>?&＃39;, array ($cid, $id) )
->Order( &＃39;id desc&＃39; )
->Limit( 1 )
->FetchRow();

//查询3：in用法
$where_data[] = $cid;
$ids = array(1,2,3);
$where_data += $ids;
$result = $db->Select( &＃39;t&＃39; )
->Where( &＃39;cid=? and id in(?)&＃39;, $where_data )
->FetchAll();

//更新
$count = $db->Update( &＃39;t&＃39;, array (
		&＃39;id&＃39; => $id,
		&＃39;cid&＃39; => $cid,
		&＃39;content&＃39; => $content
) )
->Where( &＃39;id=?&＃39;, $id )
->AffectedRows();

//删除
$count = $db->Delete( &＃39;t&＃39; )->Where( &＃39;id=?&＃39;, $id )->AffectedRows();

//sql语句查询
$result = $db->Sql( &＃39;select * from `_t` where id>?&＃39;, $id )->FetchAll();

//通过自定义来使用事务
$pdo = $db->GetConnecttion();
$pdo->beginTransaction();
...

*/
class PDOHelper
{
	protected $mConnecttion;
	protected $mPrefix;
	protected $mDebug;
	protected $mQueryType;
	protected $mSql;
	protected $mWhere;
	protected $mOrder;
	protected $mLimit;
	protected $mData;
	protected $mPDOStatement;
	/**
	 * 构造方法
	 * 
	 * @param array $config        	
	 */
	function __construct($config)
	{
		$this->mDebug = empty( $config[&＃39;debug&＃39;] ) ? false : true;
		$this->mPrefix = isset( $config[&＃39;prefix&＃39;] ) ? $config[&＃39;prefix&＃39;] : &＃39;&＃39;;
		$dsn = &＃39;mysql:host=&＃39; . $config[&＃39;host&＃39;] . &＃39;;dbname=&＃39; . $config[&＃39;database&＃39;];
		try
		{
			$this->mCOnnecttion= new PDO( $dsn, $config[&＃39;username&＃39;], $config[&＃39;password&＃39;], array (
					PDO::ATTR_PERSISTENT => empty( $config[&＃39;persistent&＃39;] ) ? false : true 
			) );
		}
		catch ( PDOException $e )
		{
			$this->Err( &＃39;Connect failed
&＃39; );
		}
		if ($this->mConnecttion)
		{
			// $this->mConnecttion->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
			$this->mConnecttion->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
			$this->mConnecttion->setAttribute( PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC );
			$charset = isset( $config[&＃39;charset&＃39;] ) ? $config[&＃39;charset&＃39;] : &＃39;utf8&＃39;;
			// $charset = strtolower( str_replace( &＃39;-&＃39;, &＃39;&＃39;, $charset ) );
			// if (! in_array( $charset, array (&＃39;utf8&＃39;,&＃39;gbk&＃39;) ))
			// {
			//     $charset = &＃39;utf8&＃39;;
			// }
			$this->mConnecttion->exec( "SET NAMES $charset" );
		}
	}
	/**
	 * 获取PDO实例，以便自己实现复杂查询
	 * 
	 * @return PDO
	 */
	function GetConnecttion()
	{
		return $this->mConnecttion;
	}
	/**
	 * 初始化链式调用的缓存
	 */
	private function Init()
	{
		$this->mQueryType = &＃39;&＃39;;
		$this->mSql = &＃39;&＃39;;
		$this->mWhere = &＃39;&＃39;;
		$this->mOrder = &＃39;&＃39;;
		$this->mLimit = &＃39;&＃39;;
		$this->mData = array ();
	}
	/**
	 * 查询链Select部分
	 * 
	 * @param string $talbe        	
	 * @param string|array $field        	
	 * @return PDOHelper
	 */
	function Select($talbe, $field = &＃39;*&＃39;)
	{
		$this->Init();
		$this->mQueryType = &＃39;s&＃39;;
		$field_str = is_array( $field ) ? &＃39;`&＃39; . implode( &＃39;`,`&＃39;, $field ) . &＃39;`&＃39; : $field;
		$this->mSql = &＃39;SELECT &＃39; . $field_str . &＃39; FROM `&＃39; . $this->mPrefix . $talbe . &＃39;`&＃39;;
		return $this;
	}
	/**
	 * 查询链Insert部分
	 * 
	 * @param string $talbe        	
	 * @param array $data        	
	 * @return PDOHelper
	 */
	function Insert($talbe, $data)
	{
		$this->Init();
		$first = current( $data );
		if (is_array( $first ))
		{
			// 多行插入
			$fields = array_keys( $first );
			$values = substr( str_repeat( &＃39;?,&＃39;, count( $fields ) ), 0, - 1 );
			$values_all = substr( str_repeat( &＃39;(&＃39; . $values . &＃39;),&＃39;, count( $data ) ), 0, - 1 );
			$this->mSql = &＃39;INSERT INTO `&＃39; . $this->mPrefix . $talbe . &＃39;`(`&＃39; . implode( &＃39;`,`&＃39;, $fields ) . &＃39;`) VALUES&＃39; . $values_all;
			foreach ( $this->mData as $item )
			{
				$this->mData += $item;
			}
		}
		else
		{
			// 单行插入
			$fields = array_keys( $data );
			$values = substr( str_repeat( &＃39;?,&＃39;, count( $fields ) ), 0, - 1 );
			$this->mSql = &＃39;INSERT INTO `&＃39; . $this->mPrefix . $talbe . &＃39;`(`&＃39; . implode( &＃39;`,`&＃39;, $fields ) . &＃39;`) VALUES(&＃39; . $values . &＃39;)&＃39;;
			$this->mData = $data;
		}
		return $this;
	}
	/**
	 * 查询链Update部分
	 * 
	 * @param string $talbe        	
	 * @param array $data        	
	 * @return PDOHelper
	 */
	function Update($talbe, $data)
	{
		$this->Init();
		$this->mQueryType = &＃39;u&＃39;;
		$fields = array_keys( $data );
		$this->mSql = &＃39;UPDATE `&＃39; . $this->mPrefix . $talbe . &＃39;` SET &＃39; . implode( &＃39;=?,&＃39;, $fields ) . &＃39;=?&＃39;;
		$this->mData = $data;
		return $this;
	}
	/**
	 * 查询链Delete部分
	 * 
	 * @param string $talbe        	
	 * @return PDOHelper
	 */
	function Delete($talbe)
	{
		$this->Init();
		$this->mQueryType = &＃39;d&＃39;;
		$this->mSql = &＃39;DELETE FROM `&＃39; . $this->mPrefix . $talbe . &＃39;`&＃39;;
		return $this;
	}
	/**
	 * 查询链Where部分
	 * 
	 * @param string $str        	
	 * @param mixed $parameter        	
	 * @return PDOHelper
	 */
	function Where($str, $parameter = null)
	{
		if ($parameter !== null)
		{
			if (is_array( $parameter ))
			{
				$this->mData += $parameter;
				// 根据实际传递的参数数目，替换in语句中的？，只能有一个in语句
				$c1 = substr_count( $str, &＃39;?&＃39; );
				$c2 = count( $parameter );
				$replace = &＃39;in(&＃39; . substr( str_repeat( &＃39;?,&＃39;, $c2 - $c1 + 1 ), 0, - 1 ) . &＃39;)&＃39;;
				$str = str_replace( &＃39;in(?)&＃39;, $replace, $str );
			}
			else
			{
				$this->mData[] = $parameter;
			}
		}
		$this->mWhere = " WHERE $str";
		return $this;
	}
	/**
	 * 查询链Order部分
	 * 
	 * @param string $str        	
	 * @return PDOHelper
	 */
	function Order($str)
	{
		$this->mOrder = " ORDER BY $str";
		return $this;
	}
	/**
	 * 查询链Limit部分
	 * 
	 * @param number $length        	
	 * @param number $begin        	
	 * @return PDOHelper
	 */
	function Limit($length = 10, $begin = 0)
	{
		$this->mLimit = " LIMIT $begin,$length";
		return $this;
	}
	/**
	 * 直接Sql语句查询
	 * 
	 * @param string $sql        	
	 * @param mixed $parameter        	
	 * @return PDOHelper
	 */
	function Sql($sql, $parameter = null)
	{
		$this->Init();
		if ($parameter !== null)
		{
			if (is_array( $parameter ))
			{
				$this->mData = $parameter;
				// 根据实际传递的参数数目，替换in语句中的？，只能有一个in语句
				$c1 = substr_count( $sql, &＃39;?&＃39; );
				$c2 = count( $parameter );
				$replace = &＃39;in(&＃39; . substr( str_repeat( &＃39;?,&＃39;, $c2 - $c1 + 1 ), 0, - 1 ) . &＃39;)&＃39;;
				$sql = str_replace( &＃39;in(?)&＃39;, $replace, $sql );
			}
			else
			{
				$this->mData[] = $parameter;
			}
		}
		// 自动为表名加前缀，需要时，请在表名前面加下划线并用反单引号括起来
		$sql = str_replace( &＃39;`_&＃39;, &＃39;`&＃39; . $this->mPrefix, $sql );
		$this->mSql = $sql;
		return $this;
	}
	/**
	 * 执行查询
	 * 
	 * @return boolean
	 */
	function Execute()
	{
		if ($this->mConnecttion)
		{
			switch ($this->mQueryType)
			{
				case &＃39;s&＃39; :
					$this->mSql .= $this->mWhere . $this->mOrder . $this->mLimit;
					break;
				case &＃39;u&＃39; :
					$this->mSql .= $this->mWhere;
					break;
				case &＃39;d&＃39; :
					$this->mSql .= $this->mWhere;
					break;
			}
			//var_dump( $this->mSql );
			//echo &＃39;
&＃39;;
			if (empty( $this->mSql ))
			{
				$this->Err( &＃39;Can not find SQL statement
&＃39; );
				return false;
			}			
			if ($this->mPDOStatement = $this->mConnecttion->prepare( $this->mSql ))
			{
				$i = 1;
				foreach ( $this->mData as $data )
				{
					// echo "<<$i:$data>>
";
					if (! $this->mPDOStatement->bindValue( $i, $data ))
					{
						$this->Err( &＃39;Error: PDOStatement::bindValue() &＃39; . $i . &＃39;/&＃39; . count( $this->mData ) . &＃39;
&＃39; );
						return false;
					}
					++ $i;
				}
				if ($this->mPDOStatement->execute())
				{
					return true;
				}
				$this->Err( &＃39;Error: PDOStatement::execute()
&＃39; );
				return false;
			}
			$this->Err( &＃39;Error: PDOStatement::prepare()
&＃39; );
		}
		return false;
	}
	/**
	 * 返回数据列表的二维关联数组
	 * 
	 * @return array(array{}) | empty array | false
	 */
	function FetchAll()
	{
		if ($this->Execute())
		{
			return $this->mPDOStatement->fetchAll();
		}
		else
		{
			return false;
		}
	}
	/**
	 * 返回数据行的一维关联数组
	 * 
	 * @return array{} | empty array | false
	 */
	function FetchRow()
	{
		if ($this->Execute())
		{
			$rs = $this->mPDOStatement->fetch();
			return $rs === false ? array () : $rs;
		}
		else
		{
			return false;
		}
	}
	/**
	 * 返回第1行第1列的值
	 * 
	 * @return mixed | false
	 */
	function FetchCell()
	{
		if ($this->Execute())
		{
			$rs = $this->mPDOStatement->fetchColumn();
			return $rs === false ? null : $rs;
		}
		else
		{
			return false;
		}
	}
	/**
	 * 返回插入数据的id
	 * 
	 * @return string boolean
	 */
	function LastId()
	{
		if ($this->Execute())
		{
			return $this->mConnecttion->lastInsertId();
		}
		else
		{
			return false;
		}
	}
	/**
	 * 返回实际受影响的行数
	 * 
	 * @return number boolean
	 */
	function AffectedRows()
	{
		if ($this->Execute())
		{
			return $this->mPDOStatement->rowCount();
		}
		else
		{
			return false;
		}
	}
	/**
	 * 调试模式下，显示错误信息
	 * 
	 * @param string $msg        	
	 */
	private function Err($msg)
	{
		if ($this->mDebug)
		{
			echo $msg;
		}
	}
}

2. [代码]更新说明

/*
1. 增加对多行插入的支持
2. 增加in语句参数的自动替换
3. 增加注释以及调试模式下的提示信息
4. Submit方法改名为Execute
5. 内部语法结构优化
 */