欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  后端开发

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

程序员文章站 2022-03-10 14:53:25
...

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() . '
');

}

}

}

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

　　?

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

//字段关联数组处理， 主要用于写入和更新数据、同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

　　?

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

58

59

60

61

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(!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);

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() . '
');

}

}

$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 ? '成功' : '失败') . '
';

//更新

$upConData['a'] = 100;

$upConJudge['a'] = '

$upConData['b'] = 30;

$upConJudge['b'] = '>';

list($upConStr, $mapUpConData) = $DB->FDField('b', 200, '

$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 . '
';

//删除

$delVal = rand(1, 10);

list($delCon, $mapDelCon) = $DB->FDField('a', $delVal);

$delRet = $DB->delete('a', $delCon, $mapDelCon);

echo '删除a=' . $delVal . ($delRet ? '成功' : '失败') . '
';

//查询

$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);

　　以上所述就是本文的全部内容了，希望大家能够喜欢。

www.bkjia.comtruehttp://www.bkjia.com/PHPjc/1000073.htmlTechArticlephp中PDO方式实现数据库的增删改查 PDO是mysql数据库操作的一个公用类了，我们不需要进行自定类就可以直接使用pdo来操作数据库了，但是在...
相关标签： php PDO 数据库

上一篇： PHP实现远程下载文件到本地_PHP教程

下一篇： ps画笔大小快捷键没反应怎么办

推荐阅读