PDOStatement对象的使用

1.quote()方法防止SQL注入

//通过返回带引号的字符串，过滤字符串中的特殊字符
$username = $pdo->quote($username);
//sql语句中便不需要增加''单引号了
$sql = "SELECT * FROM user WHERE username = {$username} AND password = '{$password}';

//PDOStatement对象的方法：rouCount()
//对于select操作返回的结果集中记录的条数
//对于insert，update，delete返回受影响的记录条数

$stmt = $pdo->query($sql);
echo $stmt->rouCount();

2.预处理语句中的占位符的使用

//冒号占位符
$sql = "SELECT * FROM user WHERE username = :username AND password = :password";
$stmt = $pdo->prepare($sql);
$stmt->execute( array( ":username"=>$username, ":password"=>$password) ); 

//问号占位符
$sql =  "SELECT * FROM user WHERE username = ? AND password = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute( array( $username, $password) ); 

3.bindParam()方法绑定参数

//bindParam()绑定一个参数到指定的变量名
$sql = "INSERT INTO user(username, password) VALUES (:username, :password)";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(":username",$username);
$stmt->bindParam(":password",$password);
$stmt->execute();

$sql = "INSERT INTO user(username, password) VALUES (?,?)";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(1,$username);
$stmt->bindParam(2,$password);
$stmt->execute();

$sql = "DELETE FROM user WHERE id < :id";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(":id", $id);
$stmt->execute();

4.bindValue()方法绑定参数

//bindValue()把一个值绑定到参数
$sql = "INSERT INTO user(username, password) VALUES (?,?)";
$stmt =  $pdo->prepare($sql);
$stmt->bindValue(1,'test');
$stmt->bindValue(2,'123456');
$stmt->execute();

/5.bindColumn()方法绑定参数/

//bindColumn()绑定一列到一个php变量
$sql = "SELECT username,password FROM user";
$stmt = $pdo->prepare($sql);
$stmt->execute();

//结果集中的列数(属性)
echo $stmt->columnCount();

$stmt->bindColumn(1,$username);
$stmt->bindColumn(2,$password);

while($stmt->fetch(PDO::FETCH_BOUND)){
    echo "用户名：".$username." 密码：".$password."<hr/>";
}

/6.fetchColumn()方法从结果集中返回一列/

$sql = "SELECT * FROM user";
$stmt = $pdo->query($sql);
echo $stmt->fetchColumn(0);
//没有办法返回同一行的令一列