8

I'm running a mysql query and the resulting array is something like this, that changes every month:

Array(    
[0] => Array
    (
        [day] => 2
        [count] => 10
    )

[1] => Array
    (
        [day] => 4
        [count] => 39
    )

[2] => Array
    (
        [day] => 5
        [count] => 51
    )
    )

I'd like to add days so I get 31 days, the ones added would be filled with 0, like this:

Array(    
[0] => Array
    (
        [day] => 1
        [count] => 0
    )

[1] => Array
    (
        [day] => 2
        [count] => 10
    )

[2] => Array
    (
        [day] => 3
        [count] => 0
    )

[3] => Array
    (
        [day] => 4
        [count] => 39
    )
    )

I'd like to fill the array with 31 days, using the days and count data that are already there... like in the second example... the days 1 and 3 wanst there... so I added them with the count value 0... in order... 1 ~ 31 days

The query is pretty simple:

SELECT day(`dates`) day, count(`dates`) count FROM `calls` where month(dates) = 7

so each month has different amount of "days", some months there's no calls.

Improve this question
7
  • fully uncleared . what you want ?? Commented Jul 9, 2015 at 3:57
  • you want cumulative count ? Commented Jul 9, 2015 at 3:58
  • I want to create a new array using the one I have... with 31 days... sometimes my array has 20 days, the ones that are not in there should be added with the count value 0... just like the example... the days 1 and 3 wasnt in the first one, so in the second one they are... with the count value 0 Commented Jul 9, 2015 at 3:59
  • Add the SQL query to your Question please. This can be done in PHP while creating the array. Commented Jul 9, 2015 at 4:01
  • 1
    I think a calendar table is what you need like suggested in the answer. Commented Jul 9, 2015 at 4:18

4 Answers 4

Reset to default
3

I would recommend creating a calendar table in your database to hold all dates. Then you can select from this table and left join your other data to get a total count per day. This article is a good place to start to create a calendar table and this stackoverflow post contains a similar question and answer to your problem.

There are php solutions as well such as iterating a date range using the php built in DateTime and DateInterval Classes as mentioned in this stackoverflow question

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

Also try this, modify range(1,10) as per your requirement

[akshay@localhost tmp]$ cat test.php 
<?php

$array=array( 
              array("day"=>2,"count"=>10),  
              array("day"=>4,"count"=>39),
              array("day"=>5,"count"=>51)
            );


function modify_array($array,$range)
{
    $tmp = array();
    array_map(function($_) use (&$tmp){ $tmp[$_] = array("day"=>$_,"count"=>0); },$range);
    $output = array_combine( array_column($array,"day"), $array ) + $tmp;
    ksort($output);
    return array_values($output);
}

// Output - modify range(1,10) as per your wish
print_r( modify_array($array, range(1,10)) );

?>

Output

[akshay@localhost tmp]$ php test.php 
Array
(
    [0] => Array
        (
            [day] => 1
            [count] => 0
        )

    [1] => Array
        (
            [day] => 2
            [count] => 10
        )

    [2] => Array
        (
            [day] => 3
            [count] => 0
        )

    [3] => Array
        (
            [day] => 4
            [count] => 39
        )

    [4] => Array
        (
            [day] => 5
            [count] => 51
        )

    [5] => Array
        (
            [day] => 6
            [count] => 0
        )

    [6] => Array
        (
            [day] => 7
            [count] => 0
        )

    [7] => Array
        (
            [day] => 8
            [count] => 0
        )

    [8] => Array
        (
            [day] => 9
            [count] => 0
        )

    [9] => Array
        (
            [day] => 10
            [count] => 0
        )

)

-- Edit for comment--

for older version of PHP which doesn't have array_column

function modify_array($array,$range)
{
    $tmp = array(); 
    array_map(function($_) use (&$tmp){ $tmp[$_] = array("day"=>$_,"count"=>0); },$range);
    $output = array_combine( array_map(function($e){return $e["day"];}, $array), $array ) + $tmp;
    ksort($output);
    return array_values($output);
}
Improve this answer

2 Comments

array_column is only for php 5.5 so I had to update mine... what a pain in the arse it was... but it workes too... I'm going to try both...
@Edgar : I edited my post have a look, which supports older version too now
1

Assuming you do this for more months than July, first set your target month as a variable and get the amount of days:

$month = '7';
$daysInMonth = cal_days_in_month(CAL_GREGORIAN, $month, 2003);

Then run your query and iterate over results to build an array indexed by day:

while ($row = $query->fetch_assoc()) {
    $results[$row['day']] = $row;
}

Now you can do a simple for loop and fill in the missing pieces: 

for ($i = 1; $i <= $daysInMonth; $i++) {
    if (!isset($results[$i])) {
        $results[$i] = array(
            'day' => $i,
            'count' => 0
        );
    }
}
Improve this answer

Comments

0 
$result = your database query result;
$days = array();
for($i=0;$i<=31;$i++){
{
   foreach($result as $aresult){
      if(($aresult['day']-1)==$i){
             $days[i] = $aresult;
             break;
      }
   }
}
Improve this answer

2 Comments

I took the -1 and added else { $days[$i] = array('day' => $i, 'count' => 0 ); } inside the foreach and it worked, thanks
This creates a lot of unnecessary extra looping. It might work but it's inefficient.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.