Concanate two or more rows from result into single result on CI activerecord

Question

I have situation like this, I want to get value from database(this values used comma delimited) from more than one rows based on month and year that I choose, for more detail check this out..

My Schedule.sql :

+---+------------+-------------------------------------+
|ID |Activ_date  | Do_skill                            |
+---+------------+-------------------------------------+
| 1 | 2020-10-01 | Accountant,Medical,Photograph       |
| 2 | 2020-11-01 | Medical,Photograph,Doctor,Freelancer|
| 3 | 2020-12-01 | EO,Teach,Scientist                  |
| 4 | 2021-01-01 | Engineering, Freelancer             |
+---+------------+-------------------------------------+

My skillqmount.sql :

+----+------------+------------+-------+
|ID  |Date_skill  |Skill       |Price  |
+----+------------+------------+-------+
| 1  | 2020-10-02 | Accountant | $ 5   |
| 2  | 2020-10-03 | Medical    | $ 7   |
| 3  | 2020-10-11 | Photograph | $ 5   |
| 4  | 2020-10-12 | Doctor     | $ 9   |
| 5  | 2020-10-01 | Freelancer | $ 7   |
| 6  | 2020-10-04 | EO         | $ 4   |
| 7  | 2020-10-05 | Teach      | $ 4   |
| 8  | 2020-11-02 | Accountant | $ 5   |
| 9  | 2020-11-03 | Medical    | $ 7   |
| 10 | 2020-11-11 | Photograph | $ 5   |
| 11 | 2020-11-12 | Doctor     | $ 9   |
| 12 | 2020-11-01 | Freelancer | $ 7   |
+----+------------+------------+-------+

In my website I want to make calculation with those two table. So if in my website want to see start from date 2020-10-01 until 2020-11-01 for total amount between those date, I try to show it with this code :

Output example

+----+-----------+-----------+---------+
|No  |Date Start |Date End   |T.Amount |
+----+-------- --+-----------+---------+
|1   |2020-10-01 |2020-11-01 |$ 45     | <= this amount came from $5+$7+$5+$7+$5+$9+$7
+----+-------- --+-----------+---------+

Note : 
Date Start : Input->post("A")
Date End : Input->post("B")
T.Amount : Total Amount based input A and B (on date)

I tried this code to get it :

<?php 
$startd = $this->input->post('A');
$endd= $this->input->post('B');
$chck = $this->db->select('Do_skill')
                    ->where('Activ_date >=',$startd)
                    ->where('Activ_date <',$endd)
                    ->get('Schedule')
                    ->row('Do_skill');
$dcek = $this->Check_model->comma_separated_to_array($chck);
$t_amount = $this->db->select_sum('price')
                    ->where('Date_skill >=',$startd)
                    ->where('Date_skill <',$endd)
                    ->where_in('Skill',$dcek)
                    ->get('skillqmount')
                    ->row('price');

echo $t_amount; ?>

Check_model :

public function comma_separated_to_array($chck, $separator = ',')
{
  //Explode on comma
  $vals     =   explode($separator, $chck);
  $count    =   count($vals);
  $val      =   array();
  //Trim whitespace
  for($i=0;$i<=$count-1;$i++) {
    $val[]   .=   $vals[$i];
  }
  return $val;
}

My problem is the result from $t_amount not $45, I think there's some miss with my code above, please if there any advice, I very appreciate it...Thank you...

Answer 1

Your first query only return 1 row data.

I think you can do something like this for the first query.

$query1 = $this->db->query("SELECT Do_skill FROM schedule WHERE activ_date >= $startd and  activ_date < $startd");
        $check = $query1->result_array();
        $array = [];

        foreach($check as $ck){
            $dats = explode(',',$ck['Do_skill']);
            
            $counter = count($dats);

            for($i=0;$i<$counter;$i++){
                array_push($array,$dats[$i]);
            }

and you can use the array to do your next query :)

Answer 2

The array $dcek has the values

Accountant,Medical,Photograph

The query from Codeigniter is

SELECT SUM(`price`) AS `price` FROM `skillqmount` 
WHERE `Date_skill` >= '2020-10-01' AND 
`Date_skill` < '2020-11-01' AND 
`Skill` IN('Accountant', 'Medical', 'Photograph')

which returns 17 - this matches the first three entries in your data.

Your first query will only ever give one row, even if the date range would match multiple rows.