Use PHP&MySQL query result to create chart with FusionCharts

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
alkhader
Forum Newbie
Posts: 1
Joined: Sat Feb 04, 2012 3:22 am

Use PHP&MySQL query result to create chart with FusionCharts

Post 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,
Post Reply