Home > Article > Backend Development > PDO method in php to implement database addition, deletion, modification and query_PHP tutorial
PDO method in php to implement database addition, deletion, modification and query_PHP tutorial
- WBOYOriginal
- 2016-07-13 09:53:56840browse
The PDO method in php realizes the addition, deletion, modification and query of the database
PDO is a public class for mysql database operation. We can directly use pdo to operate without customizing the class. The database is installed, but pdo is not enabled in the default configuration of php, so we must enable it in php.ini before it can be used.
You need to enable php’s pdo support, which is supported by php5.1 or above
There are three elements to achieve database connection singleton: static variables, static instantiation methods, and private constructors DPDO.php
?
|
class DPDO{
private $DSN;
private $DBUser;
private $DBPwd;
private $longLink;
private $pdo;
//Private constructor to prevent direct instantiation
private function __construct($dsn, $DBUser, $DBPwd, $longLink = false) {
$this->DSN = $dsn;
$this->DBUser = $DBUser;
$this->DBPwd = $DBPwd;
$this->longLink = $longLink;
$this->connect();
}
//Private empty clone function to prevent being cloned
private function __clone(){}
//Static instantiation function returns a pdo object
static public function instance($dsn, $DBUser, $DBPwd, $longLink = false){
static $singleton = array();//static function used to store instantiated objects
$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() . ' '); } } } |
Used to handle field mapping. Using pdo field mapping can effectively avoid sql injection
?
|
//Field associative array processing, mainly used for writing and updating data, query conditions with the same and or or, and generating sql statements and arrays of mapped fields 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); } //Used to handle single field processing 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); } //Using the Gang method can easily generate query conditions and corresponding data arrays 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); } |
Specific implementation of addition, deletion, modification and query 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
?
|
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, '<', '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 . ' '; //删除 $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); The above is the entire content of this article, I hope you all like it. |