PHP实战-列表页遍历中分类名称查询\联表查询，foreach遍
2019-05-28 本文已影响0人
挤时间学习的阿龙
列表页遍历中分类名称查询\联表查询，foreach遍历中显示分类名称，分类（区域、城市）id关联查询名称
1、建立关联查询
foreach循环遍历查询城市名称，然后存放到1个数据里。
//城市名称关联查询
$sql = "select * from city ";
$result = mysqli_query($conn, $sql);
$new_cityName = mysqli_fetch_all($result, MYSQL_ASSOC);
$new_cityName_value = array();
foreach($new_cityName as $row ){
$new_cityName_value[$row['id']] = $row['name'];
}
2、在列表页循环中插入对应关系
# 3.循环解析数据
foreach ($conn->query($sql) as $row){
echo "<tr>";
echo "<td>{$row['id']}</td>";
echo "<td>{$row['name']}</td>";
echo "<td>{$new_cityName_value[$row['city_id']]}</td>"; //重点在这，与上面的城市查询对应
echo "<td>{$new_areaName_value[$row['area_id']]}</td>"; //重点在这，
echo "<td>{$row['tel']}</td>";
echo "<td>{$row['address']}</td>";
echo "<td>{$row['create_time']}</td>";
echo "<td>
<a href='editProject.php?id={$row['id']}'>编辑</a>
<a href='javascript:void(0);' onclick='doDel({$row['id']})'>删除</a></td>";
echo "</tr>";
}
3、效果展示
image.png
4、完整页面代码
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport"
content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>项目管理</title>
<link href="css/css.css" rel="stylesheet" type="text/css">
<script>
function doDel(id) {
if(confirm('确认删除？')){
window.location='action.php?action=delProject&id='+id;
}
}
</script>
<style type="text/css">
table.gridtable {
font-family: verdana,arial,sans-serif;
font-size:13px;
color:#333333;
border-width: 1px;
border-color: #ccc;
border-collapse: collapse;
}
table.gridtable th {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #ccc;
background-color: #FF704D;
color: white;
font-size: 15px;
}
table.gridtable td {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #ccc;
background-color: #ffffff;
text-align: center;
}
h3{
color: #FF704D;
}
</style>
</head>
<body>
<?php include ("menu.php"); ?>
<h3>项目列表</h3>
<table class="gridtable" width="90%" border="1" cellspacing="0" style="color:#3C3C3C;font-family: 微软雅黑;" >
<tr>
<th>ID</th>
<th width='18%'>项目名称</th>
<th>城市</th>
<th>区域</th>
<th width='18%'>电话</th>
<th width='18%'>地址</th>
<th width='15%'>创建时间</th>
<th width="12%">操作</th>
</tr>
<?php
$num = 5; //每页显示的数量
$page = isset($_GET['p']) ? $_GET['p'] : 0; //$page如果有get传值就获取，没有就默认第一页
//页码小于等于1，就不变化
if ($page <=1){
$page = $page+1;
}
# 计算偏移量
$offset = ($page-1)*$num;
# 1.连接数据库
include ('inc/conn.php');
//城市名称关联查询
$sql = "select * from city ";
$result = mysqli_query($conn, $sql);
$new_cityName = mysqli_fetch_all($result, MYSQL_ASSOC);
$new_cityName_value = array();
foreach($new_cityName as $row ){
$new_cityName_value[$row['id']] = $row['name'];
}
//区域名称关联查询
$sql = "select * from area ";
$result2 = mysqli_query($conn, $sql);
$new_areaName = mysqli_fetch_all($result2, MYSQL_ASSOC);
$new_areaName_value = array();
foreach($new_areaName as $row ){
$new_areaName_value[$row['id']] = $row['name'];
}
# 2.查询数据库
$sql = "SELECT * FROM `project` LIMIT {$offset}, {$num}";
if($tempRes=mysqli_query($conn,"SELECT count(*) as count FROM `project`")){
$row=mysqli_fetch_assoc($tempRes);
$count = $row['count']; //记录总数
}
# 3.循环解析数据
foreach ($conn->query($sql) as $row){
echo "<tr>";
echo "<td>{$row['id']}</td>";
echo "<td>{$row['name']}</td>";
echo "<td>{$new_cityName_value[$row['city_id']]}</td>";
echo "<td>{$new_areaName_value[$row['area_id']]}</td>";
echo "<td>{$row['tel']}</td>";
echo "<td>{$row['address']}</td>";
echo "<td>{$row['create_time']}</td>";
echo "<td>
<a href='editProject.php?id={$row['id']}'>编辑</a>
<a href='javascript:void(0);' onclick='doDel({$row['id']})'>删除</a></td>";
echo "</tr>";
}
//计算出总页数= 总记录数 / 每天显示的数量
$total = ceil($count / $num); //ceil函数大于当前数就显示整数
if ($page >= $total){
$page = $page-1;//如果到了尾页，就不再往后跳转了
}
echo '<p align="center">';
echo '<a href="?p='.($page-1).'">上一页</a>';
for($p=1; $p <= $total; $p++){
echo '<a href="?p='.$p.'">'.$p.'</a>';
}
echo '<a href="?p='.($page+1).'">下一页</a>';
?>
</table>
</body>
</html>
