jimmydean25

Request for simple PHP/MySQL example

Recommended Posts

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,

Share this post


Link to post
Share on other sites

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);

Share this post


Link to post
Share on other sites

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)
);
        ?>
       

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

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
    
?>

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