如何使用codeigniter在单个页面中维护两个表数据

Question

I wanna fetch and show data from 2 tables on basis of invoice_id for one customer at one time. shown

   invoice                                     invoice_description
 -------------                                ----------------------
invoice_no  name   address                   product  invoice_no  qantity
  789       adi    ghfgfgfgh                   a       789         1
  786       abc    sdsddasd                    b       789         2

I wanna output like this:-

If I click on 789 Invoice no.

                    invoice no-789               name-adi
                             product    quantity
                               a           1
                               b           2

but my output is like this:-

             invoice no-789   name-adi
                  product     quantity
                   a             1
                   b             2
         ------------------------------
              invoice no-786   name-abc
                  product     quantity
                    a           1
                    b           2

how to resolve it

controller:-

   //$id is current invoice_no;
 public function invoice($id)
    {

    $this->load->database();  

    $this->load->model('useradmin');  
    $query = $this->useradmin->selectdealer($id);
  $ids = $this->useradmin->selectdealer1($id);
  $data['dealers'] =  $query;
                    $data['id'] = $ids;

        $this->load->view('envoice',$data);
}
 model:-
    public function selectdealer($id)  
 {  

 $this->db->where('invoice_no', $id);
 $query = $this->db->get('invoice_description');
 return $query->result();  

}   
public function selectdealer1($id)  
{  

  $query = $this->db->get('invoice');  
  $this->db->where('invoice_no', $id);
  return $query->result();  

}   
 view:-
   foreach($id as $ids)
   {
     echo $ids->invoice_no;
     echo $ids->name;
      foreach($dealers as $dealer)
      {
       echo $dealer->product;

       }
    }

its returns all the data invoice_no and Name which present on my table second foreach loop works correctly but first foreach loop returns all the data which present in table

Answer 1

Use join query

$this->db->select('*');
$this->db->from('invoice');
$this->db->join('invoice_description', 'invoice_description.invoice_no= invoice.invoice_no');
$this->db->where('invoice_no', $id);
$query = $this->db->get();
 return $query->result();

For the second function

$this->db->select('*');
$this->db->from('invoice_description');
$this->db->join('invoice', 'invoice_description.invoice_no= invoice.invoice_no');

$query = $this->db->get();
 return $query->result();