AquaTeamV3

Mysql, Converting Data Into A Pie Chart

Recommended Posts

Hello,

 

I'm having a little problem understanding how to get something to work.  I have a SQL table, and essentially I'm trying to get the total number of each selected column row to appear as its own pie slice.  Additionally, I need to be able to get these pie slices to be clickable for a drill-down effect.  

 

<?php
//We've included ../Includes/FusionCharts_Gen.php, which contains
//FusionCharts PHP Class to help us easily embed charts
//We've also used ../Includes/DBConn.php to easily connect to a database.
include("FusionCharts_XT_Evaluation/Code/PHPClass/Includes/FusionCharts_Gen.php");
include("FusionCharts_XT_Evaluation/Code/PHPClass/Includes/DBConn.php");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<HTML>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

        <TITLE>
		Scan Results - Home Page
        </TITLE>
        <?php
        //You need to include the following JS file, if you intend to embed the chart using JavaScript.
        //Embedding using JavaScripts avoids the "Click to Activate..." issue in Internet Explorer
        //When you make your own charts, make sure that the path to this JS file is correct. Else, you would get JavaScript errors.
        ?>
        <SCRIPT LANGUAGE="Javascript" SRC="FusionCharts/FusionCharts.js"></SCRIPT>
		<SCRIPT LANGUAGE="Javascript" SRC="FusionCharts/jquery.min.js"></SCRIPT>

        <!--[if IE 6]>
        <script type="text/javascript" src="FusionCharts_XT_Evaluation/Code/PHPClass/assets/ui/js/DD_belatedPNG_0.0.8a-min.js"></script>

<script>
          /* select the element name, css selector, background etc */
          DD_belatedPNG.fix('img');

          /* string argument can be any CSS selector */
        </script>
        <![endif]-->

        <link href="FusionCharts_XT_Evaluation/Code/PHPClass/assets/ui/css/style.css" rel="stylesheet" type="text/css" />
        <style type="text/css">
            h2.headline {
                font: normal 110%/137.5% "Trebuchet MS", Arial, Helvetica, sans-serif;
                padding: 0;
                margin: 25px 0 25px 0;
                color: #7d7c8b;
                text-align: center;
            }
            p.small {
                font: normal 68.75%/150% Verdana, Geneva, sans-serif;
                color: #919191;
                padding: 0;
                margin: 0 auto;
                width: 664px;
                text-align: center;
            }
        </style>
    </head>
    <BODY>

        <div id="wrapper">

            <div id="header">
              <div class="logo"><a class="imagelink"  href="http://www.fusioncharts.com/" target="_blank"><img src="FusionCharts_XT_Evaluation/Code/PHPClass/assets/ui/images/fusionchartsv3.2-logo.png" width="131" height="75" alt="FusionCharts XT logo" /></a></div>
                <h1 class="brand-name">FusionCharts XT</h1>
                <h1 class="logo-text">PHP Class Examples</h1>
</div>

            <div class="content-area">
                <div id="content-area-inner-main">

                    <div class="gen-chart-render">

                        <CENTER>

                            <?php
                            //In this example, we show how to connect FusionCharts to a database.
                            //For the sake of ease, we've used an MySQL databases containing two
                            //tables.
                            
                            // Connect to the Database
                            $link = connectToDB();
                            
                            # Create pie 3d chart object using FusionCharts PHP Class
                            $FC = new FusionCharts("Pie2D","500","500");
                            
                            # Set Relative Path of swf file.
                            $FC->setSWFPath("FusionCharts/");
                            
                            # Define chart attributes
                            $strParam="caption=Scan Results;subCaption=By Quantity;pieSliceDepth=30; showBorder=1;numberSuffix= Units";
                            
                            #  Set chart attributes
                            $FC->setChartParams($strParam);
                            
                            
                            // Fetch all factory records usins SQL Query
                            //Store chart data values in 'total' column/field and category names in 'FactoryName'
                            $strQuery = "SELECT SUM(cleanedFiles), SUM(quarantinedFiles) SUM(failedFiles), SUM(passprotectedFiles), SUM(skippedFiles), SUM(mismatchedFiles), SUM(blockedFiles), SUM FROM test.scanresults";
							
							//$quarantinedQuery = "select a.FactoryID, b.FactoryName, sum(a.Quantity) as total from Factory_output a, Factory_Master b 
                            //where a.FactoryId=b.FactoryId group by a.FactoryId,b.FactoryName";
							//$infectedQuery = "SELECT SUM(infectedFiles) as infected FROM test.scanresults";
							//$passQuery = "SELECT SUM(passProtectedFiles) as passpro FROM test.scanresults";
							//$failedQuery = "SELECT SUM(failedFiles) as failed FROM test.scanresults";
							//$skippedQuery = "SELECT SUM(skippedFiles) as skipped FROM test.scanresults";
							//$mismatchedQuery = "SELECT SUM(mismatchedFiles) as mismatched FROM test.scanresults";
							//$blockedQuery = "SELECT SUM(blockedFiles) as blocked FROM test.scanresults";
	
                            $result = mysql_query($strQuery) or die(mysql_error());
														
                            
                            //Pass the SQL Query result to the FusionCharts PHP Class function
                            //along with field/column names that are storing chart values and corresponding category names
                            //to set chart data from database
                            if ($result) {
                                $FC->addDataFromDatabase($result,"SUM(cleanedFiles), SUM(quarantinedFiles) SUM(failedFiles), SUM(passprotectedFiles), SUM(skippedFiles), SUM(mismatchedFiles), SUM(blockedFiles))";
                            }
                            mysql_close($link);
                            
                            # Render the chart
                            $FC->renderChart();
                            ?>
                        </CENTER>

                    </div>
                    <div class="clear"></div>
                    <p> </p>
                    <p class="small">  </p>

                    <div class="underline-dull"></div>
                </div>
            </div>

            <div id="footer">
                <ul>
                    <li><a href="../index.html"><span>« Back to list of examples</span></a></li>
                    <li class="pipe">|</li>
                    <li><a href="../NoChart.html"><span>Unable to see the chart above?</span></a></li>
                </ul>
            </div>
        </div>
    </BODY>
</HTML>

 

 

Share this post


Link to post
Share on other sites
Guest Sashibhusan

Hi,

 

Welcome to FusionCharts Forum.

 

The correct syntax for "addDataFromDatabase" method will be:

addDataFromDatabase(resource $query_result, string $db_field_ChartData[, string $db_field_CategoryNames, string $strParam, string $link])

where,

$query_result = SQL query result to fetch dataset from database.

$db_field_ChartData = database field that contains data values.

$db_field_CategoryNames = database field that contains category names.(Optional for multi-series/stacked/combination charts)

$strParam = (Optional) delimiter separated attribute list for dataplots.

$link = (Optional) Adds hyperlink feature to dataplots. This adds the link attribute to dataplots or <set> elements.It can be a simple URL say, "http://www.google.com" or another page say, "Drill/Detailed.php" etc. For details on drill down and creating links using FusionCharts XT, please go through the section Drill Down Charts.

 

So, the way you passed the parameters to the "addDataFromDatabase" method is not correct.

 

As per your requirement, you can create a temporary table with 3 fields (such as "Sum_values", "summed_fieldName" and "link") and then store the results of your SQL query string in the temporary table, and then pass the field names to the "addDataFromDatabase()" method.

 

For more informatio on "Plotting data from a database", please follow the link below:

http://docs.fusioncharts.com/charts/contents/?exporting-image/server-side/ECServerSave.html

 

Hope this helps!

Edited by Sashibhusan

Share this post


Link to post
Share on other sites

Hi 

I am trying to get data from MySql loctated on a server. I am getting following error:

( ! ) Parse error: syntax error, unexpected T_VARIABLE in C:\wamp\www\Database.php on line 41

Please tell me the solution:

And also the chart can't be generated.

 

Here is my code:

 

<?php
include("includes/FusionCharts.php");
include("includes/DBConn.php");
?>
<html>
<title> FusionChart</title>
<head>
<script language="javascript" src="includes/FusionCharts.js"></script>
 
</head>
<body>
<center>
<?php
 
//connect to the DB
$link= connectToDB();
//$strXML will be used to store the entire XML document generated
//Generate the graph element
$strXML = "<graph caption='Worklog repor' subCaption='Month wise' xaxisname='Current Month' yaxisname='WorkData' yAxisMaxValue='200'
animation='1'>";
 
//Fetch records from database
$query= "select Password,info_r3_c1 from work_data1";
$result = mysql_query($query) or die(mysql_error());
//echo $result;
 
$row=mysql_fetch_array($result)
 
//Iterate through each patient blood pressure systole
 
 
//Generate the setname and value
//echo $row['Date'];
//$date=;
//$sr=;
//echo $date;
//echo $row['Systole_reading'];
//$strXML.="<set name='' value='". $row['systole_reading']."'/>";
//$strXML.="<set name='ondate' value".$result['ondate']."'/>";
 
$strXML .="<set name='" . $row['Password'] . "' value='" . $row['info_r3_c1'] . "' />";
 
mysql_free_result($result);
 
 
 
//Finally, close <graph> element
$strXML .= "</graph>";
//Create the chart - Pie 3D Chart with data from $strXML
echo renderChartHTML("Includes/FusionCharts/Column2D.swf", "", $strXML, "Password", 650, 450,false);
//echo renderChartHTML("./Charts/FCF_Bar2D.swf", "", $strXML, "ad", 800, 600, false);
 
?>
 
</center>
 
</body>
 
</html>
Edited by preeti.91

Share this post


Link to post
Share on other sites
Guest Sashibhusan

Hi Preeti,

 

Could you please try once by iterating the result set in a while() loop?

 

Else, please provide a semicolon ";" at the end of the code: $row=mysql_fetch_array($result);

 

If the chart still not renders, could you please echo the "$strXML" string just before calling renderChartHTML() method?

 

Also, we will suggest you to use "renderChart()" method instead of the deprecated "renderChartHTML()" method.

 

Hope this helps!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now