如何在表示另一个类别的表中显示来自一个类别(tbl_category)的项目标题

Question

我有两个表tbl_category和tbl_food。

我通过将category_id添加到tbl_food来对tbl_food到tbl_category的项目进行排序，其中tbl_food中的category_id与tbl_category中的id相同。

但是，如何从tbl_category获取标题并将其显示在表示tbl_food项目的表中？

<?php
//query to get all admin from dtb 
$sql = "SELECT * FROM tbl_food";
//Exectue the query
$res = mysqli_query($conn, $sql);
//check if executed
if($res==TRUE)
{
//count rows to check wether we have data
$count = mysqli_num_rows($res); //get all rows in dtb
$sn=1; //create a variable and assign the value
//check numb of rows
if($count>0)
{
//there is data
while($rows=mysqli_fetch_assoc($res))
{
//using while loop to get data from dtb
//get individual data
$id=$rows['id'];
$title=$rows['title'];
$description=$rows['description'];
$price=$rows['price'];
$active=$rows['active'];
$category_id=$rows['category_id'];

//display values in table
?>
<tr>
<td><?php echo $sn++; ?></td>
<td><?php echo $title; ?></td>
<td><?php echo $description; ?></td>
<td><?php echo $price; ?> kn</td>
<td><?php echo $active; ?></td>
<td><?php echo $category_id; ?></td> 
</tr>
<?php

}}
else{
?>
<tr colspan="4">
<td class="error">No categories added</td>
</tr>
<?php  
}
}
?>

Answer 1

我认为您需要编辑您的查询，首先添加JOIN以通过category_id将tbl_category连接到tbl_food，如下所示：

$sql = "SELECT food.*, category.title FROM tbl_food AS food INNER JOIN tbl_category AS category ON category.id=food.category_id";

其次，您在迭代查询结果时似乎有错误。mysqli_fetch_assoc($res)返回一个关联的数组，所以它必须是这样的：

$rows = mysqli_fetch_assoc($res)
foreach($rows as $row){
$id=$row['id'];
$title=$row['title'];
$description=$row['description'];
$price=$row['price'];
$active=$row['active'];
$category_id=$row['category_id'];
//Doing stuff
}

Answer 2

您必须从tbl_category中获取标题的方法1-JOIN方法：上面已经提到了2-条件方法：

$sql = "SELECT tbl_food.*, tbl_category.title FROM tbl_food,tbl_category WHERE tbl_category.id=tbl_food.category_id";
foreach($sql as $row){
$id=$row['id'];
$title=$row['title'];
$description=$row['description'];
$price=$row['price'];
$active=$row['active'];
$category_id=$row['category_id'];
}