CODEWITHSUNDEEP
phpmysqlfusioncharts
alkhader
alkhader

Reputation: 998

Use MySQL query result to create chart with FusionCharts

I have this query 

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:

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 and $total value comes wrongly=3.

I need to display on the chart ( on XAxis " sms_prefix " and the value is the amount of 'sum').

Thanks and Regards,

$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);
}

Upvotes: 0

Views: 1162

Answers (1)

Hrishikesh -Rishi- Choudhari
Hrishikesh -Rishi- Choudhari

Reputation: 12447

I have made a few corrections in your code:

        $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);

Upvotes: 1

Related Questions