php使用pdo连接mysql增删改查

增、删、改

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