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 Code: Select all
smsstatus | sms_prefix | sum
----------+------------+-----
rejected | 963 | 2
received | 971 | 2This 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);
}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,