db_table => commitment

db_table => collection

I want to achieve something like this in datatable:

How can I achieve this with php and mysql and show it to datatable. Thanks in advance!
What I have tried in sql in codeigniter model:

SELECT c.*, t.* 
FROM commitment c 
LEFT JOIN collection t ON c.ref_no = t.ref_no
WHERE c.ref_no IN (SELECT ref_no FROM collection)
GROUP BY c.ref_no  

In controller:

public function collection_statement_list() {
         
    // Datatables Variables
    $draw = intval($this->input->get("draw"));
    $start = intval($this->input->get("start"));
    $length = intval($this->input->get("length"));
    
    $fetch = $this->Project_model->get_collection_statement();
    $data = array();

    foreach($fetch->result() as $r) {
      $ref_no = $r->ref_no;
      $comm_date = $this->Project_model->set_date_format($r->comm_date);
      $coll_date = $this->Project_model->set_date_format($r->trnx_date);
      
      
      $coll_summary = $r->amount.'<span ><small>('.$coll_date.')</small></span>';
            
      $data[] = array(
        $ref_no,
        $comm_date,
        $coll_summary,
      );
    }

    $output = array(
      "draw" => $draw,
      "recordsTotal" => $fetch->num_rows(),
      "recordsFiltered" => $fetch->num_rows(),
      "data" => $data
    );
    echo json_encode($output);
    exit();
  }

And the output in datatable is:
| ref_no | comm_date | collection summary | | ------ | ---------- | ------------------ | | 1 | 2022-10-05 | 500 (2022-10-05) | | 2 | 2022-10-05 | 100 (2022-10-05) | | 3 | 2022-10-06 | 400 (2022-10-07) | | 4 | 2022-10-07 | 0 | | 5 | 2022-10-07 | 800 (2022-10-08) | | 6 | 2022-10-08 | 0 |

CodePudding user response：

I think such way. Imagine you have a table about amounts. It is will be solution by sql:

Select ref_no, comm_date, sub(summary) as collect_summary from amount;

However you may use alternative way to be group with php such:

    <?php
    
    $amount = [
        ['ref_no'=> 1, 'amount'=>500 , 'date'=>'2022-10-05'],
        ['ref_no'=> 2, 'amount'=>100 , 'date'=>'2022-10-05'],
        ['ref_no'=> 1, 'amount'=>700 , 'date'=>'2022-10-05'],
        ['ref_no'=> 3, 'amount'=>400 , 'date'=>'2022-10-05'],
        ['ref_no'=> 3, 'amount'=>600 , 'date'=>'2022-10-05'],
        ['ref_no'=> 5, 'amount'=>800 , 'date'=>'2022-10-05'],
        ['ref_no'=> 1, 'amount'=>700 , 'date'=>'2022-10-05'],   
    ];
    
    $result = [];
    
    foreach($amount as $item) {
        $ref = $item['ref_no'];
        if(isset($result[$ref])) {
            $result[$ref]['collect_amount'] = $result[$ref]['collect_amount']   $item['amount'];
            
        }else{
            $result[$ref] = [
                'ref_no' => $ref,
                'date' => $item['date'],
                'collect_amount' =>$item['amount']
            ];
        }
    }
    echo '<pre>';
    print_r($result);

CodePudding user response：

And so in SQL only this query corresponds to your solution with group_concat...? I'm trying to answer to help those who would be looking for the solution in SQL only.

select COM.ref_no, 
if(COL.ref_no is not null,group_concat(COL.trnx_date,' (',COL.amount,')' separator ' '),'') as 'collection summary details',
if(COL.ref_no is not null,sum(COL.amount),0) as 'collection summary'
from commitment as COM 
left join collection as COL on COM.ref_no=COL.ref_no 
group by COM.ref_no