3. PDOStatement 对象

来源：互联网发布：linux常用查看命令编辑：程序博客网时间：2024/04/29 15:01

1. quote()方法

<html><head>    <meta http-equiv="content-type" content="text/html;charset=utf-8"/></head><body><form action="index.php" method="post">    用户名：<input type="text" name="username"><br/>    密&nbsp;码：<input type="password" name="password"/><br/>    <input type="submit" value="提交"/>    <input type="reset" value="重置"/></form></body></html>

index.php

<?phpheader('content-type:text/html;charset=utf-8');$username = $_POST['username'];$password = $_POST['password'];try {    $dsn = 'mysql:host=localhost;dbname=test';    $pdo = new PDO($dsn,'root','123456');    $sql = "select * from user where username='{$username}' and password='{$password}'";    echo $sql,'<br/>';    $res = $pdo->quote($sql);    echo $res;    //var_dump($res);die;    $stmt = $pdo->query($sql);    echo $stmt->rowCount();} catch (PDOException $e) {    echo $e->getMessage();}

这里写图片描述

2.预处理占位符

<?phpheader('content-type:text/html;charset=utf-8');$username = $_POST['username'];$password = $_POST['password'];try {    $dsn = 'mysql:host=localhost;dbname=test';    $pdo = new PDO($dsn,'root','123456');    /*$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));    echo $stmt->rowCount();} catch (PDOException $e) {    echo $e->getMessage();}

3.绑定参数
这里写图片描述

命名参数占位符：try {    $dsn = 'mysql:host=localhost;dbname=test';    $pdo = new PDO($dsn,'root','123456');    $sql = "insert into user(username,password,email) values(:username,:password,:email)";    $stmt = $pdo->prepare($sql);    $stmt->bindParam(':username',$username,PDO::PARAM_STR);    $stmt->bindParam(':password',$password,PDO::PARAM_STR);    $stmt->bindParam(':email',$email);    $username = 'abc123';    $password = 'abc123';    $email = 'ab123c@qq.com';    $stmt->execute();    echo $stmt->rowCount();} catch (PDOException $e) {    echo $e->getMessage();}

问号占位符：header('content-type:text/html;charset=utf-8');try {    $dsn = 'mysql:host=localhost;dbname=test';    $pdo = new PDO($dsn,'root','123456');    $sql = "insert into user(username,password,email) values(?,?,?)";    $stmt = $pdo->prepare($sql);    $stmt->bindParam(1,$username); //索引从 1 开始    $stmt->bindParam(2,$password);    $stmt->bindParam(3,$email);    $username = 'username1';    $password = 'username';    $email = 'username';    $stmt->execute();    echo $stmt->rowCount();} catch (PDOException $e) {    echo $e->getMessage();}

删除：try {    $dsn = 'mysql:host=localhost;dbname=test';    $pdo = new PDO($dsn,'root','123456');    $sql = "delete from user where id>:id";    $stmt = $pdo->prepare($sql);    $stmt->bindParam(':id',$id,PDO::PARAM_INT);    $id = 3;    $stmt->execute();    echo $stmt->rowCount();} catch (PDOException $e) {    echo $e->getMessage();}

4.bindValue()

可以让参数一些可变，一些不变：try {    $dsn = 'mysql:host=localhost;dbname=test';    $pdo = new PDO($dsn,'root','123456');    $sql = "insert into user(username,password,email) values(?,?,?)";    $stmt = $pdo->prepare($sql);    $username = 'wwoo123';    $password = '123456aaa';    $stmt->bindValue(1,$username);    $stmt->bindValue(2,$password);    $stmt->bindValue(3,'imooc@imooc.com');    $stmt->execute();    echo $stmt->rowCount();} catch (PDOException $e) {    echo $e->getMessage();}

try {    $dsn = 'mysql:host=localhost;dbname=test';    $pdo = new PDO($dsn,'root','123456');    $sql = "insert into user(username,password,email) values(:username,:password,:email)";    $stmt = $pdo->prepare($sql);    $username = 'wwoo123weee';    $password = '123456aaa';    $stmt->bindValue(':username',$username);    $stmt->bindValue(':password',$password);    $stmt->bindValue(':email','imooc@imooc.com');    $stmt->execute();    echo $stmt->rowCount();} catch (PDOException $e) {    echo $e->getMessage();}

5. bindColumn()
这里写图片描述

try {    $dsn = 'mysql:host=localhost;dbname=test';    $pdo = new PDO($dsn,'root','123456');    $sql = "select username,password,email from user";    $stmt = $pdo->prepare($sql);    $stmt->execute();    echo '结果集的列数 ： ' . $stmt->columnCount() . '<br/>';   var_dump($stmt->getColumnMeta(0));    //把列值绑定到指定变量    $stmt->bindColumn(1,$username);    $stmt->bindColumn(2,$password);    $stmt->bindColumn(3,$email);    while ($stmt->fetch(PDO::FETCH_BOUND)) {        echo '用户名：' . $username . '<br/>';        echo '密码：' . $password . '<br/>';        echo '邮箱：' . $email . '<br/>';    }} catch (PDOException $e) {    echo $e->getMessage();}

6. fetchColunm()
这里写图片描述

try {    $dsn = 'mysql:host=localhost;dbname=test';    $pdo = new PDO($dsn,'root','123456');    $sql = "select id,username,password,email from user";    $stmt = $pdo->query($sql);    var_dump($stmt);    //得到结果集第一行的第一列    echo $stmt->fetchColumn(0);    echo '<br/>';    //得到结果集第二行的第4列    echo $stmt->fetchColumn(3);} catch (PDOException $e) {    echo $e->getMessage();}

7.debugDumpParams
这里写图片描述

try {    $dsn = 'mysql:host=localhost;dbname=test';    $pdo = new PDO($dsn,'root','123456');    $sql = "insert into user(username,password,email) valus(?,?,?)";    $stmt = $pdo->prepare($sql);    $stmt->bindParam(1,$username,PDO::PARAM_STR);    $stmt->bindParam(2,$password,PDO::PARAM_STR);    $stmt->bindParam(3,$email,PDO::PARAM_STR);    $username = 'testParam';    $password = 'testParamaaa';    $email = 'testParambbb';    $stmt->execute();    echo '<pre>';    $stmt->debugDumpParams();    echo '</pre>';} catch (PDOException $e) {    echo $e->getMessage();}

这里写图片描述

try {    $dsn = 'mysql:host=localhost;dbname=test';    $pdo = new PDO($dsn,'root','123456');    $sql = "select * from user where username=:username and password=:password";    $stmt = $pdo->prepare($sql);    $stmt->bindParam(':username',$username,PDO::PARAM_STR);    $stmt->bindParam(':password',$password,PDO::PARAM_STR);    $username = 'testParam';    $password = 'testParamaaa';    $stmt->execute();    echo '<pre>';    $stmt->debugDumpParams();    echo '</pre>';} catch (PDOException $e) {    echo $e->getMessage();}

8. nextRowset()

创建存储过程：create procedure test1()BEGIN  select * from user;  select * from qqlogin0;END

这里写图片描述

try {    $dsn = 'mysql:host=localhost;dbname=test';    $pdo = new PDO($dsn,'root','123456');    $sql = "call test1()";    $stmt = $pdo->query($sql);    $rowset = $stmt->fetchAll(PDO::FETCH_ASSOC);    var_dump($rowset);    echo '<br/>*****************第二个结果集*************<br/>';    $stmt->nextRowset();    $rowset2 = $stmt->fetchAll(PDO::FETCH_ASSOC);    var_dump($rowset2);} catch (PDOException $e) {    echo $e->getMessage();}

0 0