How to get data from two tables in Mysql and Codeigniter

Question

I am working on a point of sale for a hardware store designed using the codeigniter framework.

I want to come up with a detailed account report, as in the picture below

I have two tables, which are sales and payments, I want to get data from these two tables so that I can generate a report on the account of a particular client.

This will help, as the client will be able to see all the items that he bought on a cash basis or on the basis of a loan, as well as show how much they paid, grouped by date.

It will also be possible to calculate the required amount.

I can list separately (sales and payment) using the code below and thereby calculate the amount due.

Sale

<?php $salestotal = 0; if (is_array($sales) || is_object($sales)) { foreach ($sales as $key=>$sale): {?> <tr> <td> <?php echo $sale->date; ?> </td> <td> <?php echo $sale->id; ?> </td> <td> <?php echo $sale->grand_total; ?> </td> <td> <?php echo $sale->paid; ?> </td> <td></td> </tr> <?php if(isset($sale->grand_total)) $salestotal += $sale->grand_total; } endforeach ; }?> 

Payments

 <?php $paymentstotal = 0; if (is_array($payments) || is_object($payments)) { foreach ($payments as $key=>$payment): {?> <tr> <td> <?php echo $payment->date; ?> </td> <td class="text-center"> <?php echo $payment->sale_id; ?> </td> <td class="text-center"> <?php echo $payment->paid_by; ?> </td> <td class="text-center"> <?php echo $payment->cheque_no; ?> </td> <td class="text-center"> <?php echo $payment->amount; ?> </td> <td class="text-center"> <?php echo $this->customers_model->getUserna($payment->created_by); ?> </td> <td class="text-center"> <?php echo $payment->pos_paid; ?> </td> <td class="text-center"> <?php echo $payment->pos_balance; ?> </td> <td class="text-right"> <?php echo $this->customers_model->getStorename($payment->store_id); ?> </td> </tr> <?php if(isset($payment->amount)) $paymentstotal += $payment->amount; } endforeach ;}?> 

My controller

 function statement($id = NULL) { if (!$this->Admin) { $this->session->set_flashdata('error', $this->lang->line('access_denied')); redirect('pos'); } if($this->input->get('id')) { $id = $this->input->get('id', TRUE); } $this->data['sales'] = $this->customers_model->getSales($id); $this->data['payments'] = $this->customers_model->getPayments($id); $this->data['customer'] = $this->customers_model->getCustomername($id); $this->data['customer_id'] = $id; $this->page_cons('customers/statement', $this->data); } 

My model

 public function getSales($id) { $q = $this->db->get_where('sales', array('customer_id' => $id)); if( $q->num_rows() > 0 ) { return $q->result(); } return FALSE; } public function getPayments($id) { $q = $this->db->get_where('payments', array('customer_id' => $id)); if( $q->num_rows() > 0 ) { return $q->result(); } return FALSE; } 

How do I combine these two tables?

I hope I am clear enough about this, I tried on Google, but I had no luck.

I would appreciate any help. Thanks

Edit

MYSQL Tables

Sale

Payments

Answer 1

You can reuse the specified getSales() and getPayments() methods in the new getSalesWithPayments() method to combine the two results:

 public function getSalesWithPayments($customerId) { $sales = []; foreach ($this->getSales($customerId) as $sale) { $sale->payment = null; $sales[$sale->id] = $sale; } foreach ($this->getPayments($customerId) as $payment) { $sales[$payment->sale_id]->payment = $payment; } return $sales; } 

In the first cycle, you initialize $sale->payment (for the case when the sale has no payment yet) and fill in the $sales array, which is indexed by the id column. Thus, you can easily access any sale by your id without an expensive search.

In the second cycle, you assign payments to the relevant sales.

Note that you may need to add logic to handle empty results (which would not be necessary if you just returned an empty array instead of FALSE ).

Now you can use it as ..

Controller:

 $this->data['sales'] = $this->customers_model->getSalesWithPayments($id); 

View:

 <?php foreach ($sales as $sale): ?> <tr> <td><?php echo $sale->date; ?></td> <td><?php echo $sale->id; ?></td> ... <?php if($sale->payment !== null): ?> <td><?php echo $sale->payment->date; ?></td> <td><?php echo $sale->payment->amount; ?></td> ... <?php else: // empty cells if no payment ?> <td></td> <td></td> ... <?php endif; ?> </tr> <?php endforeach; ?> 

Answer 2

If you place a table of sales and payments , the answer will be better and more accurate. You can use MySQL Join to get combined data from 2 tables.

Answer 3

Try the following:

 $this->db->select('*') ->from('sales') ->join('payments', 'payments.sales_id = sales.id', 'right') ->where(array('sales.customer_id' => $id)); $results = $this->db->get();