显示基于外键的数据

hgc7kmma 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(278)

你刚才发送的代码在我的系统上运行得非常好，所以我在另一个模块中也使用了它，管理员可以在其中查看日期。但我遇到了问题。
在这种情况下，第一个用户谁添加了他/她的订单第一，甚至下一个用户谁有相同的名称和节现在被记录和保存正确。然而，问题是当记录完成时，它会显示所记录的第一阶的重复值。

DATE   |   NAME   |   SECTION   |   PAYABLE   |   PRODUCT   |   QUANTITY
10/04/18 |   User1  |  Section 1  |     990     |  Magic Mug  |      3
                                                |   T-shirt   |      3
10/04/18 |   User1  |  Section 1  |     630     |  Magic Mug  |      3
                                                |   Thumbler  |      2
10/04/18 |   User1  |  Section 1  |     990     |  Magic Mug  |      3
                                                |   T-shirt   |      3

这是我使用的php代码，我只在代码中添加了日期。

$last_date = NULL;
$last_name = NULL;
$last_section = NULL;
$last_payable = NULL;

while ($row = mysql_fetch_array($result)) {
    $date = "";
    $name = "";
    $section = "";
    $payable = "";
    if ($last_name === NULL || $last_date != $row['date'] ||
        $last_name != $row['name'] ||
        $last_section != $row['section'] ||
        $last_payable != $row['payable']) {

        $last_date = $row['date'];
        $last_name = $row['name'];
        $last_section = $row['section'];
        $last_payable = $row['payable'];
        $date = $row['date'];
        $name = $row['name'];
        $section = $row['section'];
        $payable = $row['payable'];
    }
    echo '<tr style="text-align:center;">';
    echo '<td>'.$date.'</td>';
    echo '<td>'.$name.'</td>';
    echo '<td>'.$section.'</td>';
    echo '<td>'.$payable.'</td>';
    echo '<td>'.$row['product'].'</td>';
    echo '<td>'.$row['qty'].'</td>';
    echo '</tr>';
}

sql mysql php html-table

来源：https://stackoverflow.com/questions/52658465/show-the-data-based-on-a-foreign-key

2条答案

按热度按时间

2exbekwf1#

您的预期输出建议，当这三个值中的一个发生更改时，我们应该只显示一次name、section和payable值。因此，我们可以稍微修改对上一个问题的回答，以便在迭代结果集时保持这三列的状态。

$last_date = NULL;
$last_name = NULL;
$last_section = NULL;
$last_payable = NULL;

while ($row = mysql_fetch_array($result)) {
    $date = "";
    $name = "";
    $section = "";
    $payable = "";
    if ($last_name === NULL ||
        $last_date != $row['date'] ||
        $last_name != $row['name'] ||
        $last_section != $row['section'] ||
        $last_payable != $row['payable']) {

        $last_date = $row['date'];
        $last_name = $row['name'];
        $last_section = $row['section'];
        $last_payable = $row['payable'];
        $date = $row['date'];
        $name = $row['name'];
        $section = $row['section'];
        $payable = $row['payable'];
    }
    echo '<tr style="text-align:center;">';
    echo '<td>'.$date.'</td>';
    echo '<td>'.$name.'</td>';
    echo '<td>'.$section.'</td>';
    echo '<td>'.$payable.'</td>';
    echo '<td>'.$row['product'].'</td>';
    echo '<td>'.$row['qty'].'</td>';
    echo '</tr>';
}

赞(0）回复(0）举报 2021-06-20

pbpqsu0x2#

问题不在您的查询中。我检查了问题是否在您的逻辑中，您将其放入while循环以打印值。可能存在多个用户的用户名相同的情况。所以你可以用 confirmation ，对于每个订单都是唯一的。

$result = mysql_query("
    SELECT reservation.firstname, reservation.lastname, reservation.payable, reservation.floor, reservation.section, orders.product, orders.qty, reservation.date, orders.confirmation
    FROM orders
    INNER JOIN reservation
    ON orders.confirmation = reservation.confirmation 
    WHERE reservation.date = CURDATE() && reservation.floor = '23rd Floor'
    ");

$last_confirmation_seen = NULL;

while ($row = mysql_fetch_array($result)) {
    $firstname = "";
    $lastname = "";
    $section = "";
    $payable = "";
    if ($last_confirmation_seen === NULL || $row['confirmation'] != $last_confirmation_seen) {
        $last_confirmation_seen = $row['confirmation'];
        $firstname = $row['firstname'];
        $lastname = $row['lastname'];
        $section = $row['section'];
        $payable = $row['payable'];
    }
    echo '<tr style="text-align:center;">';
    echo '<td>'.$firstname.' '.$lastname.'</td>';
    echo '<td>'.$section.'</td>';
    echo '<td>'.$payable.'</td>';
    echo '<td>'.$row['product'].'</td>';
    echo '<td>'.$row['qty'].'</td>';
    echo '</tr>';
}

试试这个，我想这对你有帮助。

赞(0）回复(0）举报 2021-06-20

我来回答

显示基于外键的数据

2条答案

相关问题

热门标签

最新问答