Page 1 of 1

Use PHP&MySQL query result to create chart with FusionCharts

Posted: Sat Feb 04, 2012 3:34 am
by alkhader

Code: Select all

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 
The result as follows:

Code: Select all

smsstatus | sms_prefix | sum
----------+------------+-----
rejected  |  963       |  2
received  |  971       |  2
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.

Code: Select all

$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 = "<graph caption='".$oLangHandler->tr("Traffic Graph")."' xAxisName='".$oLangHandler->tr("Delivery Status")."' yAxisName='".$oLangHandler->tr("SMS Amount")."' decimalPrecision='0' formatNumberScale='0'>";
    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);
}
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,