数据库抽象层——PDO

来源：互联网发布：淘宝评价如何修改编辑：程序博客网时间：2024/06/11 10:38

PDO（PHP Data Object）,数据库访问抽象层，统一各种数据库的访问接口。

PDO支持的数据库

PDO安装与配置

1.配置PHP配置文件，开启响应扩展(即在php.ini中去掉相关注释)

extension=php_pdo.dll

若没有extension=php_pdo.dll，需要看看你php的版本，包括ext目录下是否有php_pdo.dll这个动态链接库文件，如果有的话你可以手动在php.ini文件中加上这一项。(但是经过测试，加不加都可以)

2.开启对响应数据库的扩展（以MySQL为例）

extension=php_pdo_mysql.dll

3.通过查看phpinfo可以看到PDO扩展的详细信息

在浏览器输入链接，可以看到下图

连接数据库

1.通过参数形式连接数据库（推荐）

pdo_connect1.php

<?php//通过参数形式连接数据库try {$dsn ='mysql:host=localhost;dbname=test';$username='root';$passwd='';$pdo = new PDO($dsn,$username,$passwd);var_dump($pdo);} catch (PDOException $e) {echo $e->getMessage();}

2.通过URI形式连接数据库

pdo_connect2.php

<?php//通过uri形式连接数据库try {$dsn ='uri:file://F:\Program Files\wamp\www\dsn.txt';$username='root';$passwd='';$pdo = new PDO($dsn,$username,$passwd);var_dump($pdo);} catch (PDOException $e) {echo $e->getMessage();}

在文本文档 dsn.txt 中输入

mysql:dbname=test;host=localhost

3.通过配置文件形式连接数据库

重启服务器

pdo_connect3.php

<?php//通过配置文件形式连接数据库try {$dsn ='test';$username='root';$passwd='';$pdo = new PDO($dsn,$username,$passwd);var_dump($pdo);} catch (PDOException $e) {echo $e->getMessage();}

PDO对象的方法

exec()方法

<?php header('content-type:text/html;charset=utf-8');try{$pdo=new PDO('mysql:host=localhost;dbname=test','root','root');//exec():执行一条sql语句并返回其受影响的记录的条数,如果没有受影响的记录，他返回0$sql='INSERT user(username,password,email) VALUES("pjc","pjc","pjc@qq.com")';$res=$pdo->exec($sql);echo '受影响的记录的条数为：'.$res,'<br/>';//$pdo->lastInsertId():得到新插入记录的ID号echo '最后插入的ID号为'.$pdo->lastInsertId();//$sql='select * from user';//$res=$pdo->exec($sql);   //返回0  因为没有任何记录被影响 exec对于select没有作用//echo $res.'条记录被影响';}catch(PDOException $e){echo $e->getMessage();}

query()

<?php header('content-type:text/html;charset=utf-8');try{$pdo=new PDO('mysql:host=localhost;dbname=test','root','root');//$sql='select * from user where id=2';$sql='select id,username,email from user';//$pdo->query($sql)，执行SQL语句，返回PDOStatement对象$stmt=$pdo->query($sql);var_dump($stmt);echo '<hr/>';foreach($stmt as $row){//print_r($row);echo '编号：'.$row['id'],'<br/>';echo '用户名：'.$row['password'],'<br/>';echo '邮箱：'.$row['email'],'<br/>';echo '<hr/>';}/*$sql='INSERT user(username,password,email) VALUES("pjc1","'.md5('pjc1').'","pjc1@qq.com")';$stmt=$pdo->query($sql);   //可以成功插入数据var_dump($stmt);*/}catch(PDOException $e){echo $e->getMessage();}

prepare()和execute

<?php header('content-type:text/html;charset=utf-8');try{$pdo=new PDO('mysql:host=localhost;dbname=test','root','root');$sql='select * from user where username="pjc1"';//prepare($sql):准备SQL语句$stmt=$pdo->prepare($sql);//execute():执行预处理语句$res=$stmt->execute();//var_dump($res);//fetch():得到结果集中的一条记录$row=$stmt->fetch();print_r($row);//var_dump($stmt);}catch(PDOException $e){echo $e->getMessage();}

setAttribute()和getAttribute()

<?php header('content-type:text/html;charset=utf-8');try{$dsn='mysql:host=localhost;dbname=test';$username='root';$passwd='root';$pdo=new PDO($dsn, $username, $passwd);echo '自动提交：'.$pdo->getAttribute(PDO::ATTR_AUTOCOMMIT);echo '<br/>';echo 'PDO默认的错误处理模式：'.$pdo->getAttribute(PDO::ATTR_ERRMODE);$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 0);echo '<br/>';echo '自动提交：'.$pdo->getAttribute(PDO::ATTR_AUTOCOMMIT);}catch(PDOException $e){echo $e->getMessage();}

以数组形式获取属性

<?php header('content-type:text/html;charset=utf-8');try{$dsn='mysql:host=localhost;dbname=test';$username='root';$passwd='root';$pdo=new PDO($dsn, $username, $passwd);$attrArr=array('AUTOCOMMIT','ERRMODE','CASE','PERSISTENT','TIMEOUT','ORACLE_NULLS','SERVER_INFO','SERVER_VERSION','CLIENT_VERSION','CONNECTION_STATUS');foreach($attrArr as $attr){echo "PDO::ATTR_$attr: ";echo $pdo->getAttribute(constant("PDO::ATTR_$attr")),'<br/>';}}catch(PDOException $e){echo $e->getMessage();}

在创建PDO对象时，在第四个参数里设置属性

<?php header('content-type:text/html;charset=utf-8');try{$dsn='mysql:host=localhost;dbname=test';$username='root';$passwd='root';$options=array(PDO::ATTR_AUTOCOMMIT=>0,PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION);$pdo=new PDO($dsn, $username, $passwd, $options);echo $pdo->getAttribute(PDO::ATTR_AUTOCOMMIT);echo '<br/>';echo $pdo->getAttribute(PDO::ATTR_ERRMODE);}catch(PDOException $e){echo $e->getMessage();}

quote() （一般不建议使用quote方法进行防SQL注入，因为有更好的方法）

<?php header('content-type:text/html;charset=utf-8');$username=$_POST['username'];$password=$_POST['password'];try{$pdo=new PDO('mysql:host=localhost;dbname=test','root','root');//echo $pdo->quote($username);//$sql="select * from user where username='{$username}' and password='{$password}'";//echo $sql;//通过quote():返回带引号的字符串，过滤字符串中的特殊字符$username=$pdo->quote($username);$sql="select * from user where username={$username} and password='{$password}'";echo $sql;$stmt=$pdo->query($sql);//PDOStatement对象的方法：rowCount()：对于select操作返回的结果集中记录的条数，//对于INSERT、UPDATE、DELETE返回受影响的记录的条数echo $stmt->rowCount();}catch(PDOException $e){echo $e->getMessage();}

①可以使用占位符方法防SQL注入

<?php header('content-type:text/html;charset=utf-8');$username=$_POST['username'];$password=$_POST['password'];try{$pdo=new PDO('mysql:host=localhost;dbname=test','root','root');$sql="select * from user where username=:username and password=:password";$stmt=$pdo->prepare($sql);$stmt->execute(array(":username"=>$username,":password"=>$password));echo $stmt->rowCount();}catch(PDOException $e){echo $e->getMessage();}

②可以使用？占位符方法防SQL注入

<?php header('content-type:text/html;charset=utf-8');$username=$_POST['username'];$password=$_POST['password'];try{$pdo=new PDO('mysql:host=localhost;dbname=test','root','root');$sql="select * from user where username=? and password=?";$stmt=$pdo->prepare($sql);$stmt->execute(array($username,$password));echo $stmt->rowCount();}catch(PDOException $e){echo $e->getMessage();}

PDOStatement对象的方法

fetchAll()

<?php header('content-type:text/html;charset=utf-8');try{$pdo=new PDO('mysql:host=localhost;dbname=test','root','root');$sql='select * from user';$stmt=$pdo->prepare($sql);$res=$stmt->execute();//if($res){//while($row=$stmt->fetch()){//print_r($row);//echo '<hr/>';//}//}$rows=$stmt->fetchAll();print_r($rows);//var_dump($stmt);}catch(PDOException $e){echo $e->getMessage();}

setFetchMode()

<?php header('content-type:text/html;charset=utf-8');try{$pdo=new PDO('mysql:host=localhost;dbname=test','root','root');$sql='select * from user';$stmt=$pdo->prepare($sql);$res=$stmt->execute();//if($res){//while($row=$stmt->fetch(PDO::FETCH_OBJ)){   //返回对象//print_r($row);//echo '<hr/>';//}//}//$rows=$stmt->fetchAll(PDO::FETCH_ASSOC);  //返回关联数组  PDO::FETCH_BOTH  默认为关联加索引//print_r($rows);echo '<hr/>';$stmt->setFetchMode(PDO::FETCH_ASSOC);//var_dump($stmt);$rows=$stmt->fetchAll();print_r($rows);}catch(PDOException $e){echo $e->getMessage();}

errorCode()和errorInfo()

<?php header('content-type:text/html;charset=utf-8');try{$pdo=new PDO('mysql:host=localhost;dbname=test','root','root');$sql='delete from user12 where id=1';  //故意书写错误的字段，使PDO报错$res=$pdo->exec($sql);//echo $res.'条记录被影响';//var_dump($res);if($res===false){//$pdo->errorCode():SQLSTATE的值echo $pdo->errorCode();echo '<hr/>';//$pdo->errorInfo():返回的错误信息的数组，数组中包含3个单元//0=>SQLSTATE,1=>CODE,2=>INFO$errInfo=$pdo->errorInfo();print_r($errInfo);}//echo '<hr/>';//echo $pdo->lastInsertId();}catch(PDOException $e){echo $e->getMessage();}

bindParam()

<?php header('content-type:text/html;charset=utf-8');try{$pdo=new PDO('mysql:host=localhost;dbname=test','root','root');$sql="INSERT user(username,password,email) VALUES(:username,:password,:email)";$stmt=$pdo->prepare($sql);$stmt->bindParam(":username",$username,PDO::PARAM_STR);$stmt->bindParam(":password",$password,PDO::PARAM_STR);$stmt->bindParam(":email",$email);$username='pjc2';$password='pjc2';$email='pjc2@qq.com';$stmt->execute();$username='pjc3';$password='pjc3';$email='pjc3@qq.com';$stmt->execute();echo $stmt->rowCount();}catch(PDOException $e){echo $e->getMessage();}

以？占位符形式

<?php header('content-type:text/html;charset=utf-8');try{$pdo=new PDO('mysql:host=localhost;dbname=test','root','root');$sql="INSERT user(username,password,email) VALUES(?,?,?)";$stmt=$pdo->prepare($sql);$stmt->bindParam(1,$username);$stmt->bindParam(2,$password);$stmt->bindParam(3,$email);$username='pjc4';$password='pjc4';$email='pjc4@qq.com';$stmt->execute();echo $stmt->rowCount();}catch(PDOException $e){echo $e->getMessage();}

bindValue()

<?php try{$pdo=new PDO('mysql:host=localhost;dbname=test','root','root');$sql='INSERT user(username,password) VALUES(:username,:password)';$stmt=$pdo->prepare($sql);$username='pjc6';$password='pjc6';$stmt->bindValue(':username',$username);$stmt->bindValue(':password',$password);$stmt->execute();echo $stmt->rowCount();}catch(PDOException $e){echo $e->getMessage();}

以？占位符形式

<?php try{$pdo=new PDO('mysql:host=localhost;dbname=test','root','root');$sql='INSERT user(username,password) VALUES(?,?)';$stmt=$pdo->prepare($sql);$username='pjc7';$password='pjc7';$stmt->bindValue(1,$username);$stmt->bindValue(2,$password);$stmt->execute();echo $stmt->rowCount();}catch(PDOException $e){echo $e->getMessage();}

bindColumn()、columnCount()和getColumnMeta()

<?phpheader('content-type:text/html;charset=utf-8'); try{$pdo=new PDO('mysql:host=localhost;dbname=test','root','root');$sql='SELECT username,password,email FROM user';$stmt=$pdo->prepare($sql);$stmt->execute();echo '结果集中的列数一共有：'.$stmt->columnCount();echo '<hr/>';print_r($stmt->getColumnMeta(0)); //起始点为0echo '<hr/>';$stmt->bindColumn(1, $username);$stmt->bindColumn(2,$password);$stmt->bindColumn(3, $email);while($stmt->fetch(PDO::FETCH_BOUND)){echo '用户名：'.$username.'-密码：'.$password.'-邮箱：'.$email.'<hr/>';}}catch(PDOException $e){echo $e->getMessage();}

fetchColumn()

<?phpheader('content-type:text/html;charset=utf-8'); try{$pdo=new PDO('mysql:host=localhost;dbname=test','root','root');$sql='SELECT username,password,email FROM user';$stmt=$pdo->query($sql);echo $stmt->fetchColumn(0),'<br/>';echo $stmt->fetchColumn(1),'<br/>';echo $stmt->fetchColumn(2);}catch(PDOException $e){echo $e->getMessage();}

PDO错误处理模式

PDO::ERRMODE_SLIENT: 默认模式，静默模式

出现错误时：

PDO::ERRMODE_WARNING: 警告模式

出现错误时：比静默模式多了一个 warning

PDO::ERRMODE_EXCEPTION:异常模式

出现错误时：

PDO事务

<?php header('content-type:text/html;charset=utf-8');try{$dsn='mysql:host=localhost;dbname=test';$username='root';$passwd='root';$options=array(PDO::ATTR_AUTOCOMMIT,0);$pdo=new PDO($dsn, $username, $passwd, $options);var_dump($pdo->inTransaction());//开启事务$pdo->beginTransaction();var_dump($pdo->inTransaction());//$sql='UPDATE userAccount SET money=money-2000 WHERE username="pjc"';$sql='UPDATE userAccount SET money=money-2000 WHERE username="cjp"';$res1=$pdo->exec($sql);if($res1==0){throw new PDOException('imooc 转账失败');}$res2=$pdo->exec('UPDATE userAccount SET money=money+2000 WHERE username="pjc"');if($res2==0){throw new PDOException('king 接收失败');}//提交事务$pdo->commit();}catch(PDOException $e){//回滚事务$pdo->rollBack();echo $e->getMessage();}

1 0