PHP：PDO预处理

PDO类:

prepare();

---

PDO预处理查询

<?php
$servername = "localhost";
$username = "root";
$password = "133";
$dbname='myweb';

$pdo= new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$pdo->exec('set names utf8');

$sql="select * from user";
$smt=$pdo->prepare($sql);
$smt->execute();

$rows=$smt->fetchAll(PDO::FETCH_ASSOC);

echo '<pre>';
print_r($rows);
echo '</pre>';



?>

---

添加

<?php
$servername = "localhost";
$username = "root";
$password = "133";
$dbname='myweb';

$pdo= new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$pdo->exec('set names utf8');

$sql="insert into user(username,age) values('user11','123')";
$smt=$pdo->prepare($sql);
if($smt->execute()){
	$num=$smt->rowCount();
	echo "{$num}行添加成功！";
}




?>

**

---

**

**

---

**

**

---

**

**

PDO预处理类:

• bindValue()
把一个值绑定到一个参数

---

**

<?php
$servername = "localhost";
$username = "root";
$password = "123";
$dbname='myweb';

$pdo= new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$pdo->exec('set names utf8');

$sql="select * from user where id=?";
$smt=$pdo->prepare($sql);
$id=35;
$smt->bindValue(1,$id,PDO::PARAM_INT);
$smt->execute();

$rows=$smt->fetch(PDO::FETCH_ASSOC);

echo '<pre>';
print_r($rows);
echo '</pre>';

?>

**

Array
(
    [id] => 35
    [username] => user12
    [age] => 25
    [class_id] => 5
)

• columnCount()

---

获取表列数

<?php
$servername = "localhost";
$username = "root";
$password = "123";
$dbname='myweb';

$pdo= new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$pdo->exec('set names utf8');

$sql="select * from user";
$smt=$pdo->prepare($sql);
$smt->execute();

$rows=$smt->columnCount();

echo $rows;

?>

**

4

• fetch()
从结果集中获取第一行

---

**

<?php
$servername = "localhost";
$username = "root";
$password = "133";
$dbname='myweb';

$pdo= new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$pdo->exec('set names utf8');

$sql="select * from user";
$smt=$pdo->prepare($sql);
$smt->execute();

$rows=$smt->fetch(PDO::FETCH_ASSOC);

echo '<pre>';
print_r($rows);
echo '</pre>';




?>

---

**

**

• fetchAll()

---

**

**

• fetchColumn()
从结果集中的下一行返回单独的一列。

---

**

<?php
$servername = "localhost";
$username = "root";
$password = "133";
$dbname='myweb';

$pdo= new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$pdo->exec('set names utf8');

$sql="select * from user";
$smt=$pdo->prepare($sql);
$smt->execute();

$rows=$smt->fetchColumn(3);

echo '<pre>';
print_r($rows);
echo '</pre>';

$rows=$smt->fetchColumn(2);
echo '<pre>';
print_r($rows);
echo '</pre>';


?>

**

1

19

---

获取表总行数

<?php
$servername = "localhost";
$username = "root";
$password = "123";
$dbname='myweb';

$pdo= new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$pdo->exec('set names utf8');

$sql="select count(*) from user";
$smt=$pdo->prepare($sql);
$smt->execute();

$rows=$smt->fetchColumn();

echo $rows;




?>

**

27

• rowCount()

---

rowCount()影响行数

<?php
$servername = "localhost";
$username = "root";
$password = "133";
$dbname='myweb';

$pdo= new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$pdo->exec('set names utf8');

$sql="delete from user where id=31";
$smt=$pdo->prepare($sql);
if($smt->execute()){
	$num=$smt->rowCount();
	echo "{$num}行删除成功！";
}




?>

**

1行删除成功！

• execute()
执行一条预处理语句