Convert array to another array with counts

PHP
1

How can I make this #Array Sample# look like the #End Result Array#? I am making a google graph for hourly views. Below is an example of my array from the database and it needs to be summarized to counts per hour and if there are no hits per hour it needs to maintain a 0 for that hour so the graph is accurate. And the ID is for a specific page so I have to loop this again.

# My Array Sample #
Array(
	[0] => Array(
			[id] => 1
			[date] => 2010-05-17 20:28:19
		)

	[1] => Array(
			[id] => 1
			[date] => 2010-05-17 21:28:19
		)

	[2] => Array(
			[id] => 1
			[date] => 2010-05-17 23:28:19
		)

	[3] => Array(
			[id] => 1
			[date] => 2010-05-17 23:28:19
		)

	[4] => Array(
			[id] => 1
			[date] => 2010-05-18 02:28:19
		)

)

A summary of the logic would be.
day-hour 17-20 count 1
day-hour 17-21 count 1
day-hour 17-22 count 0
day-hour 17-23 count 2
day-hour 18-00 count 0
day-hour 18-01 count 0
day-hour 18-02 count 1

# End Result Array #
Array(
	[0] => Array (
			[day-hour] => 17-20
			[count] => 1
		)
	
	[1] => Array (
			[day-hour] => 17-21
			[count] => 1
		)

	
	[2] => Array (
			[day-hour] => 17-22
			[count] => 0
		)
	
	[3] => Array (
			[day-hour] => 17-23
			[count] => 2
		)
	
	[4] => Array (
			[day-hour] => 18-00
			[count] => 0
		)
	
	[5] => Array (
			[day-hour] => 18-01
			[count] => 0
		)
	
	[6] => Array (
			[day-hour] => 18-02
			[count] => 1
		)

)
2

Hehe one more question. “foreach($data as &$entry)” what is that & sign in front of $entry for? I have never seen that before.

3

This sounds like it should be done in your rdbms to me.

Would the following not suffice?


SELECT
    COUNT(*) AS 'count'
  , DATE_FORMAT(`date`, '%d-%H') AS 'date'
FROM
  table
GROUP BY
  DATE_FORMAT(`date`, '%d-%H')
ORDER BY
  `date` ASC
4

What code do you have so far?

5

No problem dude, see you around! :smiley:

6

Wow Anthony, yep that works perfect! I was testing the range(0,23) idea but yours has better scalability with the Gregorian date feature I think. Thanks a ton! You have helped out tremendously.

7

I see, what about…


<?php
/**
 *  Create something to hold the months entries
 */
$data = array();

/**
 *  Create a blank result-set, this is for June, see the 6?
 */
for($day = 1; $day <= cal_days_in_month(CAL_GREGORIAN, 6, 2010); $day++){
  foreach(range(0, 23) as $hour){
    array_push(
      $data,
      array(
        'day-hour'  => sprintf('&#37;02d-%02d', $day, $hour),
        'count'     => 0
      )
    );
  }
}

/*
* This would be from your chosen RDBMS
*/
$database_entries = array(
  array(
    'day-hour'  => '01-15',
    'count'     => 10
  ),
  array(
    'day-hour'  => '02-15',
    'count'     => 20
  ),
  array(
    'day-hour'  => '03-15',
    'count'     => 30
  ),
);

/**
 *  Collate 'em
 */
foreach($database_entries as $database_entry){
  foreach($data as &$entry){
    if($database_entry['day-hour'] === $entry['day-hour']){
      $entry['count'] = $database_entry['count'];
    }
  }
}

/*
* $data array now populated.
*/
?>
8

Actually Anthony, that is much cleaner to do that in mysql. Now, I am still trying to figure out how to find the hours with no results so they can have 0 on the chart. Without the 0 for those empty hours the chart isn’t very accurate. So I have to be able to see where there is inactivity. I think I am only going to pull 1 day of data at a time so I can probably just loop $i++ till it hits 23.

Here is a sample from your query once I inserted that.

Array
(
    [0] =&gt; Array
        (
            [count] =&gt; 2
            [date] =&gt; 00-00
        )

    [1] =&gt; Array
        (
            [count] =&gt; 1
            [date] =&gt; 01-00
        )

    [2] =&gt; Array
        (
            [count] =&gt; 3
            [date] =&gt; 01-01
        )

    [3] =&gt; Array
        (
            [count] =&gt; 2
            [date] =&gt; 01-04
        )

    [4] =&gt; Array
        (
            [count] =&gt; 2
            [date] =&gt; 01-05
        )

    [5] =&gt; Array
        (
            [count] =&gt; 1
            [date] =&gt; 01-06
        )
9

Sorry, I was out having a nice beer. :slight_smile:

It implies a reference.

10

Hi Anthony, thanks for the reply! So far I have this which sums up the counts for the interval. I am a little confused on how to get the hours with 0 into the array since they don’t exist in the original result. Also the key is the day-hour which I think might be fine.


# Divide into intervals
	$interval_array = array();
	foreach($result as $interval) {
		array_push($interval_array, date('d-H', strtotime($interval['date'])));
    }

$newdata = (array_count_values($interval_array));


# which results as #
Array(
    [17-20] => 1
    [17-21] => 1
    [17-23] => 2
    [18-02] => 1
)