Forum Moderators: coopster
ISSUE:
I am not sure how to:
1. ensure which month's data is missing, and
2. add a zero (0) for the missing month.
WHAT ELSE I HAVE TRIED:
I oculd do an array_pad up to 6 values. However, I would not know which month missed the data.
DATA DUMP:
A sample data dump is as follows:
[DATA]
Array ( [account] => --selec ) Array ( [time] => 04-02 ) Array ( [total] => 32 )
Array ( [account] => BIS-Pre ) Array ( [time] => 04-03 ) Array ( [total] => 16 )
Array ( [account] => BIS-Pre ) Array ( [time] => 04-04 ) Array ( [total] => 21 )
Array ( [account] => EOR ) Array ( [time] => 04-02 ) Array ( [total] => 12 )
Array ( [account] => IEG - I ) Array ( [time] => 04-02 ) Array ( [total] => 41 )
Array ( [account] => IEG - I ) Array ( [time] => 04-03 ) Array ( [total] => 42 )
Array ( [account] => IEG - I ) Array ( [time] => 04-04 ) Array ( [total] => 45 )
Array ( [account] => IEG - I ) Array ( [time] => 04-05 ) Array ( [total] => 18 )
Array ( [account] => UHCSTL ) Array ( [time] => 04-02 ) Array ( [total] => 12 )
[/DATA]
FURTHER EXPLANATION:
As you could see some of the categories may have 2 month or 1 month:
IDEALLY:
I would like each category if they have only value for Jan-o4, than for it to add
Feb-04 0
Mar-04 0
Apr-04 0
May-04 0
Jun-04 0
to the array
WHY I NEED THIS:
Accuracy is needed since these values will go in to a graph.
WHAT ELSE I HAVE LOOKED AT:
I have looked at combine, if then..., implode but I will be honest I am struggling
TIA
Tre
Yes it is coming from a MySQL DB, and I have just a SELECT priv on this DB.
I stumbled on to array_merge, but than again, struggling with , how actually to code it.
You gave me
$plot[$cat['account']][] = $valu['total'];
(THANK YOU)
I changed it to incorporate the times also:
plot[$cat['account']][$tim['time']][] =
$valu['total'];
However, I could use the merge and I am successful in my experimentation to insert the misisng month, but, the main issue is I just want the data value (of zero for the missing month) in the appropriate sequence. Month I do not need but doing it so the zero is placed for the correct month...
On the other hand, do you know how best I could do that via SQL?
TIA
// This array for merging could be built dynamically as well:
$template = array(
'04-02' => array('0'),
'04-03' => array('0'),
'04-04' => array('0'),
'04-05' => array('0'),
'04-06' => array('0'),
'04-07' => array('0')
);
foreach($plot as $account => $values) {
$plot[$account] = array_merge($template, $values);
}
If you don't mind, I am placing my code below just to confirm that what you showed me will work.
[CODE]
<?php
// We need some data NOTE: connect info not shown
$result = mysql_query ("SELECT trim(SUBSTRING(Account,1,7)) as account, count(RequestID) as total, date_format(Date_closed,'%m') as time
from rfsrequests
where Date_closed is not null
group by Account, time
having count(requestID) > 10 AND account <> ' '
order by account, time, total ");
$plot=array();
while ($row=mysql_fetch_assoc($result)) {
$tim['time'] = ($row['time']);
$cat['account'] = ($row['account']);
$valu['total'] =( $row['total']);
//*from webmaster forum (by: coopster),(not sure
//if time value needs to be placed here also)
$plot[$cat['account']][] = $valu['total'];
}
//From webmaster, (By coopster) proposed
//code here (?)
//[code here];
//for debug
//print_r($plot);
//echo "<br>";
//Assigning values for each bar graph ($g1 to $gn..)
//NOTE: Look for code to dynamically assign $g[] values
//Maybe phase II
$g1=$plot['--selec'];
// PRINT TO SEE RESULTS
print_r($g1);
?>
Here is my working code, but...when the value is plugged into JPGRAPH (OO class for PHP & graphing tool)
it tells me the resulting data is scalar and cannot be used for array (I need the data values in array)...this is my last request :)
[DATA]
array(6) {
["04-01"]=>
string(1) "0"
["04-02"]=>
string(2) "32"
["04-03"]=>
string(1) "0"
["04-04"]=>
string(1) "0"
["04-05"]=>
string(1) "0"
["04-06"]=>
string(1) "0"
}
[/DATA]
Here is my code:
<?php
include ("C:\jp\jpgraph\src\jpgraph.php");
include ("C:\jp\jpgraph\src\jpgraph_bar.php");
//turn on debugging
error_reporting (E_ALL);// We need some data
mysql_connect("localhost", "user", "pass");
mysql_select_db ("uhtreqmetrics");
$result = mysql_query ("SELECT trim(SUBSTRING(Account,1,7)) as account,
count(RequestID) as total, date_format(Date_closed,'%y-%m') as time
from rfsrequests
where Date_closed is not null
group by Account, time
having count(requestID) > 10 AND account <> ' '
order by account, time, total ");
//creating array
$plot=array();
// Bringing Data in:
while ($row=mysql_fetch_assoc($result)) {
$tim['time'] = ($row['time']);
$cat['account'] = ($row['account']);
$valu['total'] =( $row['total']);
//$plot[$cat['account']][$tim['time']][] = $valu['total'];
$plot[$cat['account']][$tim['time']] = $valu['total'];
}
// DEBUG POINT 1.
//echo var_dump($plot);
// This array for merging could be built dynamically as well:
// You can add another template for the 2nd part of the year.
$template = array
( '04-01' => '0',
'04-02' => '0',
'04-03' => '0',
'04-04' => '0',
'04-05' => '0',
'04-06' => '0' );
//Creating data rectangle for missing months
foreach($plot as $account => $values)
{ $plot[$account] = array_merge($template, $values); }
//DEBUG POINT 2
//echo "<br>";
//print_r($plot);
//echo "<br>";
// Assigning values per graph bar plot
$g1=$plot['--selec'];
//$g2=$plot['BIS-Pre'];
//$g3=$plot['EOR'];
//$g4=$plot['IEG - I'];
//$g5=$plot['UHCSTL'];
// DEBUG POINT 3.
echo var_dump($g1);
//echo "<br>";
//print_r($g1);
//echo "<br>";
//print_r($g1);
//echo "<br>";
//print_r($g1);
//echo "<br>";
//print_r($g1);
// Creating data series value for X-Axis
$seriesx=array('Jan','Feb', 'Mar', 'Apr', 'May', 'June');
// Create the graph.
$graph = new Graph(507,400,"auto");
$graph->SetScale('textint');
//***IMAGE:Method 1.
$graph->SetBackgroundImage("ingenixlogo.png",BGIMG_CENTER);
//*** Use 50% blending
$graph->ygrid->SetFill(true,'#EFEFEF@0.5','#BBCCFF@0.5');
$graph->ygrid->Show();
$graph->xgrid->Show();
//create your plots
// plot 1
$bar1 = new BarPlot($g1);
$bar1->SetFillColor('#8484a6');
//$bar1->value->Show();
$bar1->SetLegend("Not Selected");
//$abplot = new AccBarPlot(array($bar1));
$one=new BarPlot($bar1);
//$graph->Add($abplot);
$graph->Add($one);
// Set the legends for the plots
//$graph->legend->Pos(0.05,0.5,"left","top");
//$graph->legend->SetAbsPos(10,10,'right','top') ;
$graph->legend->SetColor('blue');
$graph->legend->SetColumns(2);
$graph->legend->SetFillColor('lightblue');
$graph->legend->SetFont(FF_FONT0,FS_NORMAL);
$graph->legend->SetFrameWeight(1);
$graph->legend->SetMarkAbsSize(6);
// Adjust the legend position
$graph->legend->SetLayout(LEGEND_HOR);
$graph->legend->Pos(0.6,0.95,"center","bottom");
// Adjust the margin
$graph->img->SetMargin(50,50,20,70);
// Set up the title & Subtitle for the graph
//$graph->title->SetMargin(22);
$graph->title->Set("RFS By Requestor");
$graph->title->SetFont(FF_FONT2,FS_BOLD,10);
$graph->title->SetColor("darkred");
// Set up the subtitle-6 mo
$graph->subtitle->Set("(Rolling 5 Months: Jan 2004- May 2004)");
$graph->subtitle->SetFont(FF_FONT1,FS_NORMAL,8);
$graph->subtitle->SetColor("maroon") ;
// Setup font for axis
$graph->xaxis->SetFont(FF_FONT0,FS_NORMAL,8);
$graph->yaxis->SetFont(FF_FONT0,FS_NORMAL,10);
// Show 0 label on Y-axis (default is not to show)
$graph->yscale->ticks->SupressZeroLabel(false);
// Setup X-axis labels
$graph->xaxis->SetTickLabels($seriesx);
$graph->Stroke();
?>
TIA
[edited by: jatar_k at 8:03 pm (utc) on May 19, 2004]
[edit reason] removed user/pass/url [/edit]
