[MySQL Chart] Values being shown multiple times

[AjnabiZ]

Hello

 

 

 

I am trying to implement fusioncharts evaluation version with my shopping cart.

 

 

 

Here is the problem :

 

 

 

 

I have attached a screenshot as well.

 

 

 

I have trying to show "Total Sales by Customer".

 

 

 

I do get the total amount correctly, but it is being displayed multiple times.

 

 

 

Here is the PHP Code im using :

 

 

 

<?php   

   //Connect to the DB

  $link = connectToDB();



  //$strXML will be used to store the entire XML document generated

  //Generate the chart element

  $strXML = "";



  //Fetch all factory records

  $strQuery = "select * from va_orders";

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



  //Iterate through each factory

  if ($result) {

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

        //Now create a second query to get details for this factory

        $strQuery = "select sum(order_total) as OrderTotal from va_orders where user_id=" . $ors['user_id'];

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

        $ors2 = mysql_fetch_array($result2);

        //Generate 

        $strXML .= "";

        //free the resultset

        mysql_free_result($result2);

     }

  }

  mysql_close($link);



  //Finally, close  element

  $strXML .= "";



  //Create the chart - Pie 3D Chart with data from $strXML

  echo renderChart("../FusionCharts/Column3D.swf", "", $strXML, "FactorySum", 600, 300, false, false);

?>

[FusionCharts Support]

Hi ,

 Try using this SQL query for the first SQL Query String

 

"select distinct user_id,name from va_orders"

 

YOu can even optimize the SQL to put all in a single SQL Query with out the need to second query result. We did that for demonstration purpose.

 

can use this "select  name, sum(order_total) from va_orders group by name"

The code may look like this:

 

$strQuery = "select  name, sum(order_total) from va_orders group by name";
  $result = mysql_query($strQuery) or die(mysql_error());
  
  if ($result) {
  while($ors = mysql_fetch_array($result)) {
	 $strXML .= "<set label='" . $ors['name'] . "' value='" . $ors['OrderTotal'] . "' />";
	 
	 }
   }

[AjnabiZ]

Hello

 

 

 

Thanks alot for a quick reply as well as the fix.

 

 

 

I added the distinct user_id and name to the sql statement and it worked fine :Wow:

[AjnabiZ]

Hello

 

 

 

Can anyone tell me what settings to make when there are 1000+ customers and I need to show their totals.

 

 

 

The graphs will get jumbled with so many names.

 

 

 

Is there a way to show , let say, 10 customers , then click next to see the Next 10 and so on.

[FusionCharts Support]

Hi,

This will need proper SQL Query where you can use LIMIT  

"SELECT * FROM tbl LIMIT 5,10" // this will show records fom 6 to 15

Ref : http://dev.mysql.com/doc/refman/5.0/en/select.html

Now for each press of button you can reload the chart passing the limits to another file where the XML will be created from database.

You can go through our PHP example...from Documentaion's 'Guide For Web Developers' section's -Using With PHP - PHP,JAvascript and DataURL page...for more insight on how to use that.

http://www.fusioncharts.com/docs/Contents/PHP_JS_URL.html

[AjnabiZ]

Sudipto Choudhury (11/16/2007)

---

Hi,

 

 

 

This will need proper SQL Query where you can use LIMIT

 

 

 

"SELECT * FROM tbl LIMIT 5,10" // this will show records fom 6 to 15

 

 

 

Ref : http://dev.mysql.com/doc/refman/5.0/en/select.html

 

 

 

Now for each press of button you can reload the chart passing the limits to another file where the XML will be created from database.

 

 

 

You can go through our PHP example...from Documentaion's 'Guide For Web Developers' section's -Using With PHP - PHP,JAvascript and DataURL page...for more insight on how to use that.

 

 

 

http://www.fusioncharts.com/docs/Contents/PHP_JS_URL.html

 

 

 

Thanks a lot

 

 

 

I will check it out to see it it works.