连接
try{
$conn=new PDO('mysql:host=localhost;port=3306;dbname=pdo_test','root','root');
}catch(PDOException $e){
echo $e->getMessage();
}
$pdo->query('set names utf8');
插入
$sql = "insert into persons (name,age) values (?, ?);"; $preObj = $pdo->prepare($sql); //object(PDOStatement)#2 (1) { ["queryString"]=> string(45) "insert into persons (name,age) values (?, ?);" } //$preObj->bindValue(1,'小明'); //$preObj->bindValue(2,22); $res = $preObj->execute(array('小1明', 22)); var_dump($res); //true or false
删除
$sql = "delete from persons where id = ?"; $preObj = $pdo->prepare($sql); //object(PDOStatement)#2 (1) { ["queryString"]=> string(45) "delete from persons where id = ?" } //$preObj->bindValue(1,3); $res = $preObj->execute(array(3)); var_dump($res); //true or false
修改
$sql = "update persons set name = ? where id = ?;"; $preObj = $pdo->prepare($sql); //$preObj->bindValue(1,'lucy'); //$preObj->bindValue(2,5); $res = $preObj->execute(array('lucy', 5)); var_dump($res); //true or false 若数据表里没有id为5的也返回true
查询
$sql = "select * from persons where age > ? order by id desc;"; $preObj = $pdo->prepare($sql); //$preObj->bindValue(1,20); $preObj->execute(array(20)); $arr = $preObj->fetchAll(PDO::FETCH_ASSOC); //不加参数 则返回 关联和索引数组 echo "<pre>"; var_dump($arr); echo "</pre>"; --------------------------- array(1) { [0]=> array(3) { ["id"]=> string(1) "0" ["name"]=> string(7) "小1明" ["age"]=> string(2) "22" } } ========================================================= /* * FETCH_BOTH 是默认的,可省,返回关联和索引。 * FETCH_ASSOC 参数决定返回的只有关联数组。 * PDO::FETCH_NUM 返回索引数组 * PDO::FETCH_OBJ 返回由对象组成的二维数组 */ ----------------------------------- // FETCH_BOTH array(1) { [0]=> array(6) { ["id"]=> string(1) "0" [0]=> string(1) "0" ["name"]=> string(7) "小1明" [1]=> string(7) "小1明" ["age"]=> string(2) "22" [2]=> string(2) "22" } } ------------------------------------------ FETCH_ASSOC array(1) { [0]=> array(3) { ["id"]=> string(1) "0" ["name"]=> string(7) "小1明" ["age"]=> string(2) "22" } } ------------------------------------ PDO::FETCH_NUM array(1) { [0]=> array(3) { [0]=> string(1) "0" [1]=> string(7) "小1明" [2]=> string(2) "22" } } -------------------------- PDO::FETCH_OBJ array(1) { [0]=> object(stdClass)#3 (3) { ["id"]=> string(1) "0" ["name"]=> string(7) "小1明" ["age"]=> string(2) "22" } } */
rowCount();
$sql = "delete from persons where id = ?"; $preObj = $pdo->prepare($sql); $res = $preObj->execute(array(16)); $rowCount = $preObj->rowCount(); //注意是$preObj调用此方法 echo "受到影响的数据条目 : {$rowCount}";
lastInsertId();
$sql = "insert into persons (name,age) values (?, ?);"; $preObj = $pdo->prepare($sql); $res = $preObj->execute(array('小明', 22)); $lastInsertId = $pdo->lastInsertId(); //注意是$pdo调用此方法 echo "刚刚插入数据的id为 {$lastInsertId}";
PDOStatement::bindParam 与 PDOStatement::bindValue()区别
不同点:
bindValue(); 赋值传参
bindParam(); 引用传参
============================================================================
例子1:
$sql="insert into persons (name,age) VALUE (?,?);";
$pdoObj=$pdo->prepare($sql);
$a=time();
$pdoObj->bindParam(1,$a); //true
$pdoObj->bindParam(1,"10"); //false 不可以绑定常量 没有引用
---------------------
$pdoObj->bindValue(1,$a); //true
$pdoObj->bindValue(1,"10"); //true
=============================================================================
例子2:
<?php
$sex = 'male';
$s = $dbh->prepare('SELECT name FROM students WHERE sex = :sex');
$s->bindParam(':sex', $sex);
$sex = 'female';
$s->execute();
// 将执行 WHERE sex = 'female' 引用传参,所以后期改变了$sex的值,结果也会随之变化
----------------------
<?php
$sex = 'male';
$s = $dbh->prepare('SELECT name FROM students WHERE sex = :sex');
$s->bindValue(':sex', $sex);
$sex = 'female';
$s->execute(); // 将执行 WHERE sex = 'male' 直接赋值
PDO报错和返回结果集模式设置
try{
$pdo=new PDO('mysql:host=localhost;dbname=pdo_test',"root","root");
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
//报错模式设为打印任何错误
/*
PDO::ERRMODE_SILENT 静默,不报错误
PDO::ERRMODE_WARNING 提示
PDO::ERRMODE_EXCEPTION 抛出任何错误
*/
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);
//获取结果集返回关联数组
/*
PDO::FETCH_ASSOC 关联数组
PDO::FETCH_NUM 索引数组
PDO::FETCH_BOTH 关联+索引(默认)
PDO::FETCH_OBJ 对象形式的数组
*/
}catch(PDOException $e){
$e->getMessage();
}
PDO事务机制
$pdo->beginTransaction(); //开启事务机制
$pdo->commit(); //若无错,则提交事务
$pdo->getFile(); //获取错误所在的文件
$pdo->getLine(); //获取错误行数
$pdo->rollBack(); //若失败,回滚操作
======================================================
<?php
try{
$pdo=new PDO('mysql:host=localhost;dbname=pdo_test',"root","root");
$pdo->beginTransaction(); //开启事务机制
$sql="insert into persons (name,age) values (?,?)";
$smt=$pdo->prepare($sql);
$smt->bindValue(1,'张三');
$smt->bindValue(2,20);
$smt->execute();
$pdo->commit(); //若无错,则提交事务
}catch (PDOException $e){
echo $e->getMessage; //打印错误信息
$pdo->getFile(); //获取错误所在的文件
$pdo->getLine(); //获取错误行数
$pdo->rollBack(); //若失败,回滚操作
}