[ask] data redudant while plotting using jpgraph

coolbear91

hi guys,
i had some error code below which is used to plot data from SQL query result into a graph:

require_once('as-diagrams.php');
require_once ('lib/jpgraph/jpgraph.php');
require_once ('lib/jpgraph/jpgraph_line.php');
require_once( "lib/jpgraph/jpgraph_date.php");


$datax = array();
$datay = array();


//let's say this is the range date as input
$date1 = "2011-05-01";
$date2 = "2011-05-08";

$con =	mssql_connect("EV0021919ECCC0","username", "password") or die("unnable to connect");
mssql_select_db("database");
set_time_limit (1000);

$rs = mssql_query("SELECT distinct day, maxload
FROM         dbo.BO_BUSYHOUR_CPLOAD2
WHERE     (ne = 'MSJK5') AND ([day]>='$date1' AND [day]<='$date2' ) ORDER BY [day] ");

//day should return 2011-05-01, 2011-05-02, ..., 2011-05-08 as result
//maxload should return values 56.01, 53.52, 54.54, 53.16, etc. (appear in eight times, following the dates)


while($result = mssql_fetch_array($rs))
{
	$datay[] = $result['maxload'];
	$datax[] = strtotime($result['day']);
}



// Size of the overall graph
$width=650;
$height=500;

// Create the graph and set a scale.
// These two calls are always required
$graph = new Graph($width,$height);
$graph->SetScale('datlin');
$graph->xaxis->SetLabelAngle(90);
$graph->SetShadow();

// Setup margin and titles
$graph->SetMargin(40,40,30,100); 
$graph->SetAlphaBlending();
$graph->title->Set('Jakarta Inner Busy Hour CP Load (%)');
$graph->yaxis->title->Set('CP Load (%)');

$graph->yaxis->title->SetFont( FF_FONT1 , FS_BOLD );
$graph->xaxis->title->SetFont( FF_FONT1 , FS_BOLD );

$graph->yaxis->SetColor('cyan');

// Use hour:minute format for the labels
$graph->xaxis->scale->SetDateFormat('Y-m-d');

/*$a = $gDateLocale->GetShortMonth();
$graph->xaxis->SetTickLabels($a);*/

// Create the linear plot
$lineplot = new LinePlot($datay,$datax);
$lineplot->SetColor( 'blue' );
$lineplot->SetWeight( 2 );   // Two pixel wide
$lineplot->mark->SetType(MARK_UTRIANGLE);
$lineplot->mark->SetColor('blue');
$lineplot->mark->SetFillColor('red');

// Add the plot to the graph
$graph->Add($lineplot);

// Display the graph
$graph->Stroke();

the result shows in the attached file below, or u can just view from this link:
http://www.phpbuilder.com/board/attachment.php?attachmentid=4298&stc=1&d=1305792120

as u can see..
the dates are getting doubled on each day..

is there any mistakes in my code so that the result threw that way??
or should i have to add more line of codes to prevent this output??

thx before

bradgrafelman

The 'SELECT DISTINCT' applies to all tuples in the result set, so ('2011-05-02', 1) and ('2011-05-02', 2) are two distinct tuples (thus both would be returned).

In other words: get rid of the DISTINCT and instead use a GROUP BY statement to group on the date column. You'll also want to order the maxload columns in DESCending order (so that the 'maxload' value returned is the max of the maxes :p).

coolbear91

The 'SELECT DISTINCT' applies to all tuples in the result set, so ('2011-05-02', 1) and ('2011-05-02', 2) are two distinct tuples (thus both would be returned).

In other words: get rid of the DISTINCT and instead use a GROUP BY statement to group on the date column. You'll also want to order the maxload columns in DESCending order (so that the 'maxload' value returned is the max of the maxes ).

well..
fyi, the "maxload"s in this graph are actually the max of the maxes on each days.
it's no problem to sort them descendingly but I'm affraid it doesn't meet the user's want.
they just need to show the maxloads with dates sorted ascendingly.

if i get off the distinct and added group by instead,
it must be worked after i added some formula on either "day" or "maxload", isn't it??
😕

like:
SELECT formula1 as day, formula2 as maxload
FROM dbo.BO_BUSYHOUR_CPLOAD2
WHERE (ne = 'MSJK5') AND ([day]>='$date1' AND [day]<='$date2' ) group BY [day], [maxload]

if yes,
it will then return the result which doesn't meet the user's need.

CMIIW

johanafm

bradgrafelman;10980492 wrote:
You'll also want to order the maxload columns in DESCending order (so that the 'maxload' value returned is the max of the maxes :p).

Since he's not using MySql, I don't believe that will work. And even if it does, I recommend doing it the way the SQL standard specs it.

When a group by clause is present, every field has to be part of the group by clause or be part of an aggregate function.
In other words, there's no need to sort by maxload, just use MAX() instead

SELECT day, MAX(maxload)
...
GROUP BY day

coolbear91;10980493 wrote:
well..
fyi, the "maxload"s in this graph are actually the max of the maxes on each days.

But, if maxload is the max load for every day, how can there be two such values on any given day?

coolbear91

johanafm;10980494 wrote:
Since he's not using MySql, I don't believe that will work. And even if it does, I recommend doing it the way the SQL standard specs it.

When a group by clause is present, every field has to be part of the group by clause or be part of an aggregate function.
In other words, there's no need to sort by maxload, just use MAX() instead
SELECT day, MAX(maxload)
...
GROUP BY day
But, if maxload is the max load for every day, how can there be two such values on any given day?

ok, here it is.
first, we have hourly maxload data.
let's say, in 2011-05-01 we have:
COLOR="Blue" - (maxload)
00:00 - 50
00:01 - 53
00:02 - 54[/COLOR]
and so on..

then, there's some view created to find the maximum value of those hourly maxloads.
so, on each days we have max(maxload) threw from our hourly data.
then the result comes into:
COLOR="Blue" - (maxload)
2011-05-01 - 60
2011-05-02 - 55
and so on..[/COLOR]

all i need to do is just to display them sorted by date ascendingly,
but the dates were displayed twice.
i'm not pretty sure but i guessed the problem raised from the graph's X scale or something.
(u can see it on the attached image)