PHP/SWF Charts 1

[BiJae]

I am developing a program that incorporates a graph into the application. I have been reading several threads on this and used this thread

http://www.tek-tips.com/viewthread.cfm?qid=1395840

to choose PHP/SWF charts. I really like the flexibility of the application. I can change the chart types and add special effects. I think this gives the application the flare I'm looking for.

So I read the tutorial and the rest of the documentation. I built a test application using their sample code. Everything worked perfectly. Then it was time to apply the code to my application. I already have a working application that I am adding this to. I took the data that I was getting and compiled it into the format and array that the PHP/SWF application uses. When I did this I got the default graph.

I turned to the application developer for assistance and got this message”

For all inquiries, please e-mail us at info@maani.us.
Technical support is available for registered users only. To receive a reply, please e-mail us from an address with the same domain name the tool was registered for, and include your registration code in the e-mail.

I'm definitely interested in registering this application and buying a license for it. However, I want to make sure it's going to work first. They are not offering any initial support. So I'm turning to you all to see if you've seen something similar. I am not going to be paying just to get an e-mail answered.

To trouble shoot it further I took the dataset that my database was returning and manually put it in the chart application:

$chart ['chart_data'][0]= array("", "12-08","12-09", "12-10", "12-11", "12-12");

$chart ['chart_data'][1] = array("Events",18,9,19,24,26);

$chart ['chart_data'][2] = array("Dispatches",3,0,3,3,7);

This worked. I got my graph and was able to change the format to the various options available. I then tried to dynamically load the data from the database:

$Date = array();

$Event = array();

$Disp = array();



$Date[0] = "";

$Event[0] = "Events";

$Disp[0] = "Dispatched";

$x=0;

foreach($graphdata as $k=>$v) {

	$x++;

	$Date[$x] = substr($v[0],5);

	$Event[$x] = $v[1];

	$Disp[$x] = $v[2];

	}

	

$chart['chart_data'][0] = $Date;

$chart['chart_data'][1] = $Event;

$chart['chart_data'][2] = $Disp;

This code gives me the default chart, which according to

http://www.maani.us/charts

means that the application does not recognize the array that is being passed. Then I followed their advice and passed the page to the browser to make sure that it did not generate any errors. Sure enough, no errors.

I removed the generate chart code and bounced out the information to the browser. Both the static and dynamically generated array's returned these values:

stacked column 12-08 12-09 12-10 12-11 12-12 Events 18 9 19 24 26 Dispatched 3 0 3 3 7

Stumped I again generated the report. I looked at the output and received the same HTML source for the manually compiled array and the dynamically compiled array:

<chart>
<chart_type>stacked column</chart_type>
<axis_category orientation="diagonal_up" />
<chart_transition type="slide_up" delay="1" duration="2" order="series" />
<chart_data>
<row>
<string></string>
<string>12-08</string>
<string>12-09</string>
<string>12-10</string>
<string>12-11</string>
<string>12-12</string>
</row>
<row>
<string>Events</string>
<number>18</number>
<number>9</number>
<number>19</number>
<number>24</number>
<number>26</number>
</row>
<row>
<string>Dispatched</string>
<number>3</number>
<number>0</number>
<number>3</number>
<number>3</number>
<number>7</number>
</row>
</chart_data>
</chart>

<chart>
<chart_type>stacked column</chart_type>
<axis_category orientation="diagonal_up" />
<chart_transition type="slide_up" delay="1" duration="2" order="series" />
<chart_data>
<row>
<string></string>
<string>12-08</string>
<string>12-09</string>
<string>12-10</string>
<string>12-11</string>
<string>12-12</string>
</row>
<row>
<string>Events</string>
<number>18</number>
<number>9</number>
<number>19</number>
<number>24</number>
<number>26</number>
</row>
<row>
<string>Dispatches</string>
<number>3</number>
<number>0</number>
<number>3</number>
<number>3</number>
<number>7</number>
</row>
</chart_data>
</chart>

I'm really stumped by this application. I believe I'm loading the array correctly. From what I'm seeing the array is being loaded and parsed properly. I would love to get this application working in a test environment so I can buy this application, however I'm just about to give up on this application and find another solution, free or not.

Any assistance is greatly appreciated.

Thank you

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[jpadie]

bijae

i have taken your array and pasted it into a sample app. it works fine on my system.

this is my code:

<?php
require 'charts.php';
$chart ['chart_data'][0]= array("", "12-08","12-09", "12-10", "12-11", "12-12");
$chart ['chart_data'][1] = array("Events",18,9,19,24,26);
$chart ['chart_data'][2] = array("Dispatches",3,0,3,3,7);
SendChartData ( $chart );
?>

<?php
require 'charts.php';
echo InsertChart ( "charts.swf", "charts_library", "c.php", 400, 250 );
?>

it also works find with different chart_type (s). I have tried 'bar';

 

[BiJae]

Thanks, jpadie!

I can get it to work fine when I manually load the array(copy and paste). The problem is when I load the array dynamically with the data from the database as in:

$Date = array();
$Event = array();
$Disp = array();

$Date[0] = "";
$Event[0] = "Events";
$Disp[0] = "Dispatched";

$x=0;
foreach($graphdata as $k=>$v) {
    $x++;
    $Date[$x] = substr($v[0],5);
    $Event[$x] = $v[1];
    $Disp[$x] = $v[2];
    }

$chart['chart_data'][0] = $Date;
$chart['chart_data'][1] = $Event;
$chart['chart_data'][2] = $Disp;

When I use the data returned from the database in $graphdata the program generates the default graph. Although when I look through the array or post out the data to the browser they look identical.

Any ideas?

Thank you,

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[jpadie]

can you post the sql string?

 

[BiJae]

Sure thing:

select
  strdate = a.strdate
, evtcnt = sum(a.evtcnt)
, dspcnt = sum(a.dspcnt)
from (
	select
	  strdate = convert(varchar(10), Date_Time, 120)
	, evtcnt = count(*)
	, dspcnt = 0
	from $eventdb.$eventowner.Event_Log
	where Date_Time between @dtalpha and @dtomega
	group by convert(varchar(10), Date_Time, 120)
	union all
	select
	  strdate = initdisp.strdate
	, evtcnt = 0
	, dspcnt = count(initdisp.dspcnt)
	from (
		select
		  strdate = convert(varchar(10), dsp.Time_Sent, 120)
		, dspcnt = count(*)
		, dsp.EventLogID
		from
		  $eventdb.$eventowner.Event_Log evt
		, $eventdb.$eventowner.Dispatch dsp
		where
		    dsp.Time_Sent between @dtalpha and @dtomega
		and evt.EventLogID = dsp.EventLogID
		and convert(varchar(10), evt.Date_Time, 120)
			= convert(varchar(10), dsp.Time_Sent, 120)
		group by
		  convert(varchar(10), dsp.Time_Sent, 120)
		, dsp.EventLogID
	) initdisp
	group by initdisp.strdate
) a
group by a.strdate
order by a.strdate asc

This is the SQL statement that is defined in the code that I inherited. I can see that he's using the foreach statement in other places in his code to get the values out of the array. So I employed that to load the arrays for the chart program. When I look at the arrays they look the same.

I'm thinking that the array may not be loading with "'s around the items. However, when I manually load without "'s it works...

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[jpadie]

hi

based on your sql i mocked up the following code to create a db, insert some dummy vals and then perform a normal recordset interaction to build the array.

<?php
error_reporting(E_ALL);
ini_set('display_errors', 'on');
require 'charts.php';
//******************************************************
//DUMMYING THE DATABASE
$pdo = new PDO ('sqlite::memory:');
if (!$pdo) {print_r($pdo->errorInfo());}
$query = 'create table info (strdate, evtcnt, dspcnt)';
$insert = 'insert into  info values (?,?,?)';

$vals = array(	array("12-08", 18,3), 
				array("12-09",9,0), 
				array ("12-10",19,3), 
				array ("12-11",24,3), 
				array ("12-12",26,7));

$pdo->exec($query);
$s = $pdo->prepare($insert);
foreach ($vals as $val){
	$s->execute($val);
}
$rQuery = 'Select strdate,evtcnt,dspcnt from info';

//END DATABASE DUMMYING
//****************************************************

//assume that the query has already been performed

//instantiate variables
$Date = array('');
$Event = array('Events');
$Disp = array('Dispatches');

/*
FOR MYSQL CONNECTIONS CHANGE THIS NEXT LINE TO THE FOLLOWING TWO LINES
mysql_data_seek($result, 0); //move the pointer back to the start of the recordset
while ($row = mysql_fetch_assoc($result)){
*/

foreach($pdo->query($rQuery) as $row){
	extract ($row);
	//variables are 
	/* 
		$strdate
		$evtcnt
		$dspcnt */
	$Date[] = $strdate;
	$Event[] = $evtcnt;
	$Disp[] = $dspcnt;
}

//now turn it into a chart data

$chart['chart_type'] = 'bar';
$chart['chart_data'] = array( $Date, $Event, $Disp );
SendChartData ( $chart );
?>

 

[BiJae]

Thank you, jpadie..

I think I'm understanding your code and it looks very much like how I would code it if I had built the original application. Unfortunately I've inherited this one and have to work with the house of cards that is already standing. That being said I want to make sure I'm understanding your statement:

foreach($pdo->query($rQuery) as $row)

As I read this I'm making the assumption that $pdo->query($rQuery) is the result set from Select strdate,evtcnt,dspcnt from info and should be something like (as put out from a command line):
2007-12-09, 9, 0
2007-12-10, 19, 3
2007-12-11, 24, 3
2007-12-13, 26, 7

So the result set is an array that your stepping through with the foreach as row command.

That part I have working. It bounces out the correct information in the html source.. but I'm still not getting the chart.

I guess I have to come to a point and a question here...
Does

foreach($pdo->query($rQuery) as $row)

equal

foreach($graphdata as $graphdata)

This question hits me as stupid as soon as I typed it, but I have to ask because I can see my data in the array correctly or I think correctly based on the HTML Source. So I know I’m getting the data out of $graphdata in the order and the mannor which I expect it. The problem is, when I hand off that array to the charting program I get nothing.

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[jpadie]

i provided equivalent coding for a mysql database. could you try using this and seeing whether you still have the same problem. you don't need to change the legacy coding in the db call - just paste my code (other than the db dummying) at the appropriate point.

if the arrays are identical between the hard coded and dynamic versions then there must be some other difference causing an issue. however before evaluating this, it is worth ensuring that the easier stuff is correct.

 

[BiJae]

jpadie, thank you for the suggestion.

I'm trying to best implement your code without introducing too many new variables. My years of troubleshooting always make me very cautious about entering new variables into a problem. I've destroyed quite a bit that way.

I tried to use the code that you provided but the installation that I have does not have PDO support configured. I tried merging your code with my $graphdata object:

$Date = array('');

$Event = array('Events');

$Disp = array('Dispatches');
foreach($graphdata as $row) {
	extract($row);

   //variables are
    /*
        $strdate
        $evtcnt
        $dspcnt */

$Date[] = $strdate;
$Event[] = $evtcnt;
$Disp[] = $dspcnt;
	}

	

//now turn it into a chart data



$chart['chart_type'] = 'bar';

$chart['chart_data'] = array( $Date, $Event, $Disp );

SendChartData ( $chart );

Which if I'm understanding your post correctly is what you were asking me to do by pasting your code other than the db dummying. I got no results returned from that setup. Which lead me to my previous post. I am trying to understand what your line foreach($pdo->query($rQuery) as $row) is doing. My interpretation of the documentation

http://us.php.net/manual/en/function.PDO-query.php

tells me that it's returning an object very similar to $graphdata.

The more I worked with the statements the more I got no results. So I backtracked again.

Next started looking at the php tools to compare and contrast arrays. I changed my code to have $chartA be the manually loaded array and $chartB to be the dynamically loaded array. I used the code

$whatgives = array_diff($chartA, $chartB);
print_r($whatgives);

and 

$whatgives = array_intersect($chartA, $chartB);
print_r($whatgives);

to see exactly how the two arrays compared withone another. Below are the resutls.

Array( )

Array ( [chart_data] => Array (
[0] => Array ( [0] => [1] => 12-08 [2] => 12-09 [3] => 12-10 [4] => 12-11 [5] => 12-12 )
[1] => Array ( [0] => Events [1] => 18 [2] => 9 [3] => 19 [4] => 24 [5] => 26 )
[2] => Array ( [0] => Dispatches [1] => 3 [2] => 0 [3] => 3 [4] => 3 [5] => 7 )
)
)

So if I'm understanding your last post correctly. You and I both want to make sure that the arrays are infact loading with the exact same data and are presented the same by PHP. I believe this test shows that we are getting the same results with $chartA which is manually loaded, and $chartB which is loaded dynamically with database results.

Am I understanding our objectives?

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[BiJae]

Let me ask this:

When loading an Array dynmically through
$var[] = $objvar;

Does PHP include any wrappers arround the variable. From all the tests we've done the only difference I can see is in the manually coded array text and other items are surrounded by " "? I'm curious if the chart program is stumbling on another separator in the array.

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[jpadie]

the foreach code is just the way that you iterate a recordset using PDO. it's counterintuitive which is why i also posted the mysql alternative. they are direct replacements.

i can't comment on the $graphdata array as you have not posted how that is built. can you post the code?

 

[BiJae]

Absolutely! I appreciate your continued help on this...

$graphdata = array();
$kalpha = array_search($alpha, $drange);
$komega = array_search($omega, $drange);

foreach ( array_slice($drange, $kalpha, ($komega - $kalpha + 1)) as $ak => $av )
	$graphdata[] = array($av, 0, 0);
$result = mssql_query(sprintf(QDAILYEVTDSP, $alpha, $omega), $conn)
	or exit('unable to run the daily evtdsp query');
while ( FALSE !== ($r = mssql_fetch_row($result)) ) $dailyevtdsp[] = $r;

	$i = $ymax = 0;
foreach ( $graphdata as $gk => $gv ) {
	for ( $x = $i; $x < count($dailyevtdsp); $x++ ) {
		if ( $graphdata[$gk][0] == $dailyevtdsp[$x][0] ) {
			$graphdata[$gk][1] = $dailyevtdsp[$x][1];
			$graphdata[$gk][2] = $dailyevtdsp[$x][2];
			$i++;
		break;
	}
		}
	$rowmax = max($graphdata[$gk][1], $graphdata[$gk][2]);
$ymax = ( $rowmax > $ymax ? $rowmax : $ymax );
	}

I was thinking they were pretty synonymous because I could get the values I expected out of it by pulling from that array.

My concern is mostly with the way that the Chart arrays are built. If the php/swf is looking for each data element to be encompassed by a ' or " and a , and PHP loads each element of $var[] with only a , this could cause problems. Or at least that's my thinking...

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[jpadie]

i can't easily derive the shape of the array.

can you dump it and post the result?

echo "<pre>" . print_r($graphdata, true) . '</pre>';

thanks

 

[BiJae]

My pleasure:

Array
(
[0] => Array
(
[0] => 2007-12-08
[1] => 18
[2] => 3
)

[1] => Array
(
[0] => 2007-12-09
[1] => 9
[2] => 0
)

[2] => Array
(
[0] => 2007-12-10
[1] => 19
[2] => 3
)

[3] => Array
(
[0] => 2007-12-11
[1] => 24
[2] => 3
)

[4] => Array
(
[0] => 2007-12-12
[1] => 26
[2] => 7
)

)

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[jpadie]

ok.

so against this array i'd expect the code to be

$Date = array('');
$Event = array('Events');
$Disp = array('Dispatches');

foreach($graphdata as $row){
    $Date[] = $row[0]; //let's ignore the date format
    $Event[] = $row[1];
    $Disp[] = $row[2];
}

$chart['chart_type'] = 'bar';
$chart['chart_data'] = array( $Date, $Event, $Disp );
SendChartData ( $chart );

the difference to your code is that the way that you ahve built graphdata does not seem to make use of an associative array, hence we have to slum it with a numeric array.

also this

foreach($graphdata as $graphdata)

won't work as you will be overwriting the array at each iteration. (as you are reusing the $graphdata variable)

assuming all else is equal (i.e. the array output is identical), remember that the code that outputs the data to the swf file (i.e. the c.php in my example) must not output anything else whatsoever to the browser. no spaces, no extraneous lines etc. if things still don't work for this chart then try these potential solutions:

1. extract the sql and the array generator (only) to another file and use that file as the argument for the insertchart() function. yes, it's another sql query essentially doing the same thing but if it works then it points the finger at something in the other file.

2. add this at the beginning of the offending file

ob_start();

and then add this on the last line of the file

ob_end_clean(); exit();

. this should capture any inadvertent browser output.

if either of these methods work then we are down to something wrong in the script that generates the data for the graph (as opposed to the data generation itself). if you can't spot what's wrong by all means post the whole script.

 

[BiJae]

Thanks jpadie.

I've tried your suggestions. I moved the DB connection information. I moved the query definitions. I've also moved the $graphdata build. These now all reside in my test.php file which I call to for building the chart. Here is that file:

<?php

ob_start();

$strdbalias = '192.168.83.130';

$strdbuid   = 'status_user';

$strdbpwd   = 'status_user';

$namedb     = 'openview';

$nameowner  = 'ovdb';

$eventdb    = 'EVENTDB';

$eventowner = 'dbo';

define('SQLHEADER', "

SET ANSI_DEFAULTS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON
DECLARE @alpha varchar(10)
DECLARE @omega varchar(10)
DECLARE @dtalpha varchar(23)
DECLARE @dtomega varchar(23)
SET @alpha = '%s'
SET @omega = '%s'

SET @dtalpha = @alpha + ' 00:00:00.000'
SET @dtomega = @omega + ' 23:59:59.997'"

);

define('QMINMAX', "

select

  mindate = convert(varchar(10), min(Date_Time), 120)

, maxdate = convert(varchar(10), max(Date_Time), 120)

from $eventdb.$eventowner.Event_Log

");

define('QDAILYEVTDSP', SQLHEADER . "
select

  strdate = a.strdate

, evtcnt = sum(a.evtcnt)

, dspcnt = sum(a.dspcnt)

from (

	select

	  strdate = convert(varchar(10), Date_Time, 120)

	, evtcnt = count(*)

	, dspcnt = 0

	from $eventdb.$eventowner.Event_Log

	where Date_Time between @dtalpha and @dtomega

	group by convert(varchar(10), Date_Time, 120)

	union all

	select

	  strdate = initdisp.strdate

	, evtcnt = 0

	, dspcnt = count(initdisp.dspcnt)

	from (

		select

		  strdate = convert(varchar(10), dsp.Time_Sent, 120)

		, dspcnt = count(*)

		, dsp.EventLogID

		from

		  $eventdb.$eventowner.Event_Log evt

		, $eventdb.$eventowner.Dispatch dsp

		where

		    dsp.Time_Sent between @dtalpha and @dtomega

		and evt.EventLogID = dsp.EventLogID

		and convert(varchar(10), evt.Date_Time, 120)

			= convert(varchar(10), dsp.Time_Sent, 120)

		group by

		  convert(varchar(10), dsp.Time_Sent, 120)

		, dsp.EventLogID

	) initdisp

	group by initdisp.strdate

) a

group by a.strdate

order by a.strdate asc

");

$alpha = &$_GET['alpha'];

$omega = &$_GET['omega'];


$conn =	mssql_connect($strdbalias, $strdbuid, $strdbpwd)

	or exit('unable to connect to the database');



$result = mssql_query(QMINMAX, $conn)

	or exit('unable to run the min/max query');

list($mindate, $maxdate) = mssql_fetch_row($result)

	or exit('unable to get min/max dates');

$drange = array();

$drange[] = $mindate;

$i1 = strtotime($mindate);

$i2 = strtotime($maxdate);

while ( $i1 < $i2 ) { $i1 += 86400; $drange[] = date('Y-m-d', $i1); }

$srange = $drange;

rsort($srange);


$graphdata = array();

	$kalpha = array_search($alpha, $drange);

	$komega = array_search($omega, $drange);

	foreach ( array_slice($drange, $kalpha, ($komega - $kalpha + 1)) as $ak => $av )

		$graphdata[] = array($av, 0, 0);



	$result = mssql_query(sprintf(QDAILYEVTDSP, $alpha, $omega), $conn)

		or exit('unable to run the daily evtdsp query');

	while ( FALSE !== ($r = mssql_fetch_row($result)) ) $dailyevtdsp[] = $r;



	$i = $ymax = 0;

	foreach ( $graphdata as $gk => $gv ) {

		for ( $x = $i; $x < count($dailyevtdsp); $x++ ) {

			if ( $graphdata[$gk][0] == $dailyevtdsp[$x][0] ) {

				$graphdata[$gk][1] = $dailyevtdsp[$x][1];

				$graphdata[$gk][2] = $dailyevtdsp[$x][2];

				$i++;

				break;

			}

		}

		$rowmax = max($graphdata[$gk][1], $graphdata[$gk][2]);

		$ymax = ( $rowmax > $ymax ? $rowmax : $ymax );

	}



//include charts.php in your script
error_reporting(E_ALL);
ini_set('display_errors','on');

require "charts.php";





$Date = array('');

$Event = array('Events');

$Disp = array('Dispatches');



foreach($graphdata as $row) {

    $Event[] = $row[1];

    $Disp[] = $row[2];

	}

	

//now turn it into a chart data



$chartB['chart_type'] = 'bar';

$chartB['chart_data'] = array( $Date, $Event, $Disp );

SendChartData ( $chartB);
ob_end_clean(); exit();


?>

I get the same results when I run the script... default graph, but the array appears loaded.

Note: when I add the ob_start_clean() and ob_end_clean() the chart fails to load with the error:

Loading <test.php> timed out(-1)

When I remove the ob functions the default graph loads without error but also without data.

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[jpadie]

can you output you grphdata array again. perhaps as a serialized string so i can reuse it.

echo serialize($graphdata);

 

[BiJae]

Sure:

a:5:{i:0;a:3:{i:0;s:10:"2007-12-08";i:1;i:18;i:2;i:3;}i:1;a:3:{i:0;s:10:"2007-12-09";i:1;i:9;i:2;i:0;}i:2;a:3:{i:0;s:10:"2007-12-10";i:1;i:19;i:2;i:3;}i:3;a:3:{i:0;s:10:"2007-12-11";i:1;i:24;i:2;i:3;}i:4;a:3:{i:0;s:10:"2007-12-12";i:1;i:26;i:2;i:7;}}

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[jpadie]

does this work for you?

<?php
$graphdata = unserialize('a:5:{i:0;a:3:{i:0;s:10:"2007-12-08";i:1;i:18;i:2;i:3;}i:1;a:3:{i:0;s:10:"2007-12-09";i:1;i:9;i:2;i:0;}i:2;a:3:{i:0;s:10:"2007-12-10";i:1;i:19;i:2;i:3;}i:3;a:3:{i:0;s:10:"2007-12-11";i:1;i:24;i:2;i:3;}i:4;a:3:{i:0;s:10:"2007-12-12";i:1;i:26;i:2;i:7;}}');

$Date = array('');
$Event = array('Events');
$Disp = array('Dispatches');

foreach($graphdata as $row) {
	$Date = $row[0];
	$Event[] = $row[1];
	$Disp[] = $row[2];
}
$chartB['chart_type'] = 'bar';
$chartB['chart_data'] = array( $Date, $Event, $Disp );
SendChartData ( $chartB);
?>

 

[BiJae]

No, I still get the default menu.

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart