PDO链式调用的封装类

                       

1. [代码][PHP]代码   

           

<?php
/**
 * PDO链式调用的封装
 * 使用预处理方式真正防止SQL注入
 * 简化常用查询
 * 要使用PDO高级功能， 可以通过GetConnecttion()返回PDO对象自己实现
 * @author Paddy
 * @link http://www.php.cn/
 * @version 1.1
 */
/*
USAGE:

//实例化对象并传人数据库配置信息
$db = new PDOHelper (array (
		&#39;host&#39; => &#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<br/>&#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;<br/>&#39;;
			if (empty( $this->mSql ))
			{
				$this->Err( &#39;Can not find SQL statement<br/>&#39; );
				return false;
			}			
			if ($this->mPDOStatement = $this->mConnecttion->prepare( $this->mSql ))
			{
				$i = 1;
				foreach ( $this->mData as $data )
				{
					// echo "<<$i:$data>><br/>";
					if (! $this->mPDOStatement->bindValue( $i, $data ))
					{
						$this->Err( &#39;Error: PDOStatement::bindValue() &#39; . $i . &#39;/&#39; . count( $this->mData ) . &#39;<br/>&#39; );
						return false;
					}
					++ $i;
				}
				if ($this->mPDOStatement->execute())
				{
					return true;
				}
				$this->Err( &#39;Error: PDOStatement::execute()<br/>&#39; );
				return false;
			}
			$this->Err( &#39;Error: PDOStatement::prepare()<br/>&#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. 内部语法结构优化
 */