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
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();
I have some suggestion regarding your code. If you autoloading the database then you don't need to load it in the controller. Use the constructor to load the common model in the controller.
public function __construct() {
parent::__construct ();
$this->load->model ('useradmin');
}
public function invoice($id)
{
$this->load->database(); // if you autoloding the database then you don't need to load it here. if not then add this in __construct
$data = $this->useradmin->selectdealer($id);
$this->load->view('envoice',['data'=>$data]);
}
You want a data related to id and name so I have added $name variable. you have to pass it to the model by your self or if don't then remove that line of code.
public function selectdealer($id)
{
$this->db->select('id.product, id.quantity');
$this->db->from('invoice as i');
$this->db->join('invoice_description as id', 'id.invoice_no= i.invoice_no');
$this->db->where('i.invoice_no', $id);
$this->db->where('i.name', $name); // I have assumed this variable here so you have to take care of it or remove it if you don't need it.
$query = $this->db->get();
return $query->result();
//You should use $this->db->last_query(); to get last executed query to understand the query properly.
// I am alos suggestion to use codeigniter log. it would be very helpfull.
}
<?php
foreach($data as $single_data)
{
// Your expected result i have printed it just for knowledge. remove it and do you further code.
echo "<pre>";
print_r($single_data);
echo "<//pre>";
}
?>
Hope it helps you.