MsLine Chart using PHP and MYSQL

[sevensand29]

hi

 

i have problem with MSLine chart.
here is my database look like:

 

------------|--------------------------|----------------------------|

id            | Generator               |  Date                          |

-------------------------------------------------------------------|
1             | Plant 1                    |    2015-12-01            |

-------------------------------------------------------------------
2             |Plant 2                     |   2015-12-02             |

-------------------------------------------------------------------

3             | Plant 1                    | 2015-12-03               |

-------------------------------------------------------------------

 

here is mycode:

 

 <?php

$q = intval($_GET['q']);

 

 

                      

   $strQueryCategories = "select distinct DATE_FORMAT(tblmarketable.date,'%c-%d-%Y') as DatePro from tblmarketable where ae = 'juan' and  YEAR(date) = YEAR(NOW()) and MONTH(date) = '".$q."' order by DatePro";

 

 

                        

                        $resultCategories = mysql_query($strQueryCategories) or die(mysql_error());

 

                      

$strQueryData = "SELECT  ref, date, gen, SUM(amount) as amount from tblmarketable where ae = 'juan' and YEAR(date) = YEAR(NOW()) and MONTH(date) = '".$q."' group by gen, ref, date order by gen";

 

                      

                        $resultData = mysql_query($strQueryData) or die(mysql_error());

 

                       

                        $strXML = "<graph legendPostion='' caption='Marketable Sales' subCaption='By Juan' xAxisName='Group By Reference No: ' yAxisName='Amount' showValues='0' formatNumberScale='0' rotateValues='1' theme='fint'>";

 

                        $strXML .= buildCategories ($resultCategories, "DatePro");

 

                       

                        $strXML .= buildDatasets ( $resultData, "amount", "gen");

 

                       

                        $strXML .= "</graph>";

 

 

echo renderChartHTML("Charts/MSLine.swf", "", $strXML, "FactorySum", 600, 300, false, true);

 

 

                      

                        mysql_free_result($resultCategories);

                        mysql_free_result($resultData);

 

 

                     function buildCategories ( $result, $labelField ) {

                            $strXML = "";

                            if ($result) {

                                $strXML = "<categories>";

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

                                    $strXML .= "<category label='" . $ors[$labelField]. "'/>";

                                }

                                $strXML .= "</categories>";

                            }

                            return $strXML;

                        }

 

                      

 

 

                        function buildDatasets ($result, $valueField, $controlBreak ) { 

                            $strXML = "";

                            if ($result) {

 

                                $controlBreakValue ="";

                                     

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

 

echo" ";

 

                                    if( $controlBreakValue != $ors[$controlBreak] ) {

                                        $controlBreakValue =  $ors[$controlBreak];

                                        $strXML .= ( $strXML =="" ? "" : "</dataset>") . ( "<dataset seriesName='" . $controlBreakValue . "'>" ) ;

                                    }

                                  

   $strXML .= "<set value='" . $ors[$valueField] . "'/>";

 

                                }

                                $strXML .= "</dataset>";

                            }

 

                            return $strXML;

 

                        }

?> 

 

 

the PLANT 1 (id 1) is correct in the position date, but

the problem is the PLANT 2 date become 2015-12-01 that shows in a graph instead of  2015-12-02

and the PLANT 1 (id 3) date become 2015-12-02 instead of 2015-12-03

anyone can help me?

regards..




 

-

[Moonmi Sonowal]

Hi,

 

Please check out the attached sample from the drop box link: https://www.dropbox.com/s/y1kfeli59kasgp3/phpDatabasesample.zip?dl=0

 

It has a demo database, please export and check it at your end.

 

Please do share your observations.