php如何一次查询分类显示,PHP实战-列表页遍历中分类名称查询\联表查询，foreach遍历中显示分类名称...

列表页遍历中分类名称查询\联表查询，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 "

";

echo "

{$row['id']}";

echo "

{$row['name']}";

echo "

{$new_cityName_value[$row['city_id']]}"; //重点在这，与上面的城市查询对应

echo "

{$new_areaName_value[$row['area_id']]}"; //重点在这，

echo "

{$row['tel']}";

echo "

{$row['address']}";

echo "

{$row['create_time']}";

echo "

编辑

删除

";

echo "

";

}

3、效果展示

image.png

4、完整页面代码

content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">

项目管理

function doDel(id) {

if(confirm('确认删除？')){

window.location='action.php?action=delProject&id='+id;

}

}

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;

}

项目列表

ID	项目名称	城市	区域	电话	地址	创建时间	操作

$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 "

";

echo "

{$row['id']}";

echo "

{$row['name']}";

echo "

{$new_cityName_value[$row['city_id']]}";

echo "

{$new_areaName_value[$row['area_id']]}";

echo "

{$row['tel']}";

echo "

{$row['address']}";

echo "

{$row['create_time']}";

echo "

编辑

删除

";

echo "

";

}

//计算出总页数= 总记录数 / 每天显示的数量

$total = ceil($count / $num); //ceil函数大于当前数就显示整数

if ($page >= $total){

$page = $page-1;//如果到了尾页，就不再往后跳转了

}

echo '

';

echo '上一页';

for($p=1; $p <= $total; $p++){

echo ''.$p.'';

}

echo '下一页';

?>