Help with arrays

[cleibesouza]

I've spent many hours trying to solve this with no success. So, here's my challenge.

Sample Data from DB:

Month Year         Measure

5         2010       164

6         2010       31

7         2010       20

8         2010       10

9         2010       10

10       2010       10

12       2010       10

1         2011        10

 

I need to display this data in a chart, but notice that I don't have data for month 11 and the chart must show all months with data and the month that I don't have data for (11 in this case) will show an interrupted line.

I need to put this into an array to display month/year as label and measure as chart value.

So, the above data needs to become:

Month Year         Measure

5       2010         164

6       2010         31

7       2010         20

8       2010         10

9       2010         10

10     2010         10

11     2010         --

12     2010         10

1       2011         10

 

The issue here isn't the chart, but how to add the missing month (11) to the array and keep the measures corresponding with their months.

 

Thank you.

[scootstah]

How are you displaying the rest of the months? What does the data look like?

[cleibesouza]

The data looks like the first example.

Month  Year          Measure

5          2010        164

6          2010        31

7          2010        20

8          2010        10 

9          2010        10

10        2010        10

12        2010        10

1          2011        10

 

I don't need to show ALL months for the year, but I need to show from first month collected to the last month collected including months that data wasn't collected.

In other words, let's say data was collected from May 2010 to Sept 2011, but Oct, Nov and December 2010 were skipped.

In this case I need to show from May 2010 through Sept. 2011 including Oct, Nov and Dec (months when data wasn't collected. Need to display label -month/year- on chart but no value).

Think as if you were looking at a chart. It would be hard to notice one month missing if we don't show the label (month/year) for the missing month.

In my data above, you can see that I'm missing month 11. So, what I need is a way to insert month 11 and year 2010(for this example) with no measure.

 

Thanks.

[Barand]

I'd generate an array with keys for all months in the range (201005 ... 201101) but without any values. Once you have the eexpected array, populate with values from the db

[cleibesouza]

Yes, I've done that by grabbing the first month and the last month and creating a monthArray using range.

$monthArray = range($firstMonth, $lastMonth). This gives me all the month from May through January (for this example).

 

I haven't been able to match the measure value with the right month, because once I create the array for the entire range, the indexes shift because of the added months that aren't on the database.

 

Thanks

[silkfire]

How do you do the query?

[cleibesouza]

SELECT measure, periodYear, periodMonth FROM tableName  WHERE pracID = 'xxx' AND qRecID =  'x'  ORDER BY periodYear, periodMonth";

[silkfire]

Nice. What you need is to generate an array with years and months of that range (the entire range), then fill the corresponding keys with the values from database table. This means keys in the range that were never filled will stay there, but empty.

 

I made a function for you.

 

function generate_month_range($month_start, $year_start, $month_end, $year_end) {
   $array = array();

   $start_time = mktime(0, 0, 0, $month_start, 1, $year_start);
   $end_time   = mktime(0, 0, 0, $month_end + 1, 1, $year_end);

   $months_count = round(($end_time - $start_time) / 2629743);

   for ($m = 0; $m < $months_count; $m++) {
      $date = strtotime("+$m months", $start_time);

      $array[date('Y', $date)][date('n', $date)] = '';
   }

   return $array;
}

echo '<pre>', print_r(generate_month_range(5, 2010, 9, 2011), true), '</pre>';

 

Will result in this array with empty values (example code is from May 2010 to Sep 2011, months are numeric!):

 

Array
(
    [2010] => Array
        (
            [5] => 
            [6] => 
            [7] => 
            [8] => 
            [9] => 
            [10] => 
            [11] => 
            [12] => 
        )

    [2011] => Array
        (
            [1] => 
            [2] => 
            [3] => 
            [4] => 
            [5] => 
            [6] => 
            [7] => 
            [8] => 
            [9] => 
        )

)

[cleibesouza]

This is a great step on the right direction and I appreciate your help.

I've been trying to plug the measure data from the db into the function but can't make it work.

 

Thanks.

[silkfire]

What are you trying to do? In what form do you need the chart data?

[cleibesouza]

This is a sample data from the database:

Month Year     Measure

5         2010 164

6         2010 31

7         2010 20

8         2010 10

9         2010 10

10         2010 10

12         2010 10

1         2011 10

 

I need to graph the measures using month/year labels. If a don't have a measure for a specific month(in this case Nov.), the label need to appear on the chart with empty measure.

I attached an image of how it should look like.

 

 

Thanks.

[Barand]

I haven't been able to match the measure value with the right month, because once I create the array for the entire range, the indexes shift because of the added months that aren't on the database.

 

 

If your keys contain year and month, as Silkfire and I suggested, then it looks like there is a problem with the way you are placing your values in the array

[cleibesouza]

I understand that. I'm not knowing how to plug the measure values into the year/month array. That's where I'm stuck.

[Barand]

if you have an array like silkfire, then if $year, $month, $measure are from your db,

$myarray[$year][$month] = $measure;

[cleibesouza]

I'll have to show you what I have so far because I still can't see how this is going to work. Sorry for my stupidity!!

 

QUERY:

$asthma5_40Query = 
"SELECT measure, periodYear, periodMonth FROM measureTemp 
WHERE practiceID = '" . $_SESSION['practiceID'] . "'
AND questionRecID =  1
ORDER BY periodYear, periodMonth";

 

EXECUTE QUERY AND GET TOTAL NUMBER OF RESULTS:

$asthma5_40Res = mysql_query($asthma5_40Query);
$queryNumRes = mysql_num_rows($asthma5_40Res);

 

LOOP THROUGH QUERY RESULTS:

while($row = mysql_fetch_array($asthma5_40Res)){
$mArray[] = $row['periodMonth'];
$yArray[] = $row['periodYear'];
$measureArray[] = $row['measure'];	
}//end while

 

GET FIRST, LAST MONTH AND FIRST, LAST YEAR:

$firstMonth = $mArray[0];
$lastMonth  = $mArray[$queryNumRes - 1];
$firstYear  = min($yArray);
$lastYear   = max($yArray);

 

THIS IS THE FUNCTION WRITEN BY SILKFIRE:

function generate_month_range($month_start, $year_start, $month_end, $year_end) {
   $array = array();

   $start_time = mktime(0, 0, 0, $month_start, 1, $year_start);
   $end_time   = mktime(0, 0, 0, $month_end + 1, 1, $year_end);

   $months_count = round(($end_time - $start_time) / 2629743);

   for ($m = 0; $m < $months_count; $m++) {
      $date = strtotime("+$m months", $start_time);

      $array[date('Y', $date)][date('n', $date)] = '';
   }
   return $array;

 

I'm sorry, but I still don't see how to  pass months, years and measures to this function and output it.

To maybe help you understand it better, to graph it, this is the format that needs to be passed to the xml file, which I'm writing on the fly with PHP.

<set label=$periodMonth . "/" . $periodYear .  "\" value=\"" . $measure . "\"/>";

 

Your help is greatly appreciated.

I'm really frustrated with the fact that I can't see something that's sounds really simple 

 

[silkfire]

Okay I see what the issue is; you first need to find out first month and the last month.

 

CODE:

list($month_start, $year_start, $month_end, $year_end) = mysql_fetch_row(mysql_query("SELECT (SELECT periodMonth, periodYear
                                                                                              FROM measureTemp 
                                                                                              WHERE practiceID = '$_SESSION[practiceID]'
                                                                                              AND questionRecID = 1
                                                                                              ORDER BY periodYear, periodMonth LIMIT 1), (SELECT periodMonth, periodYear
                                                                                                                                          FROM measureTemp 
                                                                                                                                          WHERE practiceID = '$_SESSION[practiceID]'
                                                                                                                                          AND questionRecID = 1
                                                                                                                                          ORDER BY periodYear DESC, periodMonth DESC
                                                                                                                                          LIMIT 1)"));

$measureArray = generate_month_range($month_start, $year_start, $month_end, $year_end);

$asthma5_40Query = 
"SELECT measure, periodYear, periodMonth FROM measureTemp 
WHERE practiceID = '" . $_SESSION['practiceID'] . "'
AND questionRecID =  1
ORDER BY periodYear, periodMonth";

$asthma5_40Res = mysql_query($asthma5_40Query);

while($row = mysql_fetch_array($asthma5_40Res)) {
$measureArray[$row['periodYear']][$row['periodMonth']] = $row['measure'];	
}

[xyph]

Loop through results.

$data = array();
while($row = mysql_fetch_array($asthma5_40Res)){
$data[$row['periodYear']][$row['periodMonth']] = $row['measure'];
}//end while

 

You end up with an array like this

$data = array(
2010 => array(
	5 => 164,
	6 => 31,
	7 => 20,
	8 => 10,
	9 => 10,
	10 => 10,
	12 => 10
),
2011 => array(
	1 => 10
)
);

 

All together

 

<?php

header('Content-Type: text/plain');

$data = array(
2010 => array(
	5 => 164,
	6 => 31,
	7 => 20,
	8 => 10,
	9 => 10,
	10 => 10,
	12 => 10
),
2011 => array(
	1 => 10
)
);

# Since our MySQL query was sorted, we know the array was created in chronological order.
# We can use this to grab end results without date.
# If you've messed with the internal pointer, you may want to reset()
$year_start = key($data);
$month_start = key($data[$year_start]);

end($data);
$year_end = key($data);
end($data[$year_end]);
$month_end = key($data[$year_end]);

# Loop through dates.
# $m represents the current month in the loop.
# $y represents the current year in the loop.
# loop continues as long as current year is less than the ending year
# OR current month is less than or equal to ending month
# increment month by 1 at each loop
echo "Month\tYear\tMeasure\n";
for( $m = $month_start, $y = $year_start; $y < $year_end || $m <= $month_end; $m++ ) {
# Echo month{tab}year{tab}
echo "$m\t$y\t";
# If a value exists that matches the current year/month, echo it
if( isset($data[$y][$m]) ) echo $data[$y][$m];
# else echo --
else echo "--";
# echo a linebreak
echo "\n";
# check if it's the 12th month
if( $m == 12 ) {
	# reset $m to 0, it will be increased to 1 at the start of the next loop
	$m = 0;
	# increase our year counter by 1
	$y++;
}
}

?>

 

Outputs:

 

Month	Year	Measure
5	2010	164
6	2010	31
7	2010	20
8	2010	10
9	2010	10
10	2010	10
11	2010	--
12	2010	10
1	2011	10

 

Hope that helps. Let me know if you have any questions.

 

[silkfire]

xyph he's not interested in the output he needs the array as a base for his chart, probably in JavaScript, as an object/array.

[Andy-H]

I assume you're using google chart data, why not just have the column set to string type, then if there's missing data have the integer (measure) field replicate the month before but have the month (column) data say "11 - No data"

 

 

So your data would look like this:

 

 

Month			Year		Measure
5			2010		164
6			2010		31
7			2010		20
8			2010		10
9			2010		10
10			2010		10
11 No data		2010		10
12			2010		10
1			2011		10

[xyph]

He's interested in the output.

 

It's not hard to change my snippet to output

<set label=\"$m . "/" . $y .  "\" value=\"" . $conditional . "\"/>";

 

Or to implement something like SimpleXML to work with it.

 

How do you send data to any sort of outside script without first outputting it?

 

I may not have given him a copypasta solution, but my solution is still valid, I believe.

[Barand]

Here's a clueto the whole process

<?php
include("testDBconnect.php");

/****
* get min and max year/month
*/
$sql = "SELECT periodYear, periodMonth
        FROM measure
        ORDER BY periodYear, periodMonth";
$res = mysql_query($sql);
$first = 1;
while (list($yr, $mth) = mysql_fetch_row($res)) {
    if ($first) {
        $minY = $yr;
        $minM = $mth;
    }
    $first = 0;
    $maxY = $yr;
    $maxM = $mth;
}

/****
* create the empty array
*/
$data = array();
for ($y=$minY; $y <= $maxY; $y++) {
    for ($m = 1; $m <= 12; $m++) {
        if ($y==$minY && $m < $minM) continue;
        if ($y==$maxY && $m > $maxM) break;
        $key = sprintf('%d/%02d', $y, $m); 
        $data[$key] = 0;
    }
}

/****
* populate the array values
*/
$sql = "SELECT periodYear, periodMonth,measure
        FROM measure";
$res = mysql_query($sql);
while (list($yr, $mth, $val) = mysql_fetch_row($res)) {
    $key = sprintf('%d/%02d', $yr, $mth);
    $data[$key] = $val;
}

/****
* write xml data
*/
foreach ($data as $key => $val) {
    echo "<set label=\"$key\" value=\"$val\">" . '<br />';  // I'm echoing but you will write to file.
}

?>

 

the final output from the above is

<set label="2010/05" value="164">
<set label="2010/06" value="31">
<set label="2010/07" value="20">
<set label="2010/08" value="10">
<set label="2010/09" value="10">
<set label="2010/10" value="10">
<set label="2010/11" value="0">
<set label="2010/12" value="10">
<set label="2011/01" value="10">

[cleibesouza]

Sorry for taking forever to reply but I had to devote my attention to an emergency issue at work.

Thank you for all of you who put your time into helping me with this challenge. Barand's solution worked like a charm for this scenario. There are other scenarios where measures are calculated based on several queries.

For instance:

Give me the percentage of people who have persistent asthma for the months we have data. So, same issue here. Some months we have data, some we don't, but we need to show an interrupted line on the chart for the months we don't have data.

This is just to tell you that there will probably be more coming if I can't adapt Barand's solution to this scenario ... 

But for now, I'll tag this thread as solved.

 

Thank you all again!!!!