PDO链式调用的封装类
程序员文章站
2022-04-15 21:43:05
...
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. 内部语法结构优化 */