php连接mysql

mysql 扩展库连接：

<?php
//mysql 扩展库操作mysql数据库步骤如下

//1.获取连接

 $conn = mysql_connect("localhost", "root", "root");
 
 if(!$conn){
 	die("Could not connect: " . mysql_error());
 }else{
 	echo "connect success<br/>";
 }


//2.选择数据库

 mysql_select_db("php");
 
//3.设置操作编码
mysql_query("set names gbk");
 
//4.发送指令sql(ddl 数据定义语句,dml（数据操作语句 update,insert,delete）,dql(select ).dtl(事务语句 rollback commit...))
$sql = "select * from user1";
//5.接收返回的结果，并且处理
$result = mysql_query($sql,$conn) or die("Invalid query: " . mysql_error());

while ($row = mysql_fetch_row($result)){
// 	echo "<br/>$row[0]--$row[1]--$row[2]";

	foreach ($row as $key=>$val){
		echo "---$val";
	}
	echo "<br/>";
}

//6.释放资源，关闭连接
mysql_free_result($result);
mysql_close($conn);//这句代码可以不写，建议写上
?>

dml语句实例：

<?php
$conn=mysql_connect("localhost","root","root");

if(!$conn){
	die("err".mysql_error());
}

mysql_select_db("php") or die("select err".mysql_error());

mysql_query("set names gbk");

//$sql = "insert into user1(name,psw,email,age) values('小明',md5('123'),'xiaoming.qq.com',34)";

//$sql = "delete from user1 where id=8";

$sql = "update user1 set name='金玮' where id=3";
$res = mysql_query($sql,$conn);


if(!$res){
	die("query err".mysql_error());
	
	return;
}


if(mysql_affected_rows($conn) > 0){
	echo "<p>success query and some data find</p><br/>";
}else {
	echo "<p>没有受影响的行数。</p><br/>";
}



?>

封装mysql 操作：

<?php

	header("Content-type: text/html;charset=utf-8");
	
	class SqlTool{
		
		private $conn;
		private $host="localhost";
		private $user ="root";
		private $password="root";
		private $db = "vibc";
		
		function SqlTool(){
			$this->conn = mysql_connect($this->host,$this->user,$this->password);
			
			if(!$this->conn){
				die("connct mysql db failed.".mysql_error());
			}
			
			mysql_select_db($this->db,$this->conn);
			mysql_query("set names gbk");
		}
		
	public function close_connect(){
			if(!empty($this->conn)){
				mysql_close($this->conn);
			}
		}
		
		public function execute_dql($sql){
			$res = mysql_query($sql,$this->conn) or die("execute_sql error".mysql_error());
			return $res;
		}
		
		 function execute_dql2($sql){
			$arr = array();
			
			$res = mysql_query($sql,$this->conn) or die("execute_sql error".mysql_error());
			
			$i=0;
			while($row = mysql_fetch_assoc($res)){
				$arr[$i++]=$row;
			}
			mysql_free_result($res);
			return $arr;
		}
		
	    function execute_dml($sql){
			$b = mysql_query($sql,$this->conn);
			
			if(!$b){
				return -1;//failed
			}elseif(mysql_affected_rows($this->conn) > 0){
				return 0;//success
			}else{
				return 1;//success but no data
			}
		}
	}

?>

mysqli 扩展库连接 mysql

面向对象方式：

<?php

	header("Content-type: text/html;charset=utf-8");
	
	$mysqli = new mysqli("localhost", "root", "root", "php");
	
	if ($mysqli->connect_error) {
		die("connect error(".$mysqli->connect_errno.")");
	}
	
	echo "connect success.<br/>";
	
	//发送数据
	$sql = "select * from user1";
	$res = $mysqli->query($sql);
	
	//获取数据
	 while ($row = $res->fetch_row()) {
        //printf ("%d--%s---%s---%s---%d)<br/>", $row[0], $row[1],$row[2],$row[3],$row[4]);
        
	 	foreach ($row as $key=>$val){
	 		echo "----$val";
	 	}
	 	
	 	echo "<br/>";
    }

    /* free result set */
    //释放内存
    $res->free();

    //关闭连接
    $mysqli->close();
?>

mysqli 面向过程的方式：

<?php
	header("Content-type: text/html;charset=utf-8");
	$mysqli = mysqli_connect("localhost","root", "root","php");
	
	if(!$mysqli){
		die("connect failed ".mysqli_connect_error($mysqli));
	}
	
	$sql = "select * from user1";
	
	$res = mysqli_query($mysqli, $sql);
	
	while($row = mysqli_fetch_row($res)){
	
		foreach ($row as $key=>$val){
			echo "---$val";
		}
		
		echo "<br/>";
	}
	

	mysqli_free_result($res);
	
	mysqli_close($mysqli);
?>

批量执行dml语句：

<?php
	header("Content-type: text/html;charset=gbk");

	//批量执行sql语句
	$mysqli = new mysqli("localhost", "root", "root", "php");
	
	if($mysqli->connect_error){
		die($mysqli->connect_error);
	}
	
	$sql = "insert into user1(name,psw,email,age) values('zjw',md5('123'),'zjw@sohu.com',24);";
	
	$sql .= "insert into user1(name,psw,email,age) values('sonjiang',md5('123'),'sonjiang@sohu.com',45);";
	
	$sql .= "insert into user1(name,psw,email,age) values('lujueyi',md5('123'),'lujunyi@sohu.com',45);";
	
	$b = $mysqli->multi_query($sql);
	
	
	if($b){
		echo "执行成功！<br/>";
	}else{
		echo "执行失败！<br/>";
		echo $mysqli->error;
	}
	
	$mysqli->close();
	
	
?>

批量执行dql语句

<?php
	header("Content-type: text/html;charset=utf-8");
	
	$mysqli =new mysqli("localhost", "root", "root", "php");
	
	if($mysqli->connect_error){
		die($mysqli->connect_error);
		exit();
	}
	
	
	$sqls = "select * from user1;";
	$sqls .= "select * from user2;";
	
	if($res = $mysqli->multi_query($sqls)){
		do{
			if ($result = $mysqli->store_result()) {
				while ($row = $result->fetch_row()) {
					
					foreach ($row as $key=>$val){
						echo "---$val";
					}
					
					echo "<br/>";
				}
				$result->free();
			}
			/* print divider */
			if ($mysqli->more_results()) {
				echo "<br/>---------new results find----------------<br/>";	
			}else{
				break;
			}
		}while($mysqli->next_result());
	}
	
	/* close connection */
	$mysqli->close();
?>

mysql 事务：

<?php

//事务用于保证数据的一致性
	$mysqli = new mysqli("localhost", "root", "root", "php");
	
	if($mysqli->connect_error){
		die($mysqli->connect_error);
		exit();
	}
	
	/* set autocommit to off */
	$mysqli->autocommit(FALSE);

	$sql1 = "update account set balance=balance-2 where id=1";
	$sql2 = "update account2 set balance=balance+2 where id=2";
	
	$b1 = $mysqli->query($sql1) ;
	$b2 = $mysqli->query($sql2) ;
	
	
	
	if(!$b1 || !$b2) {
		echo "failed<br/>".$mysqli->error;
		$mysqli->rollback();
	}else{
		echo "success<br/>";
		/* commit transaction */
		$mysqli->commit();
	}
		
	$mysqli->close();
?>