mysqli扩展库

mysqli使用解析

通过mysqli类库操作数据库的步骤

• 建立到mysql的连接
• 打开指定的数据库
• 设置默认客户端的字符集
• 执行sql语句
• 释放结果集
• 关闭连接

建立连接的同时打开指定数据库

<?php
	//1.建立到mysql的连接，同时打开指定的数据库
	$msyqli = @new mysqli('localhost','root','root','test');
	//$mysqli->connect_errno:得到连接产生的错误编号
	//$mysqli->connect_error:得到连接产生的错误信息
	if($mysqli->connect_errno) {
		die('Connect Error :'.$mysqli->connect_error);
	}

	echo '<hr color="red">';
	echo '客户端的信息：'.$mysqli->client_info.'<br />';
	echo $mysqli->get_client_info().'<br />';

	echo '客户端的版本：'.$mysqli->client_version.'<br />';
	echo '<hr />';
	
	echo '服务器端信息：'.$mysqli->server_info.'<br />';
	echo $mysqli->get_server_info();
	echo '<hr />';

	echo '服务器版本：'.$mysqli->server_version.'<br />';
	echo '<hr />'

	//2.设置默认的客户端编码方式utf8
	$mysqli->set_charset('utf8');

	//3.执行sql查询
	$sql = <<<EOF
		CREATE TABLE IF NOT EXISTS mysqli(
			id TINYINT UNSIGNED AUTO_INCREMENT KEY,
			username VARCHAR(20) NOT NULL
		);
	EOF;
	/*
		SELECT/DESC/DESCRIBE/SHOW/EXPLAIN执行成功返回结果集对象，失败返回false
		对于其它sql语句的执行，执行成功返回true，否则返回false
	*/

	//4.释放结果集（这里没有，省略）

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

---

实现插入记录操作

<?php
	header('content-type:text/html;charset=UTF-8');
	$mysqli = @new mysqli('localhost','root','root','test');
	if($mysqli->connect_errno) {
		die('CONNECT ERROR:'.$mysqli->connect_error);
	}
	
	$mysqli->set_charset('utf8');

	//执行sql查询
	//添加记录，执行单条sql语句
	$sql = "INSERT INTO user (username,password) VALUES('queen1','queen1'),(queen2,queen2),(queen3,queen3);";
	$res= $mysqli->query($sql);
	if($res) {
		//得到上一步插入操作产生的AUTO_INCREMENT的值
		echo '恭喜您注册成功，你是网站的第'.$mysqli->insert_id.'位用户<br />';
		//得到上一步操作产生的受影响记录条数
		echo '有'.$mysqli->affected_rows.'条记录受影响';
	} else {
		echo 'ERROR '.$mysqli->errno.':'.$mysqli->error;
	}
	
	$mysqli->close();
?>

---

实现更新记录操作

<?php
	header('content-type:test/html;charset=UTF-8');

	$mysqli = new mysqli('localhost','root','root','test');
	if($mysqli->connect_errno) {
		exit('CONNECT ERROR:'.$mysqli->connect_error);
	}

	$mysqli->set_charset('utf8');
	
	//更新数据
	$sql = "UPDATE user SET age += 10";
	$res = $mysqli->query($sql);
	if($res) {
		echo $mysqli->affected_rows.'条记录被更新';
	}	else {
		echo 'ERROR '.$mysqli->errno.':'.$mysqli->error;
	}


	$mysqli->close();
?>

实现删除记录操作

<?php
	header('content-type:text/html;charset=utf-8');

	$mysqli= new mysqli('localhost','root','root','test');
	if($mysqli->connect_errno) {
		exit('CONNECT ERROR:'.$mysqli->connect_error)
	}
	
	$mysqli->set_charset('utf8');

	//删除用户
	$sql = "DELETE FROM user WHERE id <= 6";
	$res = $mysqli->query($sql);
	if($res) {
		echo $mysqli->affected_rows.'条记录被删除';
	} else {
		echo 'ERROR '.$mysqli->errno.':'.$mysqli->error;
	}

	$mysqli->close();
?>

注意：
affected_rows的值为三种：

• 受影响记录条数
• -1，代表sql语句有问题
• 0，代表没有受影响记录的条数

---

实现查询记录的操作

<?php
	header('content-type:text/html;charset=utf-8');
	$mysqli = new mysqli('localhost','root','root','test');
	if($mysqli->connect_errno) {
		exit('CONNECT ERROR:'.$mysqli->connect_error);
	}

	$mysqli->set_charset('utf8');
	
	//查询
	$sql = "SELECT id,username,age FROM user";
	$mysqli_result = $mysqli->query($sql);
	if($mysqli_result && $mysqli_result->num_rows>0) {
		$mysqli_result->fetch_all();//获取结果集中所有记录，默认返回的是二维的，索引+索引的形式
		$rows = $mysqli_result->fetch_all(MYSQLI_NUM);
		$rows = $mysqli_result->fetch_all(MYSQLI_ASSOC);
		$rows = $mysqli_result->fetch_all(MYSQLI_BOTH);
		$row = $mysqli_result->fetch_row();//取得结果集中一条记录作为索引数组返回
		$row = $mysqli_result->fetch_assoc();//取得结果集中的一条记录作为关联数组返回
		$row = $mysqli_result->fetch_array();
		$row = $mysqli_result->fetch_array(MYSQLI_ASSOC);
		$row = $mysqli_result->fetch_object();

		//移动结果集内部指针
		$mysqli_result->data_seek(0);
		$row = $mysqli->fetch_assoc();

		while($row = $mysqli->fetch-assoc()) {
			$rows[] = $row;
		}
		$mysqli_result->free();
	} else {
		echo '查询错误或者结果集中没有记录';
	}

	$mysqli->close();
?>

---

mysqli针对多条sql语句的执行

<?php
	header('content-type:text/html;charset=utf-8');
	$mysqli = new mysqli('localhost','root','root','test');
	if($mysqli->connect_errno) {
		exit('CONNECT ERROR:'.$mysqli->connect_error);
	} 

	$mysqli->set_charset('utf8');

	$sql = "INSERT INTO user(username,password,age) VALUES('yangzichao','yangzichao',20)";
	$sql .= "UPDATE user SET age = 5 WHERE id = 28";
	$sql .= "DELETE FROM user WHERE id = 25";
	$res = $mysqli->multi_query($sql);
	if($res) {
		echo $res->affected_rows.'条记录被删除';
	}	else {
		echo 'Error '.$mysqli->errno.':'.$mysqli->error;
	} 

	$mysqli->close();
?>

<?php
	header('content-type:text/html;charset=UTF-8');
	
	$mysqli = new mysqli('localhost','root','root','test');
	if($mysqli->connect_errno) {
		exit('CONNECT ERROR:'.$mysqli->connect_error);
	}

	$mysqli->set_charset("utf8");
	
	$sql = "select id,username,age from user;";
	$sql .= "select * from mysql.user";
	//use_result()/store_result():获取第一条查询产生的结果集
	//more_results():检测是否有更多的结果集
	//next_result():将结果集指针向下移动一位
	if($mysqli->multi_query($sql)) {
		do {
			if($mysqil_result = $mysqli->store_result()) {
				$rows[] = $mysqli_result->fetch_all(MYSQLI_ASSOC);
			}
		} while($mysqli->more_results() && $mysqli->next_result());
	} else {	
		echo '查询失败：'.$mysqli->error;
	}

	var_dump($rows);
	$mysqli->close();
?>

---

使用预处理语句执行插入操作

<?php
	header('content-type:text/html;charset=utf-8');
	$mysqli = @new mysqli('localhost','root','root','test');
	if($mysqli->connect_errno) {
		exit('CONNECT ERROR;'.$mysqli->connect_error);
	}

	$mysqli->set_charset('utf8');

	$sql = "insert user(username,password,age) values (?,?,?);";
	//准备预处理语句
	$mysqli_stmt = $mysqli->prepare($sql);

	$username = 'king';
	$password = mad5('king');
	$age = 12;
	//绑定参数
	$mysqli_stmt->bind_param('ssi',$username,$password,$age);

	//执行预处理语句
	if($mysqli_stmt->execute()) {
		echo $mysqli_stmt->insert_id;
		echo '<br />';
	} else {
		echo $mysqli_stmt->error;
	}
?>

---

使用预处理语句防止sql注入

<?php
	header('content-type:text/html;charset=utf-8');

	$mysqli = new mysqli('localhost','root','root','test');
	if($mysqli->connect_errno) {
		echo 'CONNECT ERROR:'.$mysqli->connect_error;
	}

	$mysqli->set_charset('utf8');

	$sql = "select * from user where username = ? and password = ?;";
	$mysqli_stmt = $msyqli->prepare($sql);

	$username = $_POST['username'];
	$password = md5($_POST['password']);
	$mysqli_stmt->bind_param('ss',$username,$password);

	if($mysqli_stmt->execute()) {
		$mysqli_stmt->store_result();
		if($mysqli_stmt->num_rows > 0) {
			echo '登陆成功!';
		} else {
			echo '登陆失败!';
		}
	}
?>

---

使用预处理语句执行查询操作

<?php
	header('content-type:text/html;charset=utf-8');
	
	$mysqli = new mysqli('localhost','root','root','test');
	if($mysqli->connect_errno) {
		exit('CONNECT ERROR:'.$mysqli->connect_error);
	}
	
	$mysqli->set_charset('utf8');
	
	$sql = "select id,username,age from user where id>=?";
	$mysqli_stmt = $mysqli->prepare($sql);

	$id = 20;
	$mysqli_stmt->bind_param('i',$id);

	if($mysqli_stmt->execute()) {
		//bind_result():绑定结果集中的值到变量
		$mysqli_stmt->bind_result($id,$username,$age);
		//遍历结果集
		while($mysqli_stmt->fetch()) {
			echo '编号：'.$id.'<br />';
			echo '用户名：'.$username.'<br />';
			echo '年龄：'.$age.'<br />';
			echo '<hr />';
		}
	} else {
		echo '查询错误:'.$mysqli_stmt->error;
	}
	$mysqli_stmt->free();
	$mysqli->close();
?>