创建测试数据: 首先我们需要创建一些测试记录,然后先来演示一下数据库的基本的链接命令的使用.
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'] . " ";
echo $row['name'] . " ";
echo $row['sex'] . " ";
echo $row['age'] . " ";
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'] . " ";
echo $row['name'] . " ";
echo $row['sex'] . " ";
echo $row['age'] . " ";
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>