How can I get data from two tables in Mysql and Codeigniter

Question

I am working on a point of sale for a hardware shop developed using codeigniter framework.

I want to come up with a detailed statement of account like in the picture below

I have two tables which are sales and payment, I want to get data from this two tables so that I can generate a statement of account of a specific customer.

This will help since the customer can be able to see all the items he/she bought on cash basis or credit basis and also show how much they has paid grouped by date.

It will also be possible to calculate the amount due.

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

Sales

<?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 this two tables?

I hope I am clear enough on this question, I have been trying to Google but I have no luck.

I will appreciate any help. Thanks

Edit

MYSQL tables

Sales

Payments

Answer 1

You can reuse the given methods getSales() and getPayments() in a new method getSalesWithPayments() 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 loop you initialize $sale->payment (for the case that a sale has no payment yet) and populate the $sales array, which is indexed by the id column. This way you can easily access any sale by its id without an expensive search.

In the second loop you assign the payments to the corresponding sales.

Note that you might need to add logic to handle empty results (which wouldn't be necessary if you would just return an empty array instead of FALSE).

You can now use it like..

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