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() unsigned NOT NULL AUTO_INCREMENT,
`name` varchar() DEFAULT NULL,
`age` int() 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',),('poli2',);
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,);
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=>,PDO::ATTR_ERRMODE=>);
$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,);
$sql = "select * from tttt";
$pdo->query($sql);
echo $pdo->errorCode();
print_r($pdo->errorInfo());
} catch (Exception $exc) {
echo $exc->getMessage();
}
?>