Adding months in array which are missing

Question

I have a SQL table from which I am returning months and views. Query :

   function chartData(){
   $q=$this->db->select("DATE_FORMAT(insertTime,'%Y-%M') as Month , count(*) as value")
            ->group_by("DATE_FORMAT(insertTime,'%Y %M')")
            ->get('users');
 return $q->result();
  }

EDIT : The above function returns this

    Array
 (
    [0] => stdClass Object
        (
        [Month] => 2018-August
        [value] => 21
 )

    [1] => stdClass Object
 (
        [Month] => 2018-October
        [value] => 3
 )

    [2] => stdClass Object
 (
        [Month] => 2018-September
        [value] => 3
 )

    [3] => stdClass Object
 (
        [Month] => 2019-January
        [value] => 1
 )

    [4] => stdClass Object
 (
        [Month] => 2019-October
        [value] => 1
 )

PHP

$data=$this->users->chartData();
    $keys=array();
    $values=array();
    foreach ($data as $d) {
        array_push($keys, $d->Month);
        array_push($values, $d->value);
    }

    $results['keys']=json_encode($keys);
    $results['values']=json_encode($values);

    print_r($results['keys']);
    // returns ["2018-August","2018-October","2018-September","2019-January","2019-October"]
    print_r($results['values']);
    // returns ["21","3","3","1","1"]

Simplified form :

Month : Aug 2018 : Views : 19
Month : Oct 2018 : Views : 1
Month : Jan 2019 : Views : 2
Month : Feb 2019 : Views : 8
Month : Jul 2019 : Views : 14

What I want is to insert the missing month with views=0 if the month doesn't exist in the results :

Month : Aug 2018 : Views : 19
Month : Sep 2018 : Views : 0 - This isn't in the table. I assigned to 0
Month : Oct 2018 : Views : 1
Month : Nov 2018 : Views : 0 - This isn't in the table. I assigned to 0
Month : Dec 2018 : Views : 0 - This isn't in the table. I assigned to 0

I followed this answer and this is the solution I want but with years. If you see this answer, you won't find year so its easy to manipulate this by creating the months array with values=0 before and later overwriting it with database values
LINK : Query missing months in which we haven't sold anything?

Just add a counter to your loop. Whenever the row result doesn't match the counter, print 0. — Strawberry
– Strawberry, Commented Aug 18, 2018 at 6:35
Thanks for your response :) Please check the edit above. What should I match? I have to insert a key if it isn't there. Can you provide a solution? It'll be very helpful for me ! — Muhammad Osama
– Muhammad Osama, Commented Aug 18, 2018 at 6:45
Just adjust this to use months instead of days for the interval. — mickmackusa
– mickmackusa ♦, Commented Sep 23, 2022 at 13:05

Andreas · Accepted Answer · 2018-08-18 07:01:34Z

Perhaps like this?
I loop from start of keys to end of keys as strtotime.

I use array_search to see if it's found in values if not add zero values else add the value.

$keys = ["2018-August","2018-October","2018-September","2019-January","2019-October"];
$values = [21,3,3,1,1];

For($i= strtotime($keys[0]); $i<=strtotime(end($keys)); $i+=86400*31){
    $search = array_search(date("Y-F", $i), $keys);
    if($search !== false){
        $new[$keys[$search]] = $values[$search];
    }else{
        $new[date("Y-F", $i)] =0;
    }
}
$new[end($keys)] = end($values); //add last item since I loop between the dates
Var_dump($new);

Output:

array(15) {
  ["2018-August"]=>
  int(21)
  ["2018-September"]=>
  int(3)
  ["2018-October"]=>
  int(3)
  ["2018-November"]=>
  int(0)
  ["2018-December"]=>
  int(0)
  ["2019-January"]=>
  int(1)
  ["2019-February"]=>
  int(0)
  ["2019-March"]=>
  int(0)
  ["2019-April"]=>
  int(0)
  ["2019-May"]=>
  int(0)
  ["2019-June"]=>
  int(0)
  ["2019-July"]=>
  int(0)
  ["2019-August"]=>
  int(0)
  ["2019-September"]=>
  int(0)
  ["2019-October"]=>
  int(1)
}

ScaisEdge · Accepted Answer · 2018-08-18 06:59:56Z

1

You could use a for loop

$data=$this->users->chartData();
$keys=array();
$values=array();
for ($cnt = 0; $cnt<12; $cnt++){
  if (isset($data[$cnt])){
    $keys[$cnt] = $cnt;
    $values[$cnt] = $data[$cnt]['value'];
  } else {
    $keys[$cnt] = $cnt;
    $values[$cnt] = 0;
  }
}

edited Aug 18, 2018 at 6:59

answered Aug 18, 2018 at 6:33

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

3 Comments

Muhammad Osama Over a year ago

Thanks for your response scaisEdge :) Please check the above edit. Actually the result returned isn't the same year. The year will be changed too. The answer you posted doesn't give me the solution to add month at the index where it should be added like there should be a key '2018 September ' with value zero just after the key august

ScaisEdge Over a year ago

@MuhammadOsama the loop remain valid .. but youn should begin to shiow the result starting for the month you need .. eg: 9 until 12 then start from 1 to 8 ..

Muhammad Osama Over a year ago

The above solution is printing the above result :( (screenshot link) attached prntscr.com/kk1uf7

Collectives™ on Stack Overflow

Adding months in array which are missing

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related