Request for simple PHP/MySQL example

[jimmydean25]

Hello, I am having difficulties using my mysql/php database with your charts. I just need a very simple (no drill down) example so I can start somewhere. Your "Basic" PHP DB example does not seem that simple to me and I keep getting either blank datasets or mysql errors. Can someone please generate a simple bar chart for the following query? Query = SELECT COUNT(evt_time_generated) as Count, evt_computer FROM eventlog.log_alerts group by evt_computer; This will get me a good starting point. Thanks,

[FusionCharts Support]

Hi,

try this:

 

//Before this insert your code to connect to database (MySQL) = $link

$strQuery = "SELECT COUNT(evt_time_generated) as Count, evt_computer FROM eventlog.log_alerts group by evt_computer";

$result = mysql_query($strQuery) or die(mysql_error());

 

//generating XML for Chart 

$strXML = "<graph>";

 if ($result) {

while($ors = mysql_fetch_array($result)) {

 $strXML .= "<set name='" . $ors['evt_computer'] . "' value='" . $ors['Count'] . "' />";

 }

 mysql_close($link);

 $strXML .= "</graph>";

 

 //Create the chart 

 echo renderChart("Column3D.swf", "", $strXML, "ChartId", 650, 450, false, false);

[safadig]

I am having difficulty getting a chart to display for current date that I would select from an inline jquery Calendar. the Calendar has the behavior of posting the date when a date is selected; I am trying to use to posted date to select the data for that date to be plotted in the chart. when I select the date, my chart page opens but displays 'No data to display'. I spent hours but cannot find the reason..

The Calendar File 'Popupdate.php'

<FORM NAME='datecount' ACTION='popupchart.php' METHOD='POST'>

  <input name="currentdate" type="text" id="currentdate">

</FORM>

 

and the Popupchart file:

 

  <script type="text/javascript" src="../Fusion/Charts/FusionCharts.js"></script>

<meta charset='utf-8'>

 <?php

  include("../includes/FusionCharts.php");

   include("../includes/FusionCharts_Gen.php");

   include("../includes/DBConn.php");

$mydate=$_POST['currentdate'];

$link = connectToDB();

 

 

$strQuery ="SELECT * from Counts WHERE date ='" . $mydate . "' ";

// Execute the query, or else return the error message.

$result = mysql_query($strQuery) or die(mysql_error());

$strXML = "<chart caption='Nortwest Ohio NAB Certified Pollen Count April/May 2014' subcaption='© 2014, Dr. Safadi & Associates, Inc. All rights reserved.'>";

 

Thanks for the help

 

 

// If we get a valid response - 

if ($result) {

while($row_strQuery = mysql_fetch_assoc($result))

{

        $strXML .= "<set label='Trees' value='".$row_strQuery['trees']." color='00FF00'/>";

$strXML .= "<set label='Grass' value='".$row_strQuery['grass']."' color='00FF00'/>";

$strXML .= "<set label='Weeds' value='".$row_strQuery['weeds']."' color='00FF00'/>";

$strXML .= "<set label='Mold' value='".$row_strQuery['mold']." color='00FF00'/>";

}

}

 

mysql_close($link);

  

 

 

 

$strXML .= "</chart>";

 

 

       

 

          

// Set the rendering mode to JavaScript

FC_SetRenderer('javascript');

 

// Call the renderChart method, which would return the HTML and JavaScript required to generate the chart

            echo renderChart('Charts/Column2D.swf', // Path to chart type

'', // Empty string when using Data String Method

$strXML, // Variable that contains XML string

'cal_pollen_count', // Unique chart ID

'680', '400', // Width and height in pixels

false, // Disable debug mode

true // Enable 'Register with JavaScript' (Recommended)

);

        ?>

       

[Guest Sashibhusan]

Hi,

 

Could you please echo the $strXML value before calling renderChart() method, and provide the output XML string generated, to better look into the issue?

[Julhas Sujan]

Hi,

 

You may try like this:

 

<?php

    // Database Load
        require(dirname(__FILE__)."/../../config/db.php");
    
    // Company code
        $bukrs = $_GET['cust_agign_value'];

            
    // Temporary table for aging calculation
        mysql_query("CREATE TEMPORARY TABLE temp_customer_aging ENGINE=MEMORY as
        (select kunnr,name, range30 range1,range60 range2,range90 range3,
        (range120+range150+range180+range210+range999) range4,
        (range30+range60+range90+range120+range150+range180+range210+range999) total
        from aging where bukrs='$bukrs')");
    
    // Customer aging result table for range calculation with percentage calculate
    
        mysql_query("CREATE  TEMPORARY TABLE customer_aging_result ENGINE=MEMORY as    
        (select round((sum(range1)/sum(total))*100,2)
        range30,round((sum(range2)/sum(total))*100,2)
        range60, round((sum(range3)/sum(total))*100,2)
        range90, round((sum(range4)/sum(total))*100,2)
        range90_above from temp_customer_aging)");
    
    // Temporary table for first range data insert    
        mysql_query("CREATE TEMPORARY TABLE temp_final
        select 'range30' as Header, range30 as DATA
        from customer_aging_result");
            
    // Insert 2nd data in temporary table            
        mysql_query("INSERT INTO temp_final
        select 'range60' as Header, range60 as DATA
        from customer_aging_result");
    
    // Insert 3rd data
        mysql_query("INSERT INTO temp_final
        select 'range90' as Header, range90 as DATA
        from customer_aging_result");
        
    // Insert 4th Data
        mysql_query("INSERT INTO temp_final
        select 'range90_above' as Header, range90_above as DATA
        from customer_aging_result");
            
    // Combining row data into column data        
        $customer_aging_result=mysql_query("select * from temp_final");             

    // XML Start here                    
                        $xml_output = "<?xml version=\"1.0\" ?>\n";
                        
    // Chart property Define    
                        $xml_output .= "\t<chart caption='' showvalues='1' unescapeLinks='1' legendPosition='BOTTOM' xAxisName='Customer Name' yAxisName='Amount' ieRadius='75'>\n";
    
                        $xml_output.= "<dataset showValues='1'>\n";
                            for($x2 = 0 ; $x2 < mysql_num_rows($customer_aging_result) ; $x2++)
                            {                                    
                                $row2 = mysql_fetch_assoc($customer_aging_result);
                                
                                $xml_output .= "<set label='".$row2['Header']."' value='".$row2['DATA']."' link='#info' id='popup' />\n";
                            
                            }                                
                         $xml_output .= "\t</dataset>\n";                            
                        $xml_output .= "</chart>\n";
                     echo $xml_output;

    //Create the XML file
                $fp = fopen(dirname(__FILE__)."/../sd/sd_customer_aging_div_01_data.xml","wb");
                    
    //Write the XML nodes
                fwrite($fp,$xml_output);
                
    //Close the database connection
                fclose($fp);
    
    // Load chart javascript page            
            require(dirname(__FILE__)."/../sd/sd_customer_aging_01_result.php");

    // Start time load    
            require(dirname(__FILE__)."/../../config/end_time.php");
    
    // Temporary Table drop        
        mysql_query("DROP TABLE temp_customer_aging");    
        mysql_query("DROP TABLE customer_aging_result");    
        
    //    MySQL Close
        mysql_close($link);    // See db connection in ../config/db.php
    
?>