欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  php教程

PDO链式调用的封装类

程序员文章站 2022-04-10 12:22:54
...


1. [代码][PHP]代码

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

//实例化对象并传人数据库配置信息
$db = new PDOHelper (array (
		'host' => '127.0.0.1',
		'username' => 'root',
		'password' => '',
		'database' => 'test',
		'charset' => 'utf8',
		'prefix' => '',
		'persistent' => false,
		'debug'=>true
));

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

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

//查询1:最简查询
$result = $db->Select( 't' )->FetchAll();

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

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

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

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

//sql语句查询
$result = $db->Sql( 'select * from `_t` where id>?', $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['debug'] ) ? false : true;
		$this->mPrefix = isset( $config['prefix'] ) ? $config['prefix'] : '';
		$dsn = 'mysql:host=' . $config['host'] . ';dbname=' . $config['database'];
		try
		{
			$this->mConnecttion = new PDO( $dsn, $config['username'], $config['password'], array (
					PDO::ATTR_PERSISTENT => empty( $config['persistent'] ) ? false : true 
			) );
		}
		catch ( PDOException $e )
		{
			$this->Err( 'Connect failed<br/>' );
		}
		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['charset'] ) ? $config['charset'] : 'utf8';
			// $charset = strtolower( str_replace( '-', '', $charset ) );
			// if (! in_array( $charset, array ('utf8','gbk') ))
			// {
			//     $charset = 'utf8';
			// }
			$this->mConnecttion->exec( "SET NAMES $charset" );
		}
	}
	/**
	 * 获取PDO实例,以便自己实现复杂查询
	 * 
	 * @return PDO
	 */
	function GetConnecttion()
	{
		return $this->mConnecttion;
	}
	/**
	 * 初始化链式调用的缓存
	 */
	private function Init()
	{
		$this->mQueryType = '';
		$this->mSql = '';
		$this->mWhere = '';
		$this->mOrder = '';
		$this->mLimit = '';
		$this->mData = array ();
	}
	/**
	 * 查询链Select部分
	 * 
	 * @param string $talbe        	
	 * @param string|array $field        	
	 * @return PDOHelper
	 */
	function Select($talbe, $field = '*')
	{
		$this->Init();
		$this->mQueryType = 's';
		$field_str = is_array( $field ) ? '`' . implode( '`,`', $field ) . '`' : $field;
		$this->mSql = 'SELECT ' . $field_str . ' FROM `' . $this->mPrefix . $talbe . '`';
		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( '?,', count( $fields ) ), 0, - 1 );
			$values_all = substr( str_repeat( '(' . $values . '),', count( $data ) ), 0, - 1 );
			$this->mSql = 'INSERT INTO `' . $this->mPrefix . $talbe . '`(`' . implode( '`,`', $fields ) . '`) VALUES' . $values_all;
			foreach ( $this->mData as $item )
			{
				$this->mData += $item;
			}
		}
		else
		{
			// 单行插入
			$fields = array_keys( $data );
			$values = substr( str_repeat( '?,', count( $fields ) ), 0, - 1 );
			$this->mSql = 'INSERT INTO `' . $this->mPrefix . $talbe . '`(`' . implode( '`,`', $fields ) . '`) VALUES(' . $values . ')';
			$this->mData = $data;
		}
		return $this;
	}
	/**
	 * 查询链Update部分
	 * 
	 * @param string $talbe        	
	 * @param array $data        	
	 * @return PDOHelper
	 */
	function Update($talbe, $data)
	{
		$this->Init();
		$this->mQueryType = 'u';
		$fields = array_keys( $data );
		$this->mSql = 'UPDATE `' . $this->mPrefix . $talbe . '` SET ' . implode( '=?,', $fields ) . '=?';
		$this->mData = $data;
		return $this;
	}
	/**
	 * 查询链Delete部分
	 * 
	 * @param string $talbe        	
	 * @return PDOHelper
	 */
	function Delete($talbe)
	{
		$this->Init();
		$this->mQueryType = 'd';
		$this->mSql = 'DELETE FROM `' . $this->mPrefix . $talbe . '`';
		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, '?' );
				$c2 = count( $parameter );
				$replace = 'in(' . substr( str_repeat( '?,', $c2 - $c1 + 1 ), 0, - 1 ) . ')';
				$str = str_replace( 'in(?)', $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, '?' );
				$c2 = count( $parameter );
				$replace = 'in(' . substr( str_repeat( '?,', $c2 - $c1 + 1 ), 0, - 1 ) . ')';
				$sql = str_replace( 'in(?)', $replace, $sql );
			}
			else
			{
				$this->mData[] = $parameter;
			}
		}
		// 自动为表名加前缀,需要时,请在表名前面加下划线并用反单引号括起来
		$sql = str_replace( '`_', '`' . $this->mPrefix, $sql );
		$this->mSql = $sql;
		return $this;
	}
	/**
	 * 执行查询
	 * 
	 * @return boolean
	 */
	function Execute()
	{
		if ($this->mConnecttion)
		{
			switch ($this->mQueryType)
			{
				case 's' :
					$this->mSql .= $this->mWhere . $this->mOrder . $this->mLimit;
					break;
				case 'u' :
					$this->mSql .= $this->mWhere;
					break;
				case 'd' :
					$this->mSql .= $this->mWhere;
					break;
			}
			//var_dump( $this->mSql );
			//echo '<br/>';
			if (empty( $this->mSql ))
			{
				$this->Err( 'Can not find SQL statement<br/>' );
				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( 'Error: PDOStatement::bindValue() ' . $i . '/' . count( $this->mData ) . '<br/>' );
						return false;
					}
					++ $i;
				}
				if ($this->mPDOStatement->execute())
				{
					return true;
				}
				$this->Err( 'Error: PDOStatement::execute()<br/>' );
				return false;
			}
			$this->Err( 'Error: PDOStatement::prepare()<br/>' );
		}
		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. 内部语法结构优化
 */