当前位置:首页 >> 计算机软件及应用 >>

php中PDO方式实现数据库的增删改查


php中PDO方式实现数据库的增删改查
PDO是mysql数据库操作的一个公用类了,我们不需要进行自定类就可以直接使用pdo来操作数据库了,但是在php默认配置中pdo是未 开启所以我们必须先在php.ini中开启它才可以使用。 需要开启php的pdo支持,php5.1以上版本支持 实现数据库连接单例化,有三要素 静态变量、静态实例化方法、私有构造函数 DPDO.php ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
class DPDO{ private $DSN ; private $DBUser ; private $DBPwd ; private $longLink ; private $pdo ; //私有构造函数 防止被直接实例化 private function __construct( $dsn , $DBUser , $DBPwd , $longLink = false) { $this ->DSN = $dsn ; $this ->DBUser = $DBUser ; $this ->DBPwd = $DBPwd ; $this ->longLink = $longLink ; $this ->connect(); } //私有 空克隆函数 防止被克隆 private function __clone(){} //静态 实例化函数 返回一个pdo对象 static public function instance( $dsn , $DBUser , $DBPwd , $longLink = false){ static $singleton = array (); //静态函数 用于存储实例化对象 $singIndex = md5( $dsn . $DBUser . $DBPwd . $longLink ); if ( empty ( $singleton [ $singIndex ])) { $singleton [ $singIndex ] = new self( $dsn , $DBUser , $DBPwd , $longLink = false); } return $singleton [ $singIndex ]->pdo; } private function connect(){ try { if ( $this ->longLink){ $this ->pdo = new PDO( $this ->DSN, $this ->DBUser, $this ->DBPwd, array (PDO::ATTR_PERSISTENT => true)); } else { $this ->pdo = new PDO( $this ->DSN, $this ->DBUser, $this ->DBPwd); } $this ->pdo->query( 'SET NAMES UTF-8' ); } catch (PDOException $e ) { die ( 'Error:' . $e ->getMessage() . '<br/>' ); } } }

用于处理字段映射,使用pdo的字段映射,可以有效避免sql注入 ?

1 2 3 4 5 6 7 8 9 1 0 1 1 1 2 1 3 1 4 1 5 1 6 1 7 1 8 1 9 2 0 2 1 2 2 2 3 2 4 2 5 2 6 2 7 2 8 2 9 3 0 3 1 3 2 3 3 3 4 3 5 3 6 3 7 3 8

//字段关联数组处理, 主要用于写入和更新数据、同and 或 or 的查询条件,产生sql语句和映射字段的数组 public function FDFields( $data , $link = ',' , $judge = array (), $aliasTable = '' ){ $sql = '' ; $mapData = array (); foreach ( $data as $key => $value ) { $mapIndex = ':' . ( $link != ',' ? 'c' : '' ) . $aliasTable . $key ; $sql .= ' ' . ( $aliasTable ? $aliasTable . '.' : '' ) . '`' . $key . '` ' . ( $judge [ $key ] ? $judge [ $key ] : '=' ) . ' ' . $mapIndex . ' ' . $link ; $mapData [ $mapIndex ] = $value ; } $sql = trim( $sql , $link ); return array ( $sql , $mapData ); } //用于处理单个字段处理 public function FDField( $field , $value , $judge = '=' , $preMap = 'cn' , $aliasTable = '' ) { $mapIndex = ':' . $preMap . $aliasTable . $field ; $sql = ' ' . ( $aliasTable ? $aliasTable . '.' : '' ) . '`' . $field . '`' . $judge . $mapIndex ; $mapData [ $mapIndex ] = $value ; return array ( $sql , $mapData ); } //使用刚方法可以便捷产生查询条件及对应数据数组 public function FDCondition( $condition , $mapData ) { if ( is_string ( $condition )) { $where = $condition ; } else if ( is_array ( $condition )) { if ( $condition [ 'str' ]) { if ( is_string ( $condition [ 'str' ])) { $where = $condition [ 'str' ]; } else { return false; } } if ( is_array ( $condition [ 'data' ])) { $link = $condition [ 'link' ] ? $condition [ 'link' ] : 'and' ; list( $conSql , $mapConData ) = $this ->FDFields( $condition [ 'data' ], $link , $condition [ 'judge' ]); if ( $conSql ) { $where .= ( $where ? ' ' . $link : '' ) . $conSql ; $mapData = array_merge ( $mapData , $mapConData ); } } } return array ( $where , $mapData ); }

3 9 4 0 4 1 4 2 增删改查的具体实现DB.php ?

1 2 3 4 5 6 7 8 9 1 0 1 1 1 2 1 3 1 4 1 5 1 6 1 7 1 8 1 9 2 0 2 1 2 2 2 3 2 4 2 5 2 6 2 7 2 8 2 9 3 0 3 1 3 2 3 3 3 4 3 5 3 6 3 7 3 8

public function fetch( $sql , $searchData = array (), $dataMode = PDO::FETCH_ASSOC, $preType = array (PDO::ATTR_CURSO R => PDO::CURSOR_FWDONLY)) { if ( $sql ) { $sql .= ' limit 1' ; $pdoStatement = $this ->pdo->prepare( $sql , $preType ); $pdoStatement ->execute( $searchData ); return $data = $pdoStatement ->fetch( $dataMode ); } else { return false; } } public function fetchAll( $sql , $searchData = array (), $limit = array (0, 10), $dataMode = PDO::FETCH_ASSOC, $pr eType = array (PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) { if ( $sql ) { $sql .= ' limit ' . (int) $limit [0] . ',' . ( intval ( $limit [1]) > 0 ? intval ( $limit [1]) : 10); $pdoStatement = $this ->pdo->prepare( $sql , $preType ); $pdoStatement ->execute( $searchData ); return $data = $pdoStatement ->fetchAll( $dataMode ); } else { return false; } } public function insert( $tableName , $data , $returnInsertId = false, $replace = false) { if (! empty ( $tableName ) && count ( $data ) > 0){ $sql = $replace ? 'REPLACE INTO ' : 'INSERT INTO ' ; list( $setSql , $mapData ) = $this ->FDFields( $data ); $sql .= $tableName . ' set ' . $setSql ; $pdoStatement = $this ->pdo->prepare( $sql , array (PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)); $execRet = $pdoStatement ->execute( $mapData ); return $execRet ? ( $returnInsertId ? $this ->pdo->lastInsertId() : $execRet ) : false; } else { return false; } } public function update( $tableName , $data , $condition , $mapData = array (), $returnRowCount = true) { if (! empty ( $tableName ) && count ( $data ) > 0) { $sql = 'UPDATE ' . $tableName . ' SET ' ; list( $setSql , $mapSetData ) = $this ->FDFields( $data ); $sql .= $setSql ; $mapData = array_merge ( $mapData , $mapSetData );

3 9 4 0 4 1 4 2 4 3 4 4 4 5 4 6 4 7 4 8 4 9 5 0 5 1 5 2 5 3 5 4 5 5 5 6 5 7 5 8 5 9 6 0 6 1 ?

list( $where , $mapData ) = $this ->FDCondition( $condition , $mapData ); $sql .= $where ? ' WHERE ' . $where : '' ; $pdoStatement = $this ->pdo->prepare( $sql , array (PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)); $execRet = $pdoStatement ->execute( $mapData ); return $execRet ? ( $returnRowCount ? $pdoStatement ->rowCount() : $execRet ) : false; } else { return false; } } public function delete ( $tableName , $condition , $mapData = array ()) { if (! empty ( $tableName ) && $condition ){ $sql = 'DELETE FROM ' . $tableName ; list( $where , $mapData ) = $this ->FDCondition( $condition , $mapData ); $sql .= $where ? ' WHERE ' . $where : '' ; $pdoStatement = $this ->pdo->prepare( $sql , array (PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)); $execRet = $pdoStatement ->execute( $mapData ); return $execRet ; } }

测试文件test.php

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

header( "Content-type: text/html; charset=utf-8" ); define( 'APP_DIR' , dirname( __FILE__ )); if (function_exists( 'spl_autoload_register' )) { spl_autoload_register( 'autoClass' ); } else { function __auto_load( $className ){ autoClass( $className ); } } function autoClass( $className ){ try { require_once APP_DIR. '/class/' . $className . '.php' ; } catch (Exception $e ) { die ( 'Error:' . $e ->getMessage() . '<br />' ); } } $DB = new DB(); //插入 $inData [ 'a' ] = rand(1, 100); $inData [ 'b' ] = rand(1, 1000); $inData [ 'c' ] = rand(1,200) . '.' . rand(1,100); $ret = $DB ->insert( 'a' , $inData ); echo '插入' . ( $ret ? '成功' : '失败' ) . '<br/>' ; //更新 $upConData [ 'a' ] = 100; $upConJudge [ 'a' ] = '<' ; $upConData [ 'b' ] = 30; $upConJudge [ 'b' ] = '>' ; list( $upConStr , $mapUpConData ) = $DB ->FDField( 'b' , 200, '<' , 'gt' ); $condition = array ( 'str' => $upConStr , 'data' => $upConData , 'judge' => $upConJudge , 'link' => 'and' ); $upData [ 'a' ] = rand(1, 10); $upData [ 'b' ] = 1; $upData [ 'c' ] = 1.00; $changeRows = $DB ->update( 'a' , $upData , $condition , $mapUpConData ); echo '更新行数:' . (int) $changeRows . '<br/>' ; //删除 $delVal = rand(1, 10); list( $delCon , $mapDelCon ) = $DB ->FDField( 'a' , $delVal ); $delRet = $DB -> delete ( 'a' , $delCon , $mapDelCon ); echo '删除a=' . $delVal . ( $delRet ? '成功' : '失败' ) . '<br/>' ; //查询 $data [ 'a' ] = '10' ; $judge [ 'a' ] = '>' ; $data [ 'b' ] = '400' ; $judge [ 'b' ] = '<' ; list( $conSql , $mapConData ) = $DB ->FDFields( $data , 'and' , $judge ); $mData = $DB ->fetch( 'select * from a where ' . $conSql . ' order by `a` desc' , $mapConData ); var_dump( $mData );


赞助商链接
相关文章:
更多相关文章: