PDO的基本应用【访问不同的数据库】【事务功能】【防止SQL注入】

PDO

1.访问不同的数据库
2.自带事务功能
3.防止SQL注入

访问自带的事务功能展示，

 1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 2 <html xmlns="http://www.w3.org/1999/xhtml">
 3 <head>
 4 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
 5 <title>无标题文档</title>
 6 </head>
 7 
 8 <body>
 9 
10 <?php
11 
12 /*//1.造对象
13 $dsn = "mysql:dbname=mydb;host=localhost";
14 $pdo = new PDO($dsn,"root","123");
15 
16 //2.写SQL语句
17 $sql = "update nation set name='兽族' where code='n013'";
18 
19 //3.执行SQL语句
20 //$r = $pdo->query($sql);
21 $r = $pdo->exec($sql);*/
22 
23 //事务功能
24 //造对象
25 $dsn = "mysql:dbname=mydb;host=localhost";
26 $pdo = new PDO($dsn,"root","123");
27 
28 //设置异常模式
29 $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
30 
31 
32 //写SQL语句
33 $sql1 = "insert into nation values('n016','人族')";
34 $sql2 = "insert into nation values('n017','不死族')";
35 
36 //执行两条SQL语句
37 try
38 {
39     //启动事务
40     $pdo->beginTransaction();
41     
42     $pdo->exec($sql1);
43     $pdo->exec($sql2);
44     
45     //提交事务
46     $pdo->commit();
47 }
48 catch(PDOException $e)
49 {
50     //$e->getMessage();
51     //回滚
52     $pdo->rollBack();
53 }
60 
61 
62 ?>
63 
64 
65 </body>
66 </html>

下面的是防止sql注入问号占位

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
</head>

<body>
<?php

//造对象
$dsn = "mysql:dbname=mydb;host=localhost";
$pdo = new PDO($dsn,"root","");

//写SQL语句,预处理语句
$sql = "insert into nation values(?,?)";

//准备SQL语句,返回statement对象
$st = $pdo->prepare($sql);

//绑定参数
/*$st->bindParam(1,$code);
$st->bindParam(2,$name);

$code="n022";
$name="矮人族";*/

$attr = array("n023","魔族");  //直接扔就可以了！

//提交执行,不用给SQL语句了，已经传过去了
var_dump($st->execute($attr));


//预处理语句里面用?占位的，给数组的时候要给索引数组
?>
</body>
</html>

另一种方法名称占位

 1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 2 <html xmlns="http://www.w3.org/1999/xhtml">
 3 <head>
 4 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
 5 <title>无标题文档</title>
 6 </head>
 7 
 8 <body>
 9 
10 <?php
11 
12 //造对象
13 $dsn = "mysql:dbname=mydb;host=localhost";
14 $pdo = new PDO($dsn,"root","");
15 
16 //写SQL语句,预处理语句，使用name占位
17 $sql = "insert into nation values(:code,:name)";  //注意用前面加冒号！！
18 
19 //准备执行
20 $st = $pdo->prepare($sql);
21 
22 //绑定参数
23 /*$st->bindParam(":code",$code,PDO::PARAM_STR);
24 $st->bindParam(":name",$name,PDO::PARAM_STR);
25 
26 $code="n024";
27 $name="神族";*/
28 
29 $attr = array("code"=>"n025","name"=>"虫族");
30 
31 //执行
32 $st->execute($attr);    //注意执行方法
33 
34 
35 
36 ?>
37 </body>
38 </html>

名称占位有点好处就是$_POST[""]提交的值就是处理页面要用的，省去重新赋的步骤

查询

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
</head>

<body>
<?php

//造对象
$dsn = "mysql:dbname=mydb;host=localhost";
$pdo = new PDO($dsn,"root","123");

//写SQL语句,预处理语句
$sql = "select * from nation";

//准备执行
$st = $pdo->prepare($sql);

//执行
$st->execute();

//读数据
var_dump($st->fetchAll(PDO::FETCH_ASSOC));   


?>
</body>
</html>