search

Home >php教程 >php手册 >php中PDO方式实现数据库的增删改查

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

WBOY
WBOYOriginal
2016-06-13 09:04:28823browse

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

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

Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：PHP实现远程下载文件到本地Next article：PHP生成json和xml类型接口数据格式

Related articles

See more