Question 42304 in PHP Question posted in
Official manual can be found here.

Problem with foreach loop with group by one column but have multiple values another column in php

db_table => commitment

ref_no comm_date
1 2022-10-05
2 2022-10-05
3 2022-10-06
4 2022-10-07
5 2022-10-07
6 2022-10-08

db_table => collection

ref_no amount trnx_date
1 500 2022-10-05
2 100 2022-10-05
1 700 2022-10-06
3 400 2022-10-07
3 600 2022-10-08
5 800 2022-10-08
1 700 2022-10-08

I want to achieve something like this in datatable:

ref_no comm_date collection summary
1 2022-10-05 500 (2022-10-05) + 700 (2022-10-06) + 700 (2022-10-08) = 1900
2 2022-10-05 100 (2022-10-05) = 100
3 2022-10-06 400 (2022-10-07) + 600 (2022-10-08) = 1000
4 2022-10-07 0
5 2022-10-07 800 (2022-10-08) = 800
6 2022-10-08 0

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 class="text-primary"><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 |

Tags:

2

Answers


  1. 0

    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);
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search