RE: Newbie question - trying to graph MSColumn3D.swf from MySQL data

PlainDonut — Thu, 17 Jan 2008 14:25:58 GMT

OK, well I figured out how to do it dirty. I'm wondering if someone can help me clean this code up a bit?

[code]

<?php

header("Content-type: text/xml" );

$host = "localhost";

$user = "root";

$pass = "password" ;

$database = "database";

$linkID = mysql_connect($host, $user , $pass) or die("Could not connect to host.");

mysql_select_db ($database, $linkID) or die("Could not find database." );

$query = "SELECT accounts.name AS \"Client\", accounts_cstm.remaininglicenses_c AS \"RemainingLicenses\", accounts_cstm.usedlicenses_c AS \"UsedLicenses\", accounts_cstm.studentsenrolled_c AS \"StudentsEnrolled\"

FROM accounts_cstm INNER JOIN accounts ON accounts_cstm.id_c = accounts.id

WHERE (((accounts_cstm.remaininglicenses_c)>\"0\") AND ((accounts.deleted)=0));";

$resultID = mysql_query($query, $linkID) or die("Data not found.");

$xml_output = "<chart caption=\"License Usage Report\" subcaption=\"by Client\" palette=\"1\">";

$xml_output .= "\t<categories>";

for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){

$row = mysql_fetch_assoc($resultID);

$xml_output .= "\t\t<category label=\"" . $row['Client'] . "\" />";

}

$xml_output .= "\t</categories>";

//GET LICENSES REMAINING

FROM accounts_cstm INNER JOIN accounts ON accounts_cstm.id_c = accounts.id

WHERE (((accounts_cstm.remaininglicenses_c)>\"0\") AND ((accounts.deleted)=0));";

$resultID2 = mysql_query($query, $linkID) or die("Data not found.");

$xml_output .= "\t<dataset SeriesName=\"Licenses Remaining\">";

for($y = 0 ; $y < mysql_num_rows($resultID2) ; $y++){

$rowy = mysql_fetch_assoc($resultID2);

$xml_output .= "\t\t<set value=\"" . $rowy['RemainingLicenses'] . "\" />";

}

$xml_output .= "\t</dataset>";

//GET USED LICENSES

FROM accounts_cstm INNER JOIN accounts ON accounts_cstm.id_c = accounts.id

WHERE (((accounts_cstm.remaininglicenses_c)>\"0\") AND ((accounts.deleted)=0));";

$resultID2 = mysql_query($query, $linkID) or die("Data not found.");

$xml_output .= "\t<dataset SeriesName=\"Used Licenses\">";

for($y = 0 ; $y < mysql_num_rows($resultID2) ; $y++){

$rowy = mysql_fetch_assoc($resultID2);

$xml_output .= "\t\t<set value=\"" . $rowy['UsedLicenses'] . "\" />";

}

$xml_output .= "\t</dataset>";

//GET STUDENTS ENROLLED

FROM accounts_cstm INNER JOIN accounts ON accounts_cstm.id_c = accounts.id

WHERE (((accounts_cstm.remaininglicenses_c)>\"0\") AND ((accounts.deleted)=0));";

$resultID2 = mysql_query($query, $linkID) or die("Data not found.");

$xml_output .= "\t<dataset SeriesName=\"Students Enrolled\">";

for($y = 0 ; $y < mysql_num_rows($resultID2) ; $y++){

$rowy = mysql_fetch_assoc($resultID2);

$xml_output .= "\t\t<set value=\"" . $rowy['StudentsEnrolled'] . "\" />";

}

$xml_output .= "\t</dataset>";

$xml_output .= "\t</chart>";

echo $xml_output;

[/code]

Newbie question - trying to graph MSColumn3D.swf from MySQL data

PlainDonut — Thu, 17 Jan 2008 01:23:40 GMT

Boy have I stayed up late trying to figure this one out. I hope someone can help me because I was getting meaner by the hour for anyone who approached me. Here's what I'm trying to do...

I have a database query of clients, remaining licenses, used licenses and enrolled. When I try to format the XML data I can't seem to group them correctly. I believe it is because of my lack of knowledge on php mysql arrays. I'm a quick learner though. Here is what the correct XML output should look like:

[code]

</categories>

</dataset>

</dataset>

</dataset>

</chart>

[/code]

but instead my code cycles through all the rows instead of columns so I get this (slightly abbreviated to avoid redundancy) -

[code]

<?xml version="1.0" ?>

<client>Client1</client>

</categories>

<client>Client2</client>

</categories>

<client>Client3</client>

</categories>

</entries>

[/code]

This is the php code that I'm using to generate XML output -

[code]

<?php

header("Content-type: text/xml" );

$host = "localhost";

$user = "root";

$pass = "password" ;

$database = "database";

$linkID = mysql_connect($host, $user , $pass) or die("Could not connect to host.");

mysql_select_db ($database, $linkID) or die("Could not find database." );

FROM accounts_cstm INNER JOIN accounts ON accounts_cstm.id_c = accounts.id

WHERE (((accounts_cstm.remaininglicenses_c)>\"0\") AND ((accounts.deleted)=0));";

$resultID = mysql_query($query, $linkID) or die("Data not found.");

$xml_output = "<?xml version=\"1.0\"?>";

$xml_output .= "<entries>";

for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){

$row = mysql_fetch_assoc($resultID);

$xml_output .= "\t<categories>";

$xml_output .= "\t\t<client>" . $row['Client'] . "</client>";

$xml_output .= "\t</categories>";

}

$xml_output .= "</entries>";

echo $xml_output;

[/code]

I feel like I'm so close to figuring this out. I believe I just need help on the array portion and I can make the rest work. I did try to use the documentation but I'm thrown for a loop when it uses two databases, a SUM function with a sub query and a totally different chart (pie chart as opposed to this 3D column chart I'm trying to work with here.

In general, I'm trying to query two tables and spit out 4 pieces of data into a 3D column chart.

Thanks in advance for any help!

FusionCharts Forum / FusionCharts v3 / Using FusionCharts / FusionCharts and PHP / Newbie question - trying to graph MSColumn3D.swf from MySQL data / Latest Posts

RE: Newbie question - trying to graph MSColumn3D.swf from MySQL data

Newbie question - trying to graph MSColumn3D.swf from MySQL data