Use PHP&MySQL query result to create chart with FusionCharts

[alkhader]

[php]SELECT DISTINCT (sl.smsstatus), sl.sms_prefix, sum(sl.parts) AS sum
FROM sms_log sl, sms_transaction st
WHERE
st.user_id = 552
AND st.customer_id = 1
AND st.sendtime >= 1328050800
AND st.sendtime <= 1328309999
AND st.sms_trans_id = sl.trans_id
GROUP BY sl.sms_prefix[/php]

The result as follows:

[php]smsstatus | sms_prefix | sum
----------±-----------±----
rejected | 963 | 2
received | 971 | 2[/php]

I want to display this result on Fusion Chart based on country prefix and number of SMS.

This is my code to get the result and display on the chart, But it has a problem, It displays only the first row.

[php]$res_smsstat_status = $oSMSStatHandlerCnf->get_stat_country($_user_id, $_customer_id, $s_start_time, $s_end_time);

while ($smsstat_status_row = mysql_fetch_assoc($res_smsstat_status)) {
$total = $total + $smsstat_status_row[‘sum’];
$strXML = “”;
if ($total > 0) $strXML .= “<set name=’”.$smsstat_status_row[‘sms_prefix’]."’ value=’".$total."’ color=’#87CEFA’/>";
$strXML .= “”;
echo renderChartHTML(“chart/FusionCharts/Column2D.swf”, “”, $strXML, “myNext”, 790, 300);
}[/php]

I need to display on the chart ( on XAxis " sms_prefix " and the value is the amount of ‘sum’).
So, the result should be 2 bars one for the prefix “963” with amount “2” and one for “971” with amount “2”.

Any help please.

Thanks and Regards,

[alkhader]

Any Help?

[rishi_fusion]

Hi,

I have made a few changes in your code, and explained them inline with comments:

[php] $res_smsstat_status = $oSMSStatHandlerCnf->get_stat_country($_user_id, $_customer_id, $s_start_time, $s_end_time);

// Initialize $total to 0 for safe programming

    $total = 0;

// Initialize $strXML *before* the while loop. If you do this within the loop, then you'll get only 1 chart with all the values summed up.

    $strXML = "<graph caption='".$oLangHandler->tr("Traffic Graph")."' xAxisName='".$oLangHandler->tr("Delivery Status")."' yAxisName='".$oLangHandler->tr("SMS Amount")."' decimalPrecision='0' formatNumberScale='0'>";

    while ($smsstat_status_row = mysql_fetch_assoc($res_smsstat_status)) {
            $total = $total + $smsstat_status_row['sum'];
            if ($total > 0) $strXML .= "<set name='".$smsstat_status_row['sms_prefix']."' value='".$total."' color='#87CEFA'/>";

    }

    $strXML .= "</graph>";

    echo renderChartHTML("chart/FusionCharts/Column2D.swf", "", $strXML, "myNext", 790, 300);[/php]