PDO 预处理

连接

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();	//若失败，回滚操作
	
}