Generate dynamic graphs from data in mysql db using php and excel or j

mamasita60440 · Aug 16, 2006

I need to display selected data from a mysql database in basic line graphs using php. I am open to using excel but thought I saw something about php having a tool called jgraphs.

To give you a little background info about my application:
1. Users can search the databases by date. They enter a date and press enter.
2. The data is then displayed on a new page based on the date entered.
3. The user can then select which data they want displayed in the line graphs with checkboxes. I am using session variables on this page of code.
4. After the user has checked the rows of data they wish to have displayed in their line graphs they should be able to press the submit button to generate the graphs.

Below is the code for the page where they use the checkboxes to select their data:

<?php session_start();

$_SESSION["ReportData"]=$_POST["ReportData"];
$_SESSION["HandleName"]=$_POST["HandleName"];
$_SESSION["ServerName"]=$_POST["ServerName"];
$_SESSION["LoadName"]=$_POST["LoadName"];
$_SESSION["InstallDate"]=$_POST["InstallDate"];

?>

<form action="insertPfrGraph.xls" method= "post" name="FormName">

<html>
<body>
<table>

<table border='1'>
<tr><th>Handle</th><th>Load</th><th>Server</th><th>Installation Date</th>

</tr>
<?php

foreach ($_POST['ReportData'] as $cbox=>$cboxvalue) {

$HandleName= $_POST["HandleName"][$cbox];
$LoadName= $_POST["LoadName"][$cbox];
$ServerName= $_POST["ServerName"][$cbox];
$InstallDate= $_POST["InstallDate"][$cbox];

echo "<tr><td>".$HandleName."</td>
<td>".$LoadName."</td>
<td>".$ServerName."</td>
<td>".$InstallDate."</td>

</tr>";

}

?>

</table>

<br><br><input type="Submit" value="Display Graphs">
</form>
</body>
</html>

Any help is greatly appreciated. I am not sure how to tackle this. Thanks.

sleipnir214 · Aug 16, 2006

First, play around with JpGraph (

http://www.aditus.nu/jpgraph/).

It should be able to generate the graphs you want, but keep in mind that JpGraph is a PHP class library that requires PHP version 4.3 or newer and that it makes use of PHP's image functions.

Want the best answers? Ask the best questions! TANSTAAFL!

mamasita60440 · Aug 22, 2006

Instead of generating dynamic graphs I have decided to export the data to an excel spreadsheet.
The catch is that I only want the data that has been selected using checkboxes on a previous page to be exported to the spreadsheet.

With the code I am using I can generate the excel sheet but the data is not downloading, I get "0 records found" on the spreadsheet when I open it.

Here is my code. If you are familiar with this please take a look and let me know what you think. Thank you.

<?php session_start();

$PerformanceID=$_SESSION["PerformanceID"]=$_POST["PerformanceID"];

?>

<?php
$db_host = "n***";
$db_user = "e###";
$db_pwd = "7878iui";
$db_name = "Per*****";
mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);
?>

<?php
$select = "SELECT * FROM Peformance where PerformanceID='$PerformanceID ' ";
$export = mysql_query($select);
$fields = mysql_num_fields($export);
?>

<?php
for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "\t";
}
?>

<?php
while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);
?>

<?php
if ($data == "") {
$data = "\n(0) Records Found!\n";
}
?>

<?php
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=extraction.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
?>

jpadie · Aug 22, 2006

i'd guess the performanceid was not being posted in.
try echoing out the sql query to validate.
you should also instantiate the $data variable outside the while loop to avoid getting a php Notice.
lastly, if you're using a recent version of php, there is a better function for doing what you are doing. check out fputcsv (needs php>5.1.0RC1)

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Generate dynamic graphs from data in mysql db using php and excel or j

mamasita60440

Programmer

sleipnir214

Programmer

mamasita60440

Programmer

jpadie

Technical User

Similar threads

Part and Inventory Search

Sponsor