• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号
登陆 注册

PHP操作MySQL数据库与PDO技术

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

创建测试数据: 首先我们需要创建一些测试记录,然后先来演示一下数据库的基本的链接命令的使用.

create table username
( uid int not null,name varchar(50),
   sex varchar(10),age int
);

insert into username(uid,name,sex,age) values(1,"李四","男",25);
insert into username(uid,name,sex,age) values(2,"张三","男",33);
insert into username(uid,name,sex,age) values(3,"王五","女",56);
insert into username(uid,name,sex,age) values(4,"王二麻子","男",76);
insert into username(uid,name,sex,age) values(5,"六头","男",34);
insert into username(uid,name,sex,age) values(6,"孙琪","女",25);
insert into username(uid,name,sex,age) values(7,"流云","男",63);

<?php
	$mysqli = new mysqli("localhost","root","123456","mysql");
	if(mysqli_connect_errno())
	{
		printf("连接失败: %s <br>",mysqli_connect_error());
	}

	printf("当前数据库字符集: %s <br>",$mysqli->character_set_name());
	printf("客户端版本: %s <br>",$mysqli->get_client_info());
	printf("主机信息: %s <br>",$mysqli->host_info);
	printf("服务器版本: %s <br>",$mysqli->server_info);
	printf("服务器版本: %s <br>",$mysqli->server_version);

	if($mysqli->query("select * from lyshark.username;"))
	{
		echo "当前记录条数: {$mysqli->affected_rows} 条 <br>";
		echo "新插入的ID值: {$mysqli->insert_id} 条 <br>";
	}
	$mysqli->close();
?>

逐条读取数据: 通过循环的方式逐条读取数据,并将数据根据HTML格式输出到屏幕,注意用完后释放,否则会非常占用内存.

<?php
	$mysqli = new mysqli("localhost","root","123456","lyshark");
	if(mysqli_connect_errno())
		printf("连接失败: %s <br>",mysqli_connect_error());

	if(!$mysqli->query("set names utf8;"))
		printf("切换字符集失败 <br>");

	// 第一种查询方式: 逐行遍历结果集
	$result = $mysqli->query("select uid,name from lyshark.username;");
	while(list($uid,$name) = $result->fetch_row())
	{
		echo "UID: {$uid} --> Name: {$name} <br>";
	}
	$result->close();

	// 第二种遍历方式: 遍历时直接输出到外部表格上
	$result = $mysqli->query("select * from lyshark.username;");
	echo "<table width='90%' border='1' align='center'>";
	echo "<th>用户ID</th><th>姓名</th><th>性别</th><th>年龄</th>";
	while($row=$result->fetch_assoc())
	{
		echo "<tr align='center'>";
		echo "<td> {$row['uid']}</td>";
		echo "<td> {$row['name']}</td>";
		echo "<td> {$row['sex']}</td>";
		echo "<td> {$row['age']}</td>";
		echo "<tr>";
	}
	echo "</table>";
	$result->close();

	//第三种方式,直接输出关联数组
	$result = $mysqli->query("select * from lyshark.username;");
	while($row=$result->fetch_array(MYSQLI_ASSOC))
	{
		echo "UID: {$row['uid']} 姓名: {$row['name']} <br>";
	}
	$result->close();
	$mysqli->close();
?>

通过对象返回结果集: 该方法与前面三个不同,他将以一个对象的形式返回一条结果记录,而不是数组,它的每个字段都需要以对象的方式进行访问,数据列的名称区分字母大小写.

<?php
	$mysqli = new mysqli("localhost","root","123456","lyshark");
	if(mysqli_connect_errno())
		printf("连接失败: %s <br>",mysqli_connect_error());

	if(!$mysqli->query("set names utf8;"))
		printf("切换字符集失败 <br>");

	$result = $mysqli->query("select * from lyshark.username;");
	echo "<table width='90%' border='1' align='center'>";
	echo "<th>用户ID</th><th>姓名</th><th>性别</th><th>年龄</th>";
	while($rowObj=$result->fetch_object())
	{
		echo "<tr align='center'>";
		echo "<td> {$rowObj->uid}</td>";
		echo "<td> {$rowObj->name}</td>";
		echo "<td> {$rowObj->sex}</td>";
		echo "<td> {$rowObj->age}</td>";
		echo "<tr>";
	}
	echo "</table>";
	$result->close();
	$mysqli->close();
?>

参数绑定执行: 参数绑定执行其实使用的就是预处理技术,即预先定义SQL语句模板,然后后期使用变量对模板进行填充,然后在带入数据库执行,这里其实可以在带入模板时对数据进行合法验证,保证不会出现SQL注入的现象.

<?php
	$mysqli = new mysqli("localhost","root","123456","lyshark");
	if(mysqli_connect_errno())
		printf("连接失败: %s <br>",mysqli_connect_error());

	if(!$mysqli->query("set names utf8;"))
		printf("切换字符集失败 <br>");

	// 声明一个insert语句,并使用mysqli->prepare($query)对该SQL进行预处理
	$query = "insert into username(uid,name,sex,age) values(?,?,?,?);";
	$stmt = $mysqli->prepare($query);

	// 使用占位符绑定变量: i=>整数 d=>浮点数 s=>字符串 b=>二进制
	// issi => 代表 => 整数 字符串 字符串 整数
	$stmt->bind_param("issi",$u_id,$u_name,$u_sex,$u_age);

	// 填充预处理变量
	$u_id = 8;
	$u_name = "lyshark";
	$u_sex = "男";
	$u_age = 25;
	$stmt->execute();       // 执行插入操作
	echo "插入的行数: {$stmt->affected_rows} <br>";
	echo "自动增长ID: {$mysqli->insert_id} <br>";

	// 继续填充插入新的变量
	$u_id = 10;
	$u_name = "super_user";
	$u_sex = "男";
	$u_age = 300;
	$stmt->execute();       // 执行插入操作
	echo "插入的行数: {$stmt->affected_rows} <br>";
	echo "自动增长ID: {$mysqli->insert_id} <br>";

	$stmt->close();
	$mysqli->close();
?>

预处理语句查询: 使用预处理执行SQL时,拿到的执行结果并不是一个数组,我们需要自己将这些结果集绑定到指定的变量上,然后再通过遍历变量的方式获取到结果集中的所有数据.

<?php
	$mysqli = new mysqli("localhost","root","123456","lyshark");
	if(mysqli_connect_errno())
		printf("连接失败: %s <br>",mysqli_connect_error());

	if(!$mysqli->query("set names utf8;"))
		printf("切换字符集失败 <br>");


	$query = "select uid,name,sex,age from lyshark.username;";
	if($res = $mysqli->prepare($query))
	{
		$res->execute();             // 执行SQL语句
		$res->store_result();        // 取回所有的查询结果
		echo "记录个数: {$res->num_rows} 行 <br>";

		// 绑定返回结果到指定变量上
		$res->bind_result($u_id,$u_name,$u_sex,$u_age);
		while($res->fetch())
		{
			printf("%d --> %s --> %s --> %d <br>",$u_id,$u_name,$u_sex,$u_age);
		}
	}
	$res->close();
	$mysqli->close();
?>

如果在SELECT查询语句上也使用占位符去查询,并需要多次执行这一条语句时,也可以将mysqli_stmt对象中的bind_param()和bind_result()方法结合起来.

<?php
	$mysqli = new mysqli("localhost","root","123456","lyshark");
	if(mysqli_connect_errno())
		printf("连接失败: %s <br>",mysqli_connect_error());

	if(!$mysqli->query("set names utf8;"))
		printf("切换字符集失败 <br>");

	// 此处我们使用一个占位符uid=?
	$query = "select uid,name,sex,age from lyshark.username where uid=?;";
	if($res = $mysqli->prepare($query)) // 预处理语句
	{
		$u_id = 1;
		$res->bind_param("d",$u_id);    // 绑定参数,绑定到UID上
		$res->execute();                // 执行
		$res->store_result();           // 取回所有的查询结果
		echo "记录个数: {$res->num_rows} 行 <br>";

		// 绑定返回结果到指定变量上
		$res->bind_result($u_id,$u_name,$u_sex,$u_age);
		while($res->fetch())
		{
			printf("%d --> %s --> %s --> %d <br>",$u_id,$u_name,$u_sex,$u_age);
		}
	}
	$res->close();
	$mysqli->close();
?>

开启事务提交: 在使用事务提交时需要让MySQL数据库切换到InnoDB上,然后执行事务,最后提交.

<?php
	$mysqli = new mysqli("localhost","root","123456","lyshark");
	if(mysqli_connect_errno())
		printf("连接失败: %s <br>",mysqli_connect_error());

	if(!$mysqli->query("set names utf8;"))
		printf("切换字符集失败 <br>");

	$success = TRUE;
	$age = 30;

	$mysqli->autocommit(0);        // 暂时关闭事务提交
	$result = $mysqli->query("select * from lyshark.username;");

	// 如果SQL执行失败,则将状态设置为假
	if(!$result or $mysqli->affected_rows != 1)
	{
		$success=FALSE;
	}

	// 最后判断是否成功,成功则提交事务
	if($success)
	{
		$mysqli->commit();
		echo "事务已提交 <br>";
	}
	else
	{
		$mysqli->rollback();
		echo "事务执行失败,回滚到初始状态<br>";
	}

	$mysqli->autocommit(1);     // 开启事务
	$result->close();
	$mysqli->close();
?>

PDO 连接MySQL数据库: PDO技术就是在SQL语句中添加了一个中间层,所有的查询方式都可以通过中间层去调用,极大的提高了数据库操作的通用性,同时安全性也得到了更好的保障,以下是基本的语句使用:

<?php

	// 设置持久连接的选项数组作为最后一个参数
	$opt = array(PDO::ATTR_PERSISTENT => TRUE);
	try
	{
		$dbh = new PDO("mysql:dbname=lyshark;host=localhost","root","123456",$opt);
	}catch(PDOException $e)
	{
		echo "数据库连接失败: {$e->getMessage()} <br>";
		exit;
	}

	// 调用getAttribute()可以获得所有属性名称对应的值.
	echo "是否关闭自动提交: " . $dbh->getAttribute(PDO::ATTR_AUTOCOMMIT) . "<br>";
	echo "PDO错误处理模式: " . $dbh->getAttribute(PDO::ATTR_ERRMODE) . "<br>";
	echo "表字段字符的大小写转换: " . $dbh->getAttribute(PDO::ATTR_CASE) . "<br>";
	echo "连接状态相关的信息: " . $dbh->getAttribute(PDO::ATTR_CONNECTION_STATUS) . "<br>";
	echo "空字符串转换SQL的NULL: " . $dbh->getAttribute(PDO::ATTR_ORACLE_NULLS) . "<br>";
	echo "应用程序提前获取数据大小: " . $dbh->getAttribute(PDO::ATTR_PERSISTENT) . "<br>";

	// 设置一个标志
	$dbh->setAttribute(PDO::ATTR_ERRMODE);
?>

PDO 获取表中数据: 当执行查询语句时我们可以使用PDO中的Query()方法,该方法执行后返回受影响的行总数,也可以使用Fetch等语句,下面是三者的查询方式.

<?php

	// 设置持久连接的选项数组作为最后一个参数
	$opt = array(PDO::ATTR_PERSISTENT => TRUE);
	try
	{
		$dbh = new PDO("mysql:dbname=lyshark;host=localhost","root","123456",$opt);
		// 设置捕获异常
		$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
	}catch(PDOException $e)
	{
		echo "数据库连接失败: {$e->getMessage()} <br>";
		exit;
	}
// -------------------------------------------------
	// 使用 query() 完成数据查询
	$query = "select uid,name,sex,age from username";
	try
	{
		$pdo_proc = $dbh->query($query);
		echo "总共查询到: {$pdo_proc->rowCount()} 条记录 <br>";
		foreach($pdo_proc as $row)
		{
			echo $row['uid'] . "\t";
			echo $row['name'] . "\t";
			echo $row['sex'] . "\t";
			echo $row['age'] . "\t";
			echo "<br>";
		}
	}catch(PDOException $e)
	{
		// 两种方式都可以完成异常捕获
		echo $e->getMessage();
		print_r($dbh->errorInfo());
	}

// -------------------------------------------------
	// 使用 fetch() 方法完成遍历
	$stmt = $dbh->query("select uid,name,sex,age from username");
	while($row = $stmt->fetch(PDO::FETCH_ASSOC))
	{
			echo $row['uid'] . "\t";
			echo $row['name'] . "\t";
			echo $row['sex'] . "\t";
			echo $row['age'] . "\t";
			echo "<br>";
	}

// -------------------------------------------------
	// 使用 fetch_all() 方法完成遍历
	$stmt = $dbh->prepare("select uid,name,sex,age from username;");
	$stmt->execute();

	$allRow = $stmt->fetchAll(PDO::FETCH_NUM);
	foreach ($allRow as $row)
	{
		echo "{$row[0]} <br>";
	}
?>

PDO 参数绑定后执行: 参数绑定执行,在上面的内容中已经尝试过了,这里其实就是使用的引擎变成了PDO引擎,根本的东西还是老样子.

<?php

	// 设置持久连接的选项数组作为最后一个参数
	$opt = array(PDO::ATTR_PERSISTENT => TRUE);
	try
	{
		$dbh = new PDO("mysql:dbname=lyshark;host=localhost","root","123456",$opt);
		// 设置捕获异常
		$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
	}catch(PDOException $e)
	{
		echo "数据库连接失败: {$e->getMessage()} <br>";
		exit;
	}

	// 直接绑定后插入数据
	$query = "insert into username(uid,name,sex,age) values(?,?,?,?);";
	$stmt = $dbh->prepare($query);    // 预处理

	// 填充数据
	$u_id = 100;
	$u_name = "lyshark";
	$u_sex = "男";
	$u_age = 25;
	// 绑定参数,分别绑定1,2,3,4个位置的?号,到每个变量上
	$stmt->bindParam(1,$u_id);
	$stmt->bindParam(2,$u_name);
	$stmt->bindParam(3,$u_sex);
	$stmt->bindParam(4,$u_age);
	$stmt->execute();            // 执行提交

	// -------------------------------------------------
	// 第二种绑定参数的方式
	$query = "insert into username(uid,name,sex,age) values(:u_id,:u_name,:u_sex,:u_age);";
	$stmt = $dbh->prepare($query);
	$stmt->execute(array(":u_id" => 200,":u_name"=> "三从",":u_sex" => "女",":u_age"=>25));
?>

PDO 绑定参数实现查询: 前面的查询是直接写死的SQL语句实现的查询,这里我们需要通过PDO将其参数绑定,动态的传入数据让其进行查询,该方法可以将一个列和一个指定的变量名绑定在一起.

<?php

	// 设置持久连接的选项数组作为最后一个参数
	$opt = array(PDO::ATTR_PERSISTENT => TRUE);
	try
	{
		$dbh = new PDO("mysql:dbname=lyshark;host=localhost","root","123456",$opt);
		// 设置捕获异常
		$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
	}catch(PDOException $e)
	{
		echo "数据库连接失败: {$e->getMessage()} <br>";
		exit;
	}
	$query = "select uid,name,sex,age from username;";
	try
	{
		$stmt = $dbh->prepare($query);
		$stmt->execute();

		$stmt->bindColumn(1,$u_id);   // 通过序号绑定
		$stmt->bindColumn(2,$u_name); // 第二个参数绑定到u_name

		$stmt->bindColumn('sex',$u_sex);  // 将sex绑定到u_sex
		$stmt->bindColumn('age',$u_age);

		while($row = $stmt->fetch(PDO::FETCH_BOUND))
		{
			echo "ID: {$u_id} --> Name: {$u_name} <br>";
		}

	}catch(PDOException $e)
	{
		echo $e->getMessage();
	}
?>

PDO 开启事务支持: PDO技术同样支持十五处理,事务用于保证,数据的原子性,一致性,独立性,持久性,也就是ACID模型.

<?php

	// 设置持久连接的选项数组作为最后一个参数
	$opt = array(PDO::ATTR_PERSISTENT => TRUE);
	try
	{
		$dbh = new PDO("mysql:dbname=lyshark;host=localhost","root","123456",$opt);
		// 设置捕获异常
		$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
	}catch(PDOException $e)
	{
		echo "数据库连接失败: {$e->getMessage()} <br>";
		exit;
	}

	try
	{
		$dbh->beginTransaction();     // 启动一个事务
		$dbh->exec("select * from username;");
		$dbh->commit();               // 提交事务
	}catch(Exception $e)
	{
		$dbh->rollBack();
		echo "事务失败,自动回滚: " . $e->getMessage() . "<br>";
	}
?>

Smarty模板

基础知识,Smarty

// Get: <!-- http://127.0.0.1/controller.php?username=lyshark&password=123123 -->
<html>
	<body>
		姓名: {$name}<br>
		年龄: {$age}<br>
		婚否: {$isMarried}<br>
		手机: {$contact[0]}<br>
		手机: {$contact.1}<br>

		用户名: {$smarty.get.username} <br>
		密码: {$smarty.get.password} <br>
		域名: {$smarty.server.SERVER_NAME} <br>
		客户IP: {$smarty.server.REMOTE_ADDR} <br>
		获取常量: {$smarty.const.PHP_INT_MAX} <br>
		第二种常量: {$smarty.const.DB_USER} <br>

		当前时间戳: {time()} <br>
		当前时间戳: {$smarty.now} <br>
		格式化输出: {$smarty.now|date_format:'%Y-%m-%d %H:%M:%S'} <br>
	</body>
</html>

<?php
	header("content-type:text/html;charset=utf-8");
	require_once("./smarty/libs/Smarty.class.php");

	// 新建smarty类
	$smarty = new Smarty();
	$smarty->setTemplateDir("./");         // 设置默认文件目录
	//print_r($smarty->getTemplateDir());    // 设置模板路径

	// 绑定标签
	$smarty->assign("name","lyshark");
	$smarty->assign("age",25);
	$smarty->assign("isMarried",true);
	$smarty->assign("contact",array("123456","6789"));

	// 自定义常量
	const DB_HOST = "localhost";
	define("DB_USER","root");

	// 输出页面
	$smarty->display("./view.html");
?>

<html>
	<body>
		{foreach $array as $key=>$value}
			$array[{$key}] = {$value} <br>

		{/foreach}
	</body>
</html>

<?php
	header("content-type:text/html;charset=utf-8");
	require_once("./smarty/libs/Smarty.class.php");
	$smarty = new Smarty();
	$smarty->setTemplateDir("./");



	$array = array(
		'db_host' => 'localhost',
		'db_user' => 'root',
		'db_pass' => 'root'
	);
	$smarty->assign("array",$array);
	$smarty->display("./view.html");
?>

foreach

<?php
	header("content-type:text/html;charset=utf-8");
	require_once("./smarty/libs/Smarty.class.php");

	$smarty = new Smarty();
	$smarty->setTemplateDir("./");

	$array_two = array(
		array(1001,"张某","男"),
		array(1002,"李某","女")
	);

	$smarty->assign("array_two",$array_two);
	$smarty->display("./view.html");
?>

<html>
	<body>
		<table width='500' border='1'>
			{foreach $array_two as $array}
			<tr>
				{foreach $array as $value}
					<td>{$value}</td>
				{/foreach}
			</tr>
			{/foreach}
		</table>
	</body>
</html>

<html>
	<body>
		<table width='500' border='1'>
			{foreach $array_two as $array}
			<tr>
				{foreach $array as $value}
					<td>{$value@index} --> {$value} --> {$value@key} --> {$value@value}</td>
				{/foreach}
			</tr>
			{/foreach}
		</table>
	</body>
</html>

section

<?php
	header("content-type:text/html;charset=utf-8");
	require_once("./smarty/libs/Smarty.class.php");

	$smarty = new Smarty();
	$smarty->setTemplateDir("./");

	$array = array(
		array(1,'张三',22),
		array(2,'李四',25),
		array(3,'王五',30)
	);
	$smarty->assign("array",$array);
	$smarty->display("./view.html");
?>

<html>
	<body>
		<table width='400' border='1'>
			{section name=x loop=$array}
				<tr>
					{section name=y loop=$array[x]}
						<td>{$array[x][y]}</td>
					{/section}
				</tr>
			{/section}
		</table>
	</body>
</html>

<html>
	<body>
		<!-- 从下标2开始,每次递增2-->
		{section name=x loop=$array start=2 step=2}
			{$array[x]}
		{/section}
	</body>
</html>

鲜花
握手
雷人
路过
鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
More+
上一篇：
php微信开发自动回复一直提示“该公众号提供的服务出现故障，请稍后再试” ...发布时间：2022-07-10
下一篇：
转 安装PHP出现make: *** [sapi/cli/php] Error 1 解决办法发布时间：2022-07-10
热门推荐
More+
热门话题
More+
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服（服务时间 9:00～18:00）

在线QQ客服
地址：深圳市南山区西丽大学城创智工业园
电邮：jeky_zhao#qq.com
移动电话：139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap