php - 我想一个一个地显示餐厅名称及其各自的食品名称

Question

我有两个表“restaurants”，其中包含列(id、Rname、email)和“food_items”，其中包含列(id、item_name、item_price、Rname)。现在我想开发一个页面，其中将显示所有餐厅及其各自的食品。如下所示:

Restaurant Name1:
item 1
item 2
item 3

我为此尝试了很多，但没有得到我想要的东西。我编写了一个 SQL 查询，其中显示了餐厅名称，然后显示了它们的项目名称，但它没有给我确切的信息。

<?php

$sql1 = "SELECT DISTINCT mi.item_name, mi.item_price, mi.veg_nonveg, rs.Rname FROM menu_items AS mi LEFT JOIN restaurants AS rs ON mi.Rname=rs.Rname";

    $result1 = mysqli_query($conn,$sql1);
    if(mysqli_num_rows($result1)){
    foreach ($result1 as $rows) {
    echo "<br>" . $rows['item_name'] . " " . $rows['item_price'] . " " . 
    $rows['Rname'];
    }
    }

实际结果是:

item_name, item_price , Restaurant name (Rname)

chicken curry 105 abc
veg fried rice 101 abc
Veg Momos 50 abc
veg fried rice 100 xyz

预期结果:

ABC:
Chicken curry 105
veg fried rice 101
veg momos 50

xyz:
veg fried rice 100

Answer 1

您可以通过 php 实现相同的目的，而不是从 sql 中执行此操作:

例如，如果您从数据库收到的数据是这样的:

$data = [
    ['Rname' => 'Restaurant 1', 'item_name' => 'foo', 'item_price' => '354631', 'veg_nonveg' => 'veg'],
    ['Rname' => 'Restaurant 1', 'item_name' => 'bar', 'item_price' => '234631', 'veg_nonveg' => 'veg'],
    ['Rname' => 'Restaurant 2', 'item_name' => 'foo', 'item_price' => '334234', 'veg_nonveg' => 'non-veg'],
    ['Rname' => 'Restaurant 2', 'item_name' => 'bar', 'item_price' => '45353', 'veg_nonveg' => 'non-veg'],
];

你可以这样做:

// To print restaurant name only once in a group we need to store the 
// already printed restaurant somewhere.

$restaurant = ''; // declared this variable to store current restaurant.

foreach ($data as $row) {

    // checking if current restaurant is already printed. 
    if ($restaurant != $row['Rname']) {
        //if current restaurant is not printed then we will print new one and store new restaurant.
        $restaurant = $row['Rname'];
        echo $row['Rname'] . "\r\n";
    }

    // here we are doing the regular stuff.
    echo $row['item_name']. ' ' .$row['item_price']. ' ' . $row['veg_nonveg']. "\r\n";
}

在此处查看工作示例:working example

编辑:

另一种方式:

例如我们有两个表:

作者:

+----+------+-------------+
| id | name | description |
+----+------+-------------+
|  1 | abc  | som         |
|  2 | def  | description |
|  3 | ghi  | here        |
+----+------+-------------+

书:

+----+------+------+-----------+
| id | name | year | author_id |
+----+------+------+-----------+
|  1 | ijk  | 1990 |         1 |
|  2 | lmn  | 1991 |         1 |
|  3 | opq  | 1995 |         2 |
|  4 | rst  | 1996 |         2 |
|  5 | uvw  | 2000 |         3 |
|  6 | xyz  | 2001 |         3 |
+----+------+------+-----------+

获取数据的最简单方法是运行两个 sql 查询:

首先获取作者:

SELECT * FROM author;

然后通过作者 ID 获取他们的书籍:

SELECT * FROM book where author_id IN (1,2,3);

现在要按组打印作者的书籍，我们可以按 author_id 打印书籍:

foreach ($authors as $author) {
    echo $author['name'] . "\r\n";

    foreach ($books as $book) {
        if ($book['id'] == $author['author_id']) {
            echo $book['name']. ' ' .$book['year']. "\r\n";
        }
    }
}

虽然我们在这里运行两个查询，但这是管理数据的更简洁的方式。它还使实现分页时的事情变得更容易。