PHP 查询 - 如果表中存在则排除

Question

orderfood

orderfood_id   food_id   total_amount

foodcancel

foodcancel_id   food_id  status

$query = $this->db->query("SELECT * FROM order_food of LEFT JOIN `foodcancel` fc ON of.food_id = fc.food_id WHERE of.orderfood_id = '" . (int)$orderfood_id . "'");

    $order_foods = $query->rows;

上面是我的查询，我想要的是，如果 foodcancel 表中有 food_id ，将其从行中排除，可以这样做吗？

Answer 1

要排除现有值，您可以尝试检查 null 来查找相应的匹配值    SELECT *     FROM order_food of     LEFT JOIN foodcancel fc ON of.food_id = fc.food_id          and  of.food_id  = your_value      WHERE fc.orderfood_id is  null         无论如何，你不应该在你的sql代码中使用php var，因为这样你就有sqlinjection的风险，为了避免这种情况，你应该看看准备好的语句和绑定参数

Answer 2

这是很有可能做到的。按照我的逻辑。首先，您必须获取 food_cancel 表上的所有 food_id。然后将其保存到 variabel 中，并在显示 orderFood 表并添加 NOT IN 条件时使用它。我已经为你写了代码<?php// Get Food Id From Cancel&nbsp;$orderCancel&nbsp; &nbsp;= mysqli_query($mysqli, "SELECT * FROM `foodcancel`");$cancelId&nbsp; &nbsp; &nbsp; = "";while ($cancel = mysqli_fetch_array($orderCancel)) {&nbsp; $cancelId&nbsp; &nbsp;.= $cancel["food_id"].",";};$cancelId&nbsp; &nbsp; &nbsp; = substr($cancelId, 0, -1);// Put Food Id on Cancel Table into NOT IN Condition Database$orderFood&nbsp; &nbsp; &nbsp;= mysqli_query($mysqli, "SELECT * FROM `orderfood` WHERE food_id NOT IN ($cancelId)");while ($order&nbsp; = mysqli_fetch_assoc($orderFood)) {&nbsp; $food[]&nbsp; &nbsp; &nbsp; = $order;};echo json_encode($food);?>