|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/24/2007 4:04:22 PM
Posts: 7,
Visits: 15
|
|
well my database have a columm with date ex: YYYY-MM-DD.
I´m working on a booking system and want my chart to show how many visits hi had for lets say, 2005-2006-2007.
all data is stored in one table.
I changed the original sql query to this:
$strQuery = "select sum(newsdate) as TotOutput from visits GROUP BY YEAR(newsdate)";
I get the right visits but the years doesn,t get as a group, insted i get all the dates, lets say insted of getting 2005 - 3 visits, 2006 4 visits and so on, i get 2005-11-25 lets say that i have total 10 visits, then all dates gets the same visits, and all dates (YYYY-MM-DD) shows in the chats.
Any help here?
|
|
|
|
|
Supreme Being
Group: Moderators
Last Login: Today @ 5:38:52 AM
Posts: 884,
Visits: 1,420
|
|
| Hi, Use count instead of sum $strQuery = "select count(newsdate) as TotOutput from visits GROUP BY YEAR(newsdate)";
Regards,
Sudipto Choudhury
FusionCharts Team
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/24/2007 4:04:22 PM
Posts: 7,
Visits: 15
|
|
Well i have allready tested the solution above before:
This is the code i´m using:
// Fetch all factory records
$strQuery = "select * from visits";
$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 count(newsdate) as TotOutput from visits GROUP BY YEAR(newsdate)";
$result2 = mysql_query($strQuery) or die(mysql_error());
$ors2 = mysql_fetch_array($result2);
//Generate
$strXML .= "";
//free the resultset
mysql_free_result($result2);
the result is that the charts shows all the dates example: 2007-11-12, 1 unit, 2007-10-15, 1 unit, 2005-10-21, 1 unit.
It should be 2007, 2 units, 2005, 1 unit and so on. But the years dont group.
The field "newsdate" in the table visits is set as date with value 0000-00-00. When the sql query is tested under mysql admin i get the years as a group, but not in the fusionchart...
Any help here?
|
|
|
|
|
Supreme Being
Group: Moderators
Last Login: Today @ 5:38:52 AM
Posts: 884,
Visits: 1,420
|
|
The SQL will be :
select Year(newsdate) as SYear, Count(newsdate) as TotalOutput from fc_orders group by Year(newsdate)
Run this in a single loop to fetch data and build XML as following : from columns : label={SYear} and value={TotalOutput} |
Regards,
Sudipto Choudhury
FusionCharts Team
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/24/2007 4:04:22 PM
Posts: 7,
Visits: 15
|
|
I changed to this, but still not working.
// Fetch all factory records
$strQuery = "select * from visits";
$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 Year(newsdate) as SYear, Count(newsdate) as TotalOutput from visits group by Year(newsdate)";
$result2 = mysql_query($strQuery) or die(mysql_error());
$ors2 = mysql_fetch_array($result2);
//Generate
$strXML .= "<set label={SYear} value={TotalOutput} />";
I´m getting the error:
Invalid XML data...
Almost there 
|
|
|
|
|
Supreme Being
Group: Moderators
Last Login: Today @ 5:38:52 AM
Posts: 884,
Visits: 1,420
|
|
not this way ... What i meant was to use this way..
$strXML ="<chart>";
$strQuery = "select Year(newsdate) as SYear, Count(newsdate) as TotalOutput from visits group by Year(newsdate)";
$result = mysql_query($strQuery) or die(mysql_error());
if ($result) {
while($ors = mysql_fetch_array($result)) {
$strXML .= "<set label='" . $ors['SYear'] . "' value='" . $ors['TotalOutput'] . "'/>";
}
$strXML .="</chart>";
this will build the XML needed..
Regards,
Sudipto Choudhury
FusionCharts Team
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/24/2007 4:04:22 PM
Posts: 7,
Visits: 15
|
|
Thank you. It works perfect now.
I only changed:
$strXML .= "<set label='" . $ors['SYear'] . "' value='" . $ors['TotalOutput'] . "'/>";
To: $strXML .= "<set name='" . $ors['SYear'] . "' value='" . $ors['TotalOutput'] . "'/>";
Thank you for all help.
By the way, when using column3d.swf as chart i get the years right, but the visits show funny, for example, i only added a few dates for testing, for example two 2004, one 2005, three 2006 and two 2007. The scale at the left shows the vits this way:
4
3
2
2
1
0
The chart shows 2 times the number two in the scale, if i remove som dates it shows sometimes
3
2
2
1
1
0
But if i increase the amount, it shows the right value. I tested for example Sum(visitors) and the chart was right.
In this case
30
24
18
12
6
0
Is this a bug in the chart meaning that if there is less than lets say 5 values, the column chart always must show a least up to 5 rows in the column?
|
|
|
|
|
Supreme Being
Group: Moderators
Last Login: Today @ 5:38:52 AM
Posts: 884,
Visits: 1,420
|
|
| Hi, The chart displays exactly the number it gets from the XML. So it may be some issue while getting the count..before rendering the chart can you please check the SQL in some gui for MSSQL like SQLYog etc where you can first put the SQL query and view the result visually in a table.
Regards,
Sudipto Choudhury
FusionCharts Team
|
|
|
| | |