3. PDOStatement 对象

最新推荐文章于 2021-03-10 12:16:27 发布

enlyhua

最新推荐文章于 2021-03-10 12:16:27 发布

阅读量834

点赞数

文章标签：对象

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

<?php

header('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.预处理占位符

<?php

header('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();
}