PDO对象的方法
方法 | 描述 |
---|---|
exec() | 执行一条SQL语句并返回受影响的行数 |
query() | 执行一条SQL语句并返回一个PDOStatement对象 |
prepare() | 准备要执行的SQL语句,返回PDOStatement对象 |
quote() | 为某个SQL中的字符串添加引号 |
lastInsertId() | 返回最后一条插入到表中数据的主键值 |
setAttribute() | 设置数据库连接属性 |
getAttribute() | 得到数据库连接的属性 |
errorCode() | 获取SQLSTATE(错误码) |
errorInfo() | 获取错误信息 |
getAvailableDrivers() | 获取有效的 PDO驱动器名称 |
方法 | 描述 |
---|---|
execute() | 执行一条预处理语句 |
debugDumpParams() | 打印一条 SQL预处理命令 |
rowCount() | 返回上一个SQL语句影响的行数 |
columnCount() | 返回结果集中的列数 |
fetch() | 从结果集中获取一行 |
fetchAll() | 返回一个包含结果集中所有行的数组 |
setFetchMode() | 为语句设置默认的获取模式 |
fetchColumn() | 从结果集中的下一行返回单独的一列 |
fetchObject() | 获取下一行并作为一个对象返回 |
bindParam() | 绑定一个参数到指定的变量名 |
bindValue() | 把一个值绑定到一个参数 |
bindColumn() | 绑定一列到一个php变量 |
getColumnMeta() | 返回结果集中一列的元数据 |
nextRowset() | 将结果集中指针下移 |
exec()
lastInsertId()
- exec()执行一条SQL语句并返回受影响的行数,若没有受影响条数则返回0
- exec()对 select没有作用
- lastInsertId()只针对 insert语句
try{
$pdo = new PDO('mysql:host=localhost;dbname=shopimooc', 'root', 'root');
$sql = 'INSERT user(username,password,email) VALUES("xx","'.md5("xxxx").'","xx@qq.com")';
//$sql = 'UPDATE user SET username="xx" WHERE id=5';
//$sql = 'DELETE FROM user WHERE id=5';
$res = $pdo->exec($sql);
echo $pdo->lastInsertId();
}catch (PDOException $e){
echo $e->getMessage();
}
errorCode() [返回SQLSTATE]
errorInfo() [0=>SQLSTATE,1=>错误编号,2=>错误信息]
try{
$pdo = new PDO('mysql:host=localhost;dbname=shopimooc', 'root', 'root');
$sql = 'DELETE user WHERE id=5';
$pdo->exec($sql);
echo $pdo->errorCode();
echo "<br>";
print_r($pdo->errorInfo());
}catch (PDOException $e){
echo $e->getMessage();
}
query() [主要用于 select]
try{
$pdo = new PDO('mysql:host=localhost;dbname=shopimooc', 'root', 'root');
$sql = 'SELECT * FROM user';
//$sql = 'INSERT user(username,password,email) VALUES("ll","'.md5("lll").'","ll@qq.com")';
$res = $pdo->query($sql);
foreach ($res as $row) {
echo $row['id'];
echo $row['username'];
echo $row['email'];
echo "<hr>";
}
}catch (PDOException $e){
echo $e->getMessage();
}
prepare()
execute()
fetch()
$sql = 'SELECT * FROM user WHERE username="xx"';
$stmt = $pdo->prepare($sql);
$res = $stmt->execute();
// var_dump($res); bool(true)
$row = $stmt->fetch();
print_r($row);
fetchAll()
$sql = 'SELECT * FROM user';
$stmt = $pdo->prepare($sql);
$res = $stmt->execute();
// if($res){
// while ($row = $stmt->fetch()){
// print_r($row);
// echo "<hr>";
// }
// }
$row = $stmt->fetchAll();
print_r($row);
setFetchMode()
// PDO::FETCH_ASSOC 返回关联部分;
// PDO::FETCH_BOTH 返回关联加索引;
// PDO::FETCH_OBJ 返回对象
// if($res){
// while ($row = $stmt->fetch(PDO::FETCH_OBJ)){
// print_r($row);
// echo "<hr>";
// }
// }
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$row = $stmt->fetchAll();
print_r($row);
setAttribute(属性名称,值)
getAttribute(属性名称)
echo "自动提交:".$pdo->getAttribute(PDO::ATTR_AUTOCOMMIT); //1
echo "<br>";
echo "错误处理模式:".$pdo->getAttribute(PDO::ATTR_ERRMODE);
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 0);
echo "<br>";
echo "自动提交:".$pdo->getAttribute(PDO::ATTR_AUTOCOMMIT); //0
$op = array(PDO::ATTR_AUTOCOMMIT=>0, PDO::ATTR_ERRMODE=>2);
$pdo = new PDO('mysql:host=localhost;dbname=shopimooc', 'root', 'root', $op);
echo "自动提交:".$pdo->getAttribute(PDO::ATTR_AUTOCOMMIT); //0
rowCount() [对于 select操作返回结果集中记录的条数]
quote() [返回带引号的字符串,过滤字符串中的特殊字符,防SQL注入]
$username = $_POST['username'];
$passwd = md5($_POST['password']);
try{
$pdo = new PDO('mysql:host=localhost;dbname=shopimooc','root','root');
$username = $pdo->quote($username);
$sql = "select * from user where username={$username} and password='{$passwd}'";
// echo $sql;
$stmt = $pdo->query($sql);
echo $stmt->rowCount();
}catch (PDOException $e){
$e->getMessage();
}
也可通过 prepare()和 execute()来防止SQL注入(占位符)
$username = $_POST['username'];
$passwd = md5($_POST['password']);
try{
$pdo = new PDO('mysql:host=localhost;dbname=shopimooc','root','root');
$sql = "select * from user where username=:username and password=:passwd";
//$sql = "select * from user where username=? and password=?";
$stmt = $pdo->prepare($sql);
$stmt->execute(array(":username"=>$username,":passwd"=>$passwd));
//$stmt->execute(array($username,$passwd));
echo $stmt->rowCount();
}catch (PDOException $e){
$e->getMessage();
}
bindParam()
bindValue() 和 bindParam()用法差不多,如果插入 sql1和sql2语句中的v1和v2的值相同,可以只绑定一次
占位符
$sql = "insert user(username,password,email) values(:username,:password,:email)";
$stmt = $pdo->prepare($sql);
//$sql = "insert user(username,password,email) values(?,?,?)";
$stmt->bindParam(":username",$username,PDO::PARAM_STR); //默认PARAM_STR字符串
$stmt->bindParam(":password",$password);
$stmt->bindParam(":email",$email);
//$stmt->bindParam(1,$username);
//$stmt->bindParam(2,$password);
//$stmt->bindParam(3,$email);
$username = 'admin';
$password = md5('admin');
$email = 'admin@qq.com';
$stmt->execute();
bindColumn()
getColumnMeta() [返回数组,第一列的索引为0]
columnCount()
$sql = "select username,email from user";
$stmt = $pdo->prepare($sql);
$stmt->execute();
echo $stmt->columnCount().'<hr>';
print_r($stmt->getColumnMeta(0));
echo '<hr>';
$stmt->bindColumn(1,$username);
$stmt->bindColumn(2,$email);
while($stmt->fetch(PDO::FETCH_BOUND)){
echo '用户名:'.$username.'-邮箱:'.$email.'<hr>';
}
fetchColumn()
索引从0开始,若第一次 fetchColumn(0)则返回第一列第一个值,第二次 fetchColumn(1)时(相当把指针向下移动一位),返回的是第二列第二个值,它没有办法返回第二列第一个值
$sql = "select username,email from user";
$stmt = $pdo->prepare($sql);
$stmt->execute();
echo $stmt->fetchColumn(0);