How To Hide Option From Select DropDown If Same Id Exist In Both Table

I'm using PHP based on MVC structure, i have 2 tables sale & receipt, when a user want to make receipt he/she will select dropdown option, Sale No. coming from DB sale table, which has a value of sale_id, so that user make recept on behalf of sale, and sale_id store in receipt table, i want to achieve that if sale_id exist in receipt table, hide that option from Select DropDown Options.

Like in Below Images, i have 3 sale record and 1 receipt record, receipt record contains sale_id, i want to hide that Sale No. from select dropdown option.

SALE TABLE

RECEIPT TABLE

Select Option In 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>

My Receipt Controller : That How I'm getting Data From Sale Table In My DropDown Option

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

My Receipt Model

    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;
        }

    }

Snapey

7 years ago

Level 122

If using Eloquent and the tables have a relationship then you can run a query that only gets Sale that does not have receipt.

You then just loop over the sale details in the select dropdown options.

https://laravel.com/docs/5.7/eloquent-relationships#querying-relationship-absence

partabsaifzakir

7 years ago

Level 1

@SNAPEY - Not Using Eloquent Bro, Its Like Core PHP based on MVC structure.

partabsaifzakir

7 years ago

Best Answer

Level 1

I've made this function in my 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;
        }

And call Sale Model with that function in receipt controller:

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

Please or to participate in this conversation.