PHP数据库操作总结

php链接数据库操作我所知道的总共有3种方式，分别是mysql扩展库，mysqli扩展库，pdo三种方式，花了两天时间做练习总结了一下，下面贴出了相关代码。

一.使用mysql扩展库操作数据库

<?php
	//使用mysql扩展
	header("Content-Type:text/html;charset=utf-8");
	
	$server="localhost";
	$username="root";
	$password="";
	
	$link=mysql_connect($server,$username,$password) or die("连接失败".mysql_errno().":".mysql_error()."<br>");
	mysql_select_db("test", $link) or die("选择数据库失败".mysql_errno().":".mysql_error()."<br>");
    mysql_query("set names 'UTF8'");

	$sql="insert into student(id,name,age,sex) values('','zhangsan','22','男')"; 
	$result=mysql_query($sql);//mysql_query()执行select语句返回资源，执行非select返回boolean值
	if($result)
	{
		$affected_rows=mysql_affected_rows();
		echo "影响行数".$affected_rows."<br>";
	}
	else
	{
		echo "操作失败:".mysql_errno().":".mysql_error()."<br>";
		
	}
	
	//处理结果集
	//mysql_fetch_row($result)   //返回索引数组
	//mysql_fetch_assoc($result) //返回关联数组（下标：就是列表）
	//mysql_fetch_array($result) //返回索引和关联两个数组
	//mysql_fetch_object($result)//将一条记录以对象的形式返
	//将指针移到到下一条记录（默认是第一记录,mysql_data_seek($result,row))
	
	$sql="select * from student";
	$result=mysql_query($sql);
	while($row=mysql_fetch_assoc($result))
	{
		foreach($row as $key=>$value)
		{
			echo $key."========>".$value."<br>";
		}
	}
	mysql_free_result($result);
	mysql_close();
?>

二.使用mysqli扩展库操作数据库

<?php
	//使用mysqli扩展
	header("Content-Type:text/html;charset=utf-8");
	
	$server="localhost";
	$username="root";
	$password="";
	$database_name="test";
	
	$mysqli=new mysqli($server,$username,$password,$database_name) or die("连接数据库出错：".mysqli_connect_errno()."->".mysqli_connect_error()."<br>");
	$mysqli->set_charset("utf8");
	
	
	$sql="insert into student(id,name,age,sex) values('','lisi','22','男')"; 
	$result=$mysqli->query($sql);
	if($result)
	{
		echo "影响行数：".$mysqli->affected_rows."<br>";
	}
	else
	{
		echo "操作失败：".$mysqli->errno."->".$mysqli->error."<br>";
	}
	
	$sql="select * from student";
	$result=$mysqli->query($sql);
	if($result)
	{
		while($row=$result->fetch_assoc()) 
		{
			foreach($row as $key=>$value)
			{
				echo "mysqli测试：".$key."========>".$value."<br>";
			}
		}
	}
	else
	{
		echo "操作失败：".$mysqli->errno."".$mysqli->error."<br>";
	}

	//执行多条sql语句：$mysqli->multi_query($sqls);$result=$mysqli->store_result();$row=$result->fetch_assoc();
	
	//事务处理
	$mysqli->autocommit(0);//关闭自动提交
	$error=true;//设置错误变量
	
	$sql="insert into student(id,name,age,sex)values('','王五','22','男')";
	$result=$mysqli->query($sql);
	if($result)
	{
		echo "王五插入成功！<br>";
	}
	else
	{
		$error=false;
		echo "王五插入失败！错误：".$mysqli->errno.":".$mysqli->error."<br>";
	}
	
	$sql="insert into student(id,name,age,sex)values('','赵六','20','女')";
	$result=$mysqli->query($sql);
	if($result)
	{
		echo "赵六插入成功！<br>";
	}
	else
	{
		$error=false;
		echo "赵六插入失败！错误：".$mysqli->errno.":".$mysqli->error."<br>";
	}
	
	if($error==true)
	{
		$mysqli->commit();
		echo "操作成功！<br>";
	}
	else
	{
		$mysqli->rollback();
		echo "操作失败！<br>";
	}
	$mysqli->autocommit(1);//打开自动提交
	 
	 //mysqli预处理
	 $sql="insert into student(id,name,age,sex) values(?,?,?,?)";
	 $stmt=$mysqli->prepare($sql);
	 //给占位符号每个?号传值（绑定参数） i  d  s  b
	 $stmt->bind_param("isis",$id,$name,$age,$sex);
	 
	 $id="";
	 $name="zhang";
	 $age=22;
	 $sex="男";
	 
	 $stmt->execute();
	 
	 if($stmt)
	 {
		echo "影响行数：".$stmt->affected_rows."<br>";
	}
	else
	{
		echo "操作失败：".$mysqli->errno."".$mysqli->error."<br>";
	}
	 
	 
	 $sql="select * from student where name=?";
	 $stmt=$mysqli->prepare($sql);
	 $stmt->bind_param("s",$name);
	 $stmt->bind_result($id,$name,$age,$sex);
	 
	 $name="zhang";
	 $stmt->execute();
	 
	 $stmt->store_result();//获取结果
	 while($row=$stmt->fetch())
	 {
		 echo "$id----$name----$age----$sex<br>";
	}
	 
	 $stmt->free_result();
	 $stmt->close();
	 
	 	 
	//$result->free();	
	$mysqli->close();
?>

三.使用PDO操作数据库

<?php
	//使用PDO连接数据库
	header("Content-Type:text/html;charset=utf-8");
	//数据库配置信息
	$dsn="mysql:host=localhost;dbname=test";
	$username="root";
	$password="";
	
	try
	{
		$pdo=new PDO($dsn,$username,$password);
		$pdo->exec("set names utf8");
	}
	catch(PDOException $e)
	{
		echo "连接数据库错误：".$e->getMessage();
		exit;
	}
	
	/*执行SQL语句：
	  exec()//执行非select语句
	  query()//执行select语句
	  prepare()//预处理
	 */
	
	 $sql="insert into student(id,name,age,sex) values('','BurNing','26','男')";
	 if($affected_rows=$pdo->exec($sql))
	 {
		echo "影响行数：".$affected_rows."<br>";	
	}
	else
	{
		echo "操作失败：<br>";
		echo "<pre>";
		print_r($pdo->errorInfo());
		echo "</pre>";
	}
	
	$sql="select * from student where name='BurNing'";
	$stmt=$pdo->query($sql);
	foreach($stmt as $row)
	{
		echo "<pre>";
		print_r($row);
		echo "</pre>";
	}
	
	//事务处理
	$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 0);//关闭自动提交
	$pdo->beginTransaction();//开启事务
	
	try
	{
	 	$sql="insert into student(id,name,age,sex) values('','gaybe','18','male')";
		if(!$pdo->exec($sql))
		{
			throw new PDOException("gaybe插入失败");
		}
		
		$sql="select * from student where name='gaybe'";
		if($stmt=$pdo->query($sql))
		{
			foreach($stmt as $row)
			{
				echo "<pre>";
				print_r($row);
				echo "</pre>";
			}
		}
		else
		{
			throw new PDOException("gaybe查询失败");
		}
		
	}
	catch(PDOException $e)
	{
		echo $e->getMessage();
		$pdo->rollback();
	}
	$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 1);//打开自动提交
	
	
	//PDO预处理
	$sql="insert into student(id,name,age,sex) value (?,?,?,?)";
	$stmt=$pdo->prepare($sql);
	$array=array("","hang","22","男");
	$stmt->execute($array);
	
	$sql="select * from student where name=?";
	$stmt=$pdo->prepare($sql);
	$array=array("hang");
	$stmt->execute($array);
	while($row=$stmt->fetch())
	{
		echo "<pre>";
		print_r($row);
		echo "</pre>";
	}
	
?>