增、删、改
<?php
try{
//设置dsn
$dsn = 'mysql:host=localhost;dbname=test;charset=utf8';
//连接数据库
$pdo = new PDO($dsn,'root','');
//设置SQL语句
$sql = "insert into user values(null,'大力哥','123456','18')"; //添加
// $sql = "update user set name = '在大力' where id between 106 and 108"; //修改
// $sql = "delete from user where id between 106 and 108";//删除
//4.发送执行SQL语句
$result = $pdo->exec($sql);
//5.判断执行结果
if($result){
echo 'success';
}else{
echo 'error';
}
}catch(PDOException $e){
//获取pdo自动抛出的异常对象
echo $e->getMessage();
}catch(Exception $e){
//获取pdo自动抛出的异常对象
echo $e->getMessage();
}
查
1.使用?占位符, 绑定列获取结果
<?php
try{
//设置dsn
$dsn = 'mysql:host=localhost;dbname=test;charset=utf8';
//连接数据库
$pdo = new PDO($dsn,'root','');
//设置SQL语句
$sql = "select id,name,password,age from user where id between ? and ? and age > ?";
//PDO预处理
$stmt = $pdo->prepare($sql);
//绑定参数
$stmt->bindParam(1,$begin);
$stmt->bindParam(2,$end);
$stmt->bindParam(3,$age);
$begin = 1;
$end = 105;
$age = 10;
//执行
$stmt->execute();
//判断操作是否成功
if($stmt->rowCount()){
//遍历结果集
//绑定列操作
$stmt->bindColumn(1,$id);
$stmt->bindColumn(2,$name);
$stmt->bindColumn(4,$age);
//循环操作
while($stmt->fetch(PDO::FETCH_ASSOC)){
echo $id.'---'.$name.'---'.$age.'<br/>';
}
}else{
echo '无数据';
}
}catch(PDOException $e){
//获取pdo自动抛出的异常对象
echo $e->getMessage();
}catch(Exception $e){
//获取pdo自动抛出的异常对象
echo $e->getMessage();
}
2.使用 : 占位符, 循环获取结果
<?php
try{
//设置dsn
$dsn = 'mysql:host=localhost;dbname=test;charset=utf8';
//连接数据库
$pdo = new PDO($dsn,'root','');
//设置SQL语句
$sql = "select id,name,password,age from user where id between :begin and :end and age > :age";
//PDO预处理 PDOStatement
$stmt = $pdo->prepare($sql);
//绑定参数
$stmt->bindParam(':begin',$begin);
$stmt->bindParam(':end',$end);
$stmt->bindParam(':age',$age);
$begin = 1;
$end = 105;
$age = 10;
//执行操作
$stmt->execute();
//判断操作是否成功
if($stmt->rowCount()){
//循环获取结果集
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
var_dump($row);
}
}else{
echo '查询失败';
}
}catch(PDOException $e){
//获取pdo自动抛出的异常对象
echo $e->getMessage();
}catch(Exception $e){
//获取pdo自动抛出的异常对象
echo $e->getMessage();
}
3.使用 ?或: 占位符, 直接获取结果
<?php
try{
//设置dsn
$dsn = 'mysql:host=localhost;dbname=test;charset=utf8';
//连接数据库
$pdo = new PDO($dsn,'root','');
//设置SQL语句
// $sql = "select id,name,password,age from user where id between ? and ? and age > ?";
$sql = "select id,name,password,age from user where id between :begin and :end and age > :age";
//PDO预处理 PDOStatement
$stmt = $pdo->prepare($sql);
//执行操作
// $stmt->execute(array(1,105,10)); //?定位符为索引数组
$stmt->execute(array('begin'=>1,'end'=>105,'age'=>10)); //:xxx定位符为关联数组
//判断操作是否成功
if($stmt->rowCount()){
//获取结果
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($rows);
}else{
echo '查询失败';
}
//5.判断执行结果
}catch(PDOException $e){
//获取pdo自动抛出的异常对象
echo $e->getMessage();
}catch(Exception $e){
//获取pdo自动抛出的异常对象
echo $e->getMessage();
}