使用 MySQL 查询结果通过 FusionCharts 创建图表

Question

我有这个查询

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 

结果如下：

smsstatus | sms_prefix | sum
----------+------------+-----
rejected  |  963       |  2
received  |  971       |  2

我想根据国家前缀和短信数量在 Fusion Chart 上显示此结果。

这是我获取结果并显示在图表上的代码，但它有一个问题，它仅显示第一行，并且 $total 值错误=3。

我需要在图表上显示（在 XAxis 上“ sms_prefix ”，该值是“总和”的数量）。

谢谢和问候，

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

Answer 1

我对您的代码做了一些更正：

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