php部分---PDO;

PDO

数据访问抽象层

PDO的三个功能：

1.操作其它数据库

2.事务功能

3.防止SQL注入攻击

操作数据库：

造PDO对象

//$dsn = "mysql:dbname=mydb;host=localhost"; //数据源
//$pdo = new PDO($dsn,"root","123");

//写SQL语句

//$sql = "select * from nation";
//$sql = "insert into nation values('n077','数据')";

//执行,返回的是PDOStatement对象

//$a = $pdo->query($sql); //执行查询
//$a = $pdo->exec($sql); //执行其他语句

//var_dump($a);

//$arr = $attr->fetchAll(PDO::FETCH_BOTH);//返回索引和关联数组

$arr = $attr->fetch(PDO::FETCH_ASSOC);  //返回关联数组

//var_dump($arr);

//事务功能

//事务：能够控制语句同时成功同时失败，失败时可以回滚

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

//设置异常模式

$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

try
{
//开启事务
$pdo ->beginTransaction(); //回滚点即若有错误，回滚到这里

$sql1 = "insert into nation values('n080','是删')";
$sql2 = "insert into nation values('n070','好几款')";
$sql3 = "insert into nation values('n075','好几款')";

$pdo->exec($sql1);
$pdo->exec($sql2);
$pdo->exec($sql3);

//提交
$pdo->commit();
}
catch(Exception $e)
{
//抓住try里面出现的错误，并且处理
//echo $e->getMessage(); //获取异常信息

//设置pdo回滚
$pdo->rollBack();
}
//final()
//{
//最终执行，无论以上try代码有没有出错，都会执行
//}

预处理语句防止SQL注入:

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

//$code = "n005";

第一种方式：SQL语句里面需要加占位符 ?

//$sql = "select * from nation where code=?";
$sql = "insert into nation values(?,?)";

//准备执行,返回PDOStatement对象

$st = $pdo->prepare($sql);

//调用绑定参数的方法来绑定参数

//$st->bindParam(1,$code);
//$st->bindParam(2,$name);
//$name = "测试1";

//索引数组

$attr = array("n006","测试2");

//执行方法

$st->execute($attr);

//$attr = $st->fetchAll();

//var_dump($attr);

 

第二种方式：占位符是字符串

$sql = "insert into nation values(:code,:name)";

$st = $pdo->prepare($sql);
//1.$st->bindParam(":code",$code,PDO::PARAM_STR);
//$st->bindParam(":name",$name,PDO::PARAM_STR);
//$code = "n007";
//$name = "测试3";

//关联数组
2.$attr = array("code"=>"n008","name"=>"测试4");

$st->execute($attr);

字符串占位符的例子：

客户端页面

<form action="addchuli.php" method="post">
    <div>代号：<input type="text" name="code" /></div>
    <div>名称：<input type="text" name="name" /></div>
    <input type="submit" value="添加" />
</form>

处理页面

<?php
$dsn = "mysql:dbname=mydb;host=localhost";
$pdo = new PDO($dsn,"root","123");

//占位符是字符串
$sql = "insert into nation values(:code,:name)";

$st = $pdo->prepare($sql);
//1.$st->bindParam(":code",$code,PDO::PARAM_STR);
//$st->bindParam(":name",$name,PDO::PARAM_STR);
//$code = "n007";
//$name = "测试3";

2.$st->execute($_POST);   post传过来的数据就是二维索引数组