php - 如果两个表中都存在相同的 ID，如何从 Select DropDown 中隐藏选项

Question

我正在使用基于 MVC 结构的 PHP，我有 2 个表sale& receipt，当用户想要制作时，receipt他/她将选择下拉选项，Sale No.来自DB sale表，其值为sale_id，以便用户recept代表制作sale并sale_id存储在receipt表中，我想实现，如果sale_id存在于receipt表中，从选择下拉选项中隐藏该选项。

就像在下面的图像中一样，我有3 sale记录和1 receipt记录，receipt记录包含sale_id，我想Sale No.从选择下拉选项中隐藏它。

销售表

收据表

选择选项Receipt

<select class="form-control" id="sale_id" name="sale_id">
  <option disabled selected value="">Select One</option>
  <?php foreach($sales as $sale): ?>
  <option value="<?php echo $sale['sale_id']; ?>" <?php echo ($sale_id==$ sale[ 'sale_id']? 'selected="selected"': ''); ?>>
    <?php echo $sale['sale_no']; ?>&nbsp;=>&nbsp;
    <?php echo $sale['customer_name']; ?>
  </option>
  <?php endforeach; ?>
</select>

我的收据控制器：我如何在下拉选项中从销售表中获取数据

$this->model['sale'] = $this->load->model('sale/sale');
$this->data['sales'] = $this->model['sale']->getRows();

我的收据模型

class ModelTransactionReceipt extends HModel {

    protected function getTable() {
        return 'receipt';
    }

    protected function getView() {
        return 'vw_receipt';
    }

    public function getMaxReceiptNo(){
        $sql = "SELECT MAX(receipt_no) as max_no FROM `receipt`";
        $query = $this->db->query($sql);
        $record = $query->row;

        if(empty($record['max_no'])) {
            $max_no =  1;
        } else {
            $max_no =  $record['max_no']+1;
        }

        return $max_no;
    }
        public function getRemainingSale() {
           $sql = "SELECT sale_id";
           $sql = " FROM `sale`";
           $sql = " WHERE  sale_id NOT IN (SELECT sale_id FROM `receipt`)";

           $query = $this->db->query($sql);
           $record = $query->row;
    }

}

标签： phphtmlmysqldatabase

Answer 1

我在我的Sale Model：

    public function getRemainingSale() {
        $sql = "SELECT `sale_id`,";
        $sql .= " `sale_no`,";
        $sql .= " `customer_name`,";
        $sql .= " `sale_amount`";
        $sql .= " FROM   sale";
        $sql .= " WHERE  sale_id NOT IN (SELECT sale_id FROM `receipt`)";

        $query = $this->db->query($sql);
        $record = $query->rows;
        return $record;
    }

并Sale Model使用该函数调用receipt controller：

        $this->model['sale'] = $this->load->model('sale/sale');
        $this->data['sales'] = $this->model['sale']->getRemainingSale();
//        d($this->data['sales'],true);