Octagon_AU

i want to add new row to to the bottom which is to get the result of the total. code : $sql_query = "SELECT * FROM sales where Date BETWEEN #8/1/2015# AND #8/30/2015# ORDER BY BRANCH asc"; $rs = odbc_exec($connect, $sql_query); if(!$rs) {exit("Error in SQL");} $dates = array(); $data = array(); while (odbc_fetch_array($rs)) { $rows[] = array( 'branch' => odbc_result($rs,'BRANCH'), 'date' => odbc_result($rs,'Date'), 'sales' => odbc_result($rs,'SumOftotal_inc') ); } foreach($rows as $row) { $dates[] = $row['date']; $data[$row['branch']][$row['date']] = $row['sales']; } $dates = array_unique($dates); sort($dates); $no_data = "<p style = 'color:#fe0000'>0.00</p>"; $date_format = 'n/j/Y'; $currency = '₱'; $content = "<table class='table table-bordered table-hover'><tr style = 'background-color:#878787;font-weight:bold;font-size:12px;color:#ccc;'><th style = 'padding-top:20px;width:800px;'>BRANCH / DATE</th><th style = 'color:#ccc;padding-top:20px;'> TOTAL </th>"; foreach($dates as $date) { $dt = new DateTime($date); $df = $dt->format($date_format); $content .= "<th style = 'padding-top:20px;'> $df </th>"; } $content .= "</tr>"; foreach($data as $branch=>$arr) { $content .= "<tr class='active'><td class='col-md-6' style = 'background-color:#878787;font-weight:bold;font-size:12px;color:#ccc;padding-top:20px;'> $branch </td>"; $total = array_sum($arr); $content .= "<td style = 'color:#FE0000;font-size:12px;padding-top:20px;font-weight:bold;'>$currency" .number_format($total,2). "</td>"; foreach($dates as $date) { $value = isset($arr[$date]) ? $currency.number_format((double)$arr[$date],2) : $no_data; $content .= "<td class='text-center' style = 'color:#874b03;font-size:12px;padding-top:20px;'>$value</td>"; } $content .= "</tr>"; } foreach($total as $tot_count) { $count_total = array_sum($tot_count); } $content .= "<tr><td>TOTAL</td>"; $content .= "<td>$currency" .number_format($count_total,2). "</td>"; // display over all total $content .= "<td>TOTAL</td></tr>"; // display per date total $content .= "</table>"; echo $content;

Hi, i just want to add new row to compute over_all total and total per day or per date? how

Thank you so much guys. to all your help thank you...

Hi, Almost done, but i get extra rows and it don't need.

Hi. mac_gyver and ch0cu3r, a little bit, and very close to solve my problem. i get this output and what is wrong with it. code $sql1 = "SELECT * FROM sales"; $rs = odbc_exec($connect, $sql1); if(!$rs) {exit("Error in SQL");} $dates = array(); $data = array(); while (odbc_fetch_array($rs)) { $rows[] = array( 'branch' => odbc_result($rs,'BRANCH'), 'date' => odbc_result($rs,'Date'), 'sales' => odbc_result($rs,'SumOftotal_inc') ); } foreach($rows as $row) { $dates[] = $row['date']; $data[$row['branch']][$row['sate']] = $row['sales']; } $dates = array_unique($dates); sort($dates); $no_data = 'n/a'; $date_format = 'n/j/Y'; $currency = '$'; $content = "<table class='table table-bordered'><tr><th>BRANCH / DATE</th>"; foreach($dates as $date) { $dt = new DateTime($date); $df = $dt->format($date_format); $content .= "<th> $df </th>"; } $content .= "<th> TOTAL </th>"; foreach($data as $branch=>$arr) { $content .= "<tr><th> $branch </th></tr>"; foreach($dates as $date) { $value = isset($arr[$date]) ? $currency.number_format((double)$arr[$date],2) : $no_data; $content .= "<td>$value</td>"; } $total = array_sum($arr); $content .= "<td>$currency" .number_format($total,2). "</td></tr>"; } $content .= "</table>"; echo $content;

Hi mac_gyver, Thank you so much for your time. and in ODBC its need to have this lines to get the result but how do i convert this in array? $sql1 = "SELECT * FROM sales"; $rs = odbc_exec($connect, $sql1); if(!$rs) {exit("Error in SQL");} while (odbc_fetch_row($rs)) { $rs_total = odbc_result($rs,'SumOftotal_inc'); $rs_branch = odbc_result($rs,'BRANCH'); $rs_date = odbc_result($rs,'Date'); } i try to have this but, not work. $rows[] = array($rs_branch,$rs_date,$rs_total);

Output which is not correct

Hi Barand, i try to follow your code but i did not get it. and difficult to convert using MS Access DB or using ODBC. take a look my code and it work but wrong column base on header. Hope someone can take a time to analyze code below $sql1 = "SELECT DISTINCT BRANCH FROM sales ORDER BY BRANCH asc"; $rs = odbc_exec($connect, $sql1); if(!$rs) {exit("Error in SQL");} echo"<table class='table table-bordered'>"; $result_branch = array(); while (odbc_fetch_row($rs)) { $result_branch[] = odbc_result($rs,'branch'); } echo "<tr style = 'background-color:#ccc;'>"; echo "<td style = 'font-weight:bold;font-size:12px;'>"; echo "BRANCH / DATE"; echo "</td>"; $sqldate = "SELECT DISTINCT Date FROM sales where Date BETWEEN #8/1/2015# AND #8/30/2015# ORDER BY Date asc"; $rs1 = odbc_exec($connect, $sqldate); $result_date = array(); while (odbc_fetch_row($rs1)) { $result_date[] = odbc_result($rs1,'Date'); } foreach($result_date as $rs_date) { echo "<td style = 'font-weight:bold;font-size:12px;'>"; echo substr("$rs_date",0, 10); echo "</td>"; } echo "<td style = 'font-weight:bold;'>" . TOTAL . "</td>"; echo "</tr>"; foreach($result_branch as $rs_branch) { echo "<tr>"; echo "<td style = 'font-weight:bold; width:70px;background-color:#ccc;font-size:12px;'>". $rs_branch . "</td>"; $sql2 = ("SELECT Date,SumOftotal_inc,BRANCH FROM sales WHERE BRANCH = '$rs_branch' ORDER BY Date asc"); $rs2 = odbc_exec($connect, $sql2); $result_total = array(); $result_date2 = array(); while (odbc_fetch_row($rs2)) { $result_total[] = odbc_result($rs2,'SumOftotal_inc'); $result_date2[] = odbc_result($rs2,'Date'); } foreach($result_total as $rs_total) { echo "<td style = 'color:#fe0000;font-size:12px;'>₱". number_format($rs_total, 2). "<input type = 'hidden' name = 'hdden' value = '$result_date2'></td>"; } echo "<td style = 'color:#000;font-weight:bold;'> ₱"; $mval = array_sum($result_total); echo number_format($mval, 2); echo "<input type = 'hidden' name = 'hdden' value = '$result_date2'></td>"; echo "</tr>"; } echo "<tr>"; echo "<td style = 'font-weight:bold;'>"; echo "Total"; echo "</td>"; echo "</tr>"; echo "</table>";

What do you mean?

Hi, i get difficulties to solve the following below and i hope someone can help with this.