pdo的简介
pdo的配置与启用
1.配置php配置文件,开启相应扩展
extension = php_pdo.dll
2.开启对相应数据库的扩展(mysql为例)
extension = php_pdo_mysql.dll
pdo连接数据库
1.通过参数形式连接数据库(推荐)
try {
$dsn='mysql:host=localhost;dbname=t2';
$username='root';
$password='123456';
$pdo = new PDO($dsn,$username,$password);
var_dump($pdo);
} catch (Exception $exc) {
echo $exc->getMessage();
}
2.通过uri形式连接数据库
和上面基本类似,只是把$dsn放在新建的文件里
3.通过配置文件形式连接数据库
pdo对象的使用
常用方法
exec()执行一条sql语句,并返回其受影响的行数
query()执行一条sql语句,返回一个pdostatement对象
prepare()准备要执行的sql语句,返回pdostatement对象
quote()返回一个添加引号的字符串,用于sql语句中
lastInsertId 返回最后插入行的ID
setAttribute()设置数据库连接属性
getAttribute()得到数据库连接的属性
errorCode()获取跟数据库句柄上一次操作相关的sqlstate
errorInfo()获取跟数据库句柄上一次操作的错误信息
beginTransaction()启动一个事物
commit()提交一个事物
rollBack回滚一个事物
inTransaction()检测是否在一个内
1.exec()
//建表
try {
$dsn='mysql:host=localhost;dbname=t2';
$username='root';
$password='123456';
$pdo = new PDO($dsn,$username,$password);
$sql =<<<EOF
CREATE TABLE `people2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
);
EOF;
//执行一条sql语句并返回其受影响的记录的条数
//注意:exec对与select没有作用;
$res = $pdo->exec($sql);
var_dump($res);
} catch (Exception $exc) {
echo $exc->getMessage();
}
//插入数据
header('content-type:text/html;charset=utf-8');
try {
$dsn='mysql:host=localhost;dbname=t2';
$username='root';
$password='123456';
$pdo = new PDO($dsn,$username,$password);
$sql =<<<EOF
insert into people2(name,age) values('jim2',15),('poli2',24);
EOF;
//执行一条sql语句并返回其受影响的记录的条数
//注意:exec对与select没有作用;
$res = $pdo->exec($sql);
echo '受影响的行数为:'.$res.'<br>';
echo '最后插入的ID号为:'.$pdo->lastInsertId();
var_dump($res);
} catch (Exception $exc) {
echo $exc->getMessage();
}
- 错误信息
header('content-type:text/html;charset=utf-8');
try {
$dsn='mysql:host=localhost;dbname=t2';
$username='root';
$password='123456';
$pdo = new PDO($dsn,$username,$password);
$sql =<<<EOF
insert into people2222222222(name,age) values('jim2',15),('poli2',24);
EOF;
$res = $pdo->exec($sql);
//现在插入一个错误表
if($res===false){
echo $pdo->errorCode();//sqlstate的值
echo '<hr/>';
$errInfo = $pdo->errorInfo();//返回错误信息的数组
print_r($errInfo);
}
} catch (Exception $exc) {
echo $exc->getMessage();
}
- 查询
header('content-type:text/html;charset=utf-8');
try {
$dsn='mysql:host=localhost;dbname=t2';
$username='root';
$password='123456';
$pdo = new PDO($dsn,$username,$password);
$sql =<<<EOF
select * from people2;
EOF;
$res = $pdo->query($sql);
foreach ($res as $row){
print_r($row);
echo '<hr/>';
}
} catch (Exception $exc) {
echo $exc->getMessage();
}
4.预处理查询
header('content-type:text/html;charset=utf-8');
//prepare()
//execute()
try {
$dsn='mysql:host=localhost;dbname=t2';
$username='root';
$password='123456';
$pdo = new PDO($dsn,$username,$password);
$sql =<<<EOF
select * from people2;
EOF;
$stmt = $pdo->prepare($sql);
//var_dump($res);
$res=$stmt->execute();
//$row = $stmt->fetch();
//print_r($row);
if($res){
while ($row=$stmt->fetch()){
print_r($row);
echo '<hr />';
}
}
} catch (Exception $exc) {
echo $exc->getMessage();
}
<?php
header('content-type:text/html;charset=utf-8');
//设置获取模式
try {
$dsn='mysql:host=localhost;dbname=t2';
$username='root';
$password='123456';
$pdo = new PDO($dsn,$username,$password);
$sql =<<<EOF
select * from people2;
EOF;
$stmt = $pdo->prepare($sql);
//var_dump($res);
$res=$stmt->execute();
//设置获取模式,一种传参数,另一种setFetchMode;
$mode = PDO::FETCH_NUM; //索引
//$mode = PDO::FETCH_ASSOC; 关联数组
//$mode = PDO::FETCH_BOTH;
//$rows = $stmt->fetchAll($mode);
//或者使用setFetchMode()方式
$stmt->setFetchMode($mode);
$rows = $stmt->fetchAll();
print_r($rows);
} catch (Exception $exc) {
echo $exc->getMessage();
}
5.数据库连接属性
header('content-type:text/html;charset=utf-8');
//获取属性、设置属性
try {
$dsn='mysql:host=localhost;dbname=t2';
$username='root';
$password='123456';
$pdo = new PDO($dsn,$username,$password);
echo '自动提交:'.$pdo->getAttribute(PDO::ATTR_AUTOCOMMIT);
echo '<hr/>';
echo 'PDO默认的错误处理模式:'.$pdo->getAttribute(PDO::ATTR_ERRMODE);
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,0);
echo '<hr/>';
echo '自动提交:'.$pdo->getAttribute(PDO::ATTR_AUTOCOMMIT);
} catch (Exception $exc) {
echo $exc->getMessage();
}
header('content-type:text/html;charset=utf-8');
//设置属性
try {
$dsn='mysql:host=localhost;dbname=t2';
$username='root';
$password='123456';
$options = array(PDO::ATTR_AUTOCOMMIT=>0,PDO::ATTR_ERRMODE=>2);
$pdo = new PDO($dsn,$username,$password,$options);
echo $pdo->getAttribute(PDO::ATTR_AUTOCOMMIT);
ECHO '<HR>';
echo $pdo->getAttribute(PDO::ATTR_ERRMODE);
} catch (Exception $exc) {
echo $exc->getMessage();
}
header('content-type:text/html;charset=utf-8');
//常用属性
try {
$dsn='mysql:host=localhost;dbname=t2';
$username='root';
$password='123456';
$pdo = new PDO($dsn,$username,$password);
$attrArr = array(
'AUTOCOMMIT','ERRMODE','CASE','PERSISTENT','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 (Exception $exc) {
echo $exc->getMessage();
}
pdo statement对象的使用
- quote方法防止sql注入
<?php
header('content-type:text/html;charset=utf-8');
//设置属性
if($_POST['form']){
$username = 'root';
$password = '123456';
try {
$dsn='mysql:host=localhost;dbname=t2';
$name=$_POST['name'];
$age=$_POST['age'];
$pdo = new PDO($dsn,$username,$password);
/*
echo $name = $pdo->quote($name); //防止注入
echo '<hr>';
$sql = "select * from people2 where name={$name} and age={$age}";
*
*/
$sql = "select * from people2 where name='{$name}' and age='{$age}'";
echo $sql;
echo '<hr>';
//把数据全部输出出来了
$stmt = $pdo->query($sql);
//返回查询记录的行数
echo $stmt->rowCount();
} catch (Exception $exc) {
echo $exc->getMessage();
}
}
?>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>TODO supply a title</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
</head>
<body>
<form action="pdo1.php" name="form2" method="post">
Name:<input type="text" name="name" value="' or 1=1 #" /><br>
age:<input type="text" name="age" value="23" />
<input type="submit" name="form" value="提交" />
</form>
</body>
</html>
2.预处理防注入
<?php
header('content-type:text/html;charset=utf-8');
//设置属性
if($_POST['form']){
$username = 'root';
$password = '123456';
try {
$dsn='mysql:host=localhost;dbname=t2';
$name=$_POST['name'];
$age=$_POST['age'];
$pdo = new PDO($dsn,$username,$password);
$sql = "select * from people2 where name=:name and age=:age";
//$sql = "select * from people2 where name=? and age=?";
echo '<hr>';
$stmt = $pdo->prepare($sql);
$res=$stmt->execute(array(":name"=>$name,":age"=>$age));
//$res=$stmt->execute(array($name,$age));
echo $stmt->rowCount();
} catch (Exception $exc) {
echo $exc->getMessage();
}
}
?>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>TODO supply a title</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
</head>
<body>
<form action="pdo1.php" name="form2" method="post">
Name:<input type="text" name="name" value="' or 1=1 #" /><br>
age:<input type="text" name="age" value="23" />
<input type="submit" name="form" value="提交" />
</form>
</body>
</html>
pdo事物处理
1.错误提示模式
<?php
header('content-type:text/html;charset=utf-8');
/*
PDO::ERRMODE_SLIENT 默认模式 静默模式 0
PDO::ERRMODE_WARNING 警告模式 1
PDO::ERRMODE_EXCEPTION 异常模式 2
* */
$username = 'root';
$password = '123456';
try {
$dsn='mysql:host=localhost;dbname=t2';
$pdo = new PDO($dsn,$username,$password);
//$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_ERRMODE,2);
$sql = "select * from tttt";
$pdo->query($sql);
echo $pdo->errorCode();
print_r($pdo->errorInfo());
} catch (Exception $exc) {
echo $exc->getMessage();
}
?>