Building Data array for a chart

[eluser]jleequeen[/eluser]
I'm trying to build a data array to use for a chart. This chart for example will be used to show daily progress of activity. So basically the data array would contain two pieces of info for each day (the date and amount of activity) and would look like the following:

'2008-02-05', '12'

What I'm trying to figure out is exactly how to gather this data array. In my database, I can grab the records I need to for days where there was activity with no problem. But what about days there was no activity, because they wouldn't be in the database. But on a chart where I want to show the entire year, I would need to show all days, so I would want zeros on days there was no activity.

Anyone have any ideas on how I would go about building this data array to pass to my chart?

Thanks in advance.

[eluser]tonanbarbarian[/eluser]
You can actually do all of this in the query
It is what is commonly refered to as a cross-tab query
I have done them heaps of time.
Unfortunately it would take me too long to explain, and I doubt it would explain it well
I recommend googling cross tab query mysql and see what it comes it with
It should show you how to build a query that gives you all of your data exactly as you need it in one monolithic query
And despite the site of the query string it will find the data pretty quickly for you

[eluser]xadio[/eluser]
Couldn't you just grab the data that exists and if it isn't in the array then the amount of activity would be 0. I may have misunderstood you, but this is what I saw from your description. (I didn't get a chance to test the code so errors may be present.)

So something like

Code:

$ignore = true; //To fix the php color

//Load helper
$this->load->helper('array');

//Set year
$year = 2008;

/* Get the data.  It would something like this:
* Array(
*   2008-01-05 => 12,
*   ...
*   2008-02-05 => 5
* );
*/
$activityHash = getActivityFromDB($year);

//Either fill up hash with items, zeros if empty
for($month = 1; $month <= 12; $month++) {
  for($day = 1; $day <= cal_days_in_month(CAL_GREGORIAN, $month, $year); $day++) {
    $monthStr = ($month < 10)?"0".$month:$month;
    $dayStr = ($day < 10)?"0".$day:$day;
    $timestamp = "$year-$monthStr-$dayStr";
    element($timestamp, $activityHash, 0);
  }
}

//Or when converting to a chart
...
if(!isset($activityHash[$key])) {
  //return, print, or something 0
}
...

[eluser]jleequeen[/eluser]
@xadio

Thanks for your reply. I like your solution. Maybe I was over thinking things a bit or something.

[eluser]xadio[/eluser]
[quote author="jleequeen" date="1202286933"]@xadio

Thanks for your reply. I like your solution. Maybe I was over thinking things a bit or something.[/quote]

I am glad I could help. This morning I was thinking about it and if it is called numerous times I thought of a better to save computation.

This only pertains to the first method of adding zeros to a hash.

Code:

$ignore = true; //Fix for php color

//Only run once a year
function cacheYearInAHash($year) {
  $handle = fopen(APPPATH . 'cache/YearInAHash.txt', 'w+') or die('Could not open');
  fwrite($handle, '$activityYear=array(') or die('Could not write');
  for($month = 1; $month <= 12; $month++) {
    for($day = 1; $day <= cal_days_in_month(CAL_GREGORIAN, $month, $year); $day++) {
      $monthStr = ($month < 10)?"0".$month:$month;
      $dayStr = ($day < 10)?"0".$day:$day;
      $timestamp = "$year-$monthStr-$dayStr";
      fwrite($handle, "${timestamp}=>0,");
    }
  }
  fwrite($handle, ');');
}

$year = date('Y');
//Some conditional checking the last creation date of the hash
#if YearInAHash.txt creation date != $year; then
cacheYearInAHash($year);
#end if

//Work with it
$activityHash = getActivityDataFromDB($year);
include(APPPATH . 'cache/YearInAHash.txt');
$activityHash = array_merge($activityYear, $activityHash);

It just depends on what you are trying to achieve. Good luck with your work!