本文实例讲述了php数据对象pdo操作技巧。分享给大家供大家参考，具体如下：
php 数据对象 （pdo） 扩展为php访问数据库定义了一个轻量级的一致接口。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
<?php
try {
$dsn = "mysql:host=localhost; port=3306; dbname=wsq_hotel; charset=utf-8";
$user = 'root';
$psw ='root';
$pdo = new pdo($dsn,$user,$psw);
$sql = 'select goods_prices from wsq_goods_info where goods_id=2';
// $sql = "show database";
$res = $pdo->query($sql) or var_dump($pdo->errorinfo());
// var_dump($res);
$mon = $res->fetch(pdo::fetch_assoc);
echo $mon['goods_price'];
} catch (pdoexception $e) {
echo $e->getmessage();
}
?>
|
pdo操作事务
|
1
2
3
4
5
6
7
8
|
//开启事务
begintransacition()
//回滚
rollback()
//提交
commit()
//判断是否处于事务之中
intransaction()
|
返回最后插入行的id
|
1
|
pdo::lastinsertid()
|
exec()执行
与query()相比，exec()返回的是受影响行数
|
1
2
3
4
|
$sql = "insert into table values('$val')";
if(false===$pdo->exec($sql)){
echo '执行失败';
}
|
pdo实现预编译
指的是预先编译sql的结构的一种执行sql的语法
如果执行多条结构相同的sql，编译的中间结果（语法树）应该也是一致的，因此可以将相同的结构，统一编译，每次使用不同的数据执行即可。
编译统一的结构
|
1
|
$pdostatement = $pdo->prepare(sql结构)
|
绑定数据到中间编译结果
|
1
|
$pdostatement ->bindvalue()
|
执行
|
1
2
3
4
5
6
7
8
9
|
$pdostatement ->execute()
//$sql = "insert into table values(null,?)";
$sql = "insert into table values(null,:name)";
$stmt = $pdo->prepare($sql);
//多组数据也是一编译一执行
//$stmt->bindvalue(1,'bee');
$stmt->bindvalue(':name','bee');
$res = $stmt->execute();
var_dump($res);
|
预编译能更好地防止sql注入，是因为预编译时候不需要用户的数据参与，因此编译时结构固定，所以数据不影响到sql结构。
$pdo->query()与$pdo->execute()如果需要防止sql注入，可以使用$pdo->quote()(其作用是先转义后加引号)
pdostatement常用方法：
errorinfo()
errorcode()
fetchcolumn()
fetch()
fetchall()
rowcount()
closecursor()
pdo应用
|
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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
|
<?php
header('content-type:text/html;charset=utf-8');
class pdodb{
static private $_init;
private $_host;
private $_port;
private $_dbname;
private $_username;
private $_password;
private $_charset;
private $_dns;
private $_pdo;
private function __construct($config){
$this->_initparamas($config);
$this->_initdns();
$this->_initdriveroptions();
$this->_initpdo();
}
private function __clone(){}
static public function getinstance($config){
if(!static::$_init instanceof static){
static::$_init = new static($config);
}
return static::$_init;
}
private function _initparamas($config){
$this->_host = isset($config['host'])?$config['host']:'localhost';
$this->_port = isset($config['port'])?$config['port']:'3306';
$this->_dbname = isset($config['dbname'])?$config['dbname']:'';
$this->_username = isset($config['username'])?$config['username']:'root';
$this->_passward = isset($config['passward'])?$config['passward']:'';
$this->_charset = isset($config['charset'])?$config['charset']:'utf8';
}
private function _initdns(){
$this->_dns = "mysql:host=$this->_host;port=$this->_port;dbname=$this->_dbname";
}
private function _initdriveroptions(){
$this->_driveroptions = array(
pdo::mysql_attr_init_command => "set names $this->_charset"
);
}
private function _initpdo(){
$this->_pdo = new pdo($this->_dns,$this->_username,$this->_passward,$this->_driveroptions) or die("fail");
}
public function query($sql){
if(!$result = $this->_pdo->query($sql)){
$erro = $this->_pdo->errorinfo();
echo '失败的语句'.$sql.'<br>';
echo '错误代码'.$erro[1].'<br>';
echo '错误信息'.$erro[2].'<br>';
die;
}
return $result;
}
public function fetchall($sql){
$res = $this->query($sql);
$list = $res->fetchall(pdo::fetch_assoc);
$res->closecursor();
return $list;
}
public function fetchrow($sql){
$res = $this->query($sql);
$row = $res->fetch(pdo::fetch_assoc);
$res->closecursor();
return $row;
}
public function fetchone($sql){
$res = $this->query($sql);
$one = $res->fetchcolumn();
$res->closecursor();
return $one;
}
public function escape_string($data){
return $this->_pdo->quote($data);
}
}
$config = array(
"host"=>"localhost",
"username"=>"root",
"passward"=>"root",
"dbname"=>"students"
);
$pdo = pdodb::getinstance($config);
$sql = "select sdept from student where sage=21";
var_dump($pdo->fetchrow($sql));
?>
|
运行效果图如下：
希望本文所述对大家php程序设计有所帮助。