php中PDO方式实现数据库的增删改查
发布:smiling 来源: PHP粉丝网 添加日期:2021-05-26 22:08:23 浏览: 评论:0
PDO是mysql数据库操作的一个公用类了,我们不需要进行自定类就可以直接使用pdo来操作数据库了,但是在php默认配置中pdo是未开启所以我们必须先在php.ini中开启它才可以使用。
需要开启php的pdo支持,php5.1以上版本支持
实现数据库连接单例化,有三要素 静态变量、静态实例化方法、私有构造函数 DPDO.php
- 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 (emptyempty($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注入
- //字段关联数组处理, 主要用于写入和更新数据、同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);
- }
增删改查的具体实现DB.php
- public function fetch($sql, $searchData = array(), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => 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, $preType = 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(!emptyempty($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(!emptyempty($tableName) && count($data) > 0) {
- $sql = 'UPDATE ' . $tableName . ' SET ';
- list($setSql, $mapSetData) = $this->FDFields($data);
- $sql .= $setSql;
- $mapData = array_merge($mapData, $mapSetData);
- 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(!emptyempty($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
- 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);
以上所述就是本文的全部内容了,希望大家能够喜欢。
Tags: PDO php数据库增删改查
相关文章
- ·PDO版本问题 Invalid parameter number: no parameters were bound(2013-12-03)
- ·pdo连接数据类与中文乱码解决方法(2014-09-11)
- ·pdo连接数据库与查询与保存数据到mysql(2014-09-11)
- ·pdo连接access数据库代码(2014-09-11)
- ·php实现基于PDO的预处理示例(2018-08-31)
- ·Linux(CentOS)下PHP扩展PDO编译安装的方法(2019-10-04)
- ·PHP中PDO的事务处理分析(2019-10-04)
- ·PHP的PDO操作简单示例(2019-10-31)
- ·PHP5.2中PDO的简单使用方法(2019-11-06)
- ·如何获取PDO对象并设置属性?(代码详解)(2020-02-03)
- ·如何使用PDO进行SELECT查询?(代码示例)(2020-02-03)
- ·PHP PDOStatement:bindParam插入数据错误问题分析(2020-07-02)
- ·PDO预处理语句PDOStatement对象使用总结(2021-04-27)
- ·PHP PDOStatement对象bindpram()、bindvalue()和bindcolumn之间的区别(2021-04-27)
- ·php使用pdo连接并查询sql数据库的方法(2021-05-04)
- ·php使用pdo连接mssql server数据库实例(2021-05-04)
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)