kat35601 Posted April 12, 2021 Share Posted April 12, 2021 I always have trouble formatting my HTML in PHP. I want to create a total at the bottom of each uomptrucknumber to show the total for all the orders on that truck. I have included a screen shot of the output. So I want to put a row that adds up the order totals and show that total for every truck number. before the next truck number. <html> <head> <title>Loads Between Dates</title> </head> <body> <style> table { border: 1px solid #B0CBEF; border-width: 1px 0px 0px 1px; font-size: 14pt; font-family: Calibri; font-weight: 100; border-spacing: 0px; border-collapse: collapse; } TH { background-image: url(excel-2007-header-bg.gif); background-repeat: repeat-x; font-weight: normal; font-size: 17px; border: 1px solid #9EB6CE; border-width: 0px 1px 1px 0px; height: 17px; } TD { border: 0px; padding: 0px 4px 0px 2px; border: 1px solid #D0D7E5; border-width: 0px 1px 1px 0px; width: 100px; } TD B { border: 0px; background-color: white; font-weight: bold; } TD.heading { background-color: #E4ECF7; text-align: center; border: 1px solid #9EB6CE; border-width: 0px 1px 1px 0px; } </style> <?php $loadid = '0'; $loadTotal=0; $beginDate=$_POST["begin"]; $endDate=$_POST["end"]; $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); } $gr_total = 0; $sql="select * from (select convert(varchar,ompRequestedShipDate,101) as ShipDate,UOMPTRUCKNUMBER,UOMPDROPSEQUENCE ,ompSalesOrderID , ompCustomerOrganizationID, sum(omlOrderQuantity) as QTY, UOMPVOLUMETOTAL, sum(omlExtendedPriceBase) as total from m1_kf.dbo.SalesOrders left outer join m1_kf.dbo.SalesOrderLines on omlSalesOrderID=ompSalesOrderID where ompClosed !=-1 and ompRequestedShipDate >= '$beginDate' and ompRequestedShipDate <= '$endDate' and ompCustomerPO not like '%void%' and UOMPTRUCKNUMBER !='' group by ompRequestedShipDate,UOMPTRUCKNUMBER,UOMPDROPSEQUENCE ,ompSalesOrderID,ompCustomerOrganizationID,UOMPVOLUMETOTAL) as cte order by UOMPTRUCKNUMBER ,UOMPDROPSEQUENCE,ompSalesOrderID,ompCustomerOrganizationID" ; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } function ShowDetail($row){ echo "<tr><td>".$row['ShipDate']."</td>"; echo "<td>" .$row['UOMPDROPSEQUENCE']."</td>"; echo "<td>" .$row['ompSalesOrderID']."</td>"; echo "<td>" .$row['ompCustomerOrganizationID']."</td>"; echo "<td>" .round($row['QTY'],0)."</td>"; echo "<td>" .($row['UOMPVOLUMETOTAL'])."</td>"; echo "<td align = 'right'>" .$row['total']."</td>"; } //**************************************************************** while ($row = odbc_fetch_array($result)) { echo "<table>"; echo "<tr style='background:#82fbfd'>"; if($row['UOMPTRUCKNUMBER'] != $loadid) { echo '<th>'.$row['UOMPTRUCKNUMBER']."</th>"; echo "<th>Drop</th>"; echo "<th>OrderID</th>"; echo "<th>CustID</th>"; echo "<th>QTY</th>"; echo "<th>Cubes</th>"; echo "<th>Total</th></tr>"; $TotalPcs=0; $loadTotal=0; } ShowDetail($row); $loadid = $row['UOMPTRUCKNUMBER']; $TotalPcs+=round($row['QTY'],0); $loadTotal +=$row['total']; } echo "</table>"; //****************************************************************** ?> Quote Link to comment https://forums.phpfreaks.com/topic/312462-hey-phpfreaks-i-need-some-help-creating-a-row-at-the-bottom-of-each-uomptrucknumber-to-show-a-total-for-loadtotal/ Share on other sites More sharing options...
Barand Posted April 12, 2021 Share Posted April 12, 2021 Not much modification required. I've commented the changes needed. while ($row = odbc_fetch_array($result)) { echo "<table>"; echo "<tr style='background:#82fbfd'>"; if($row['UOMPTRUCKNUMBER'] != $loadid) { if ($loadid != 0) { # ADD // OUTPUT PREVIOUS TRUCK TOTAL HERE # ADD } # ADD echo '<th>'.$row['UOMPTRUCKNUMBER']."</th>"; echo "<th>Drop</th>"; echo "<th>OrderID</th>"; echo "<th>CustID</th>"; echo "<th>QTY</th>"; echo "<th>Cubes</th>"; echo "<th>Total</th></tr>"; $TotalPcs=0; $loadTotal=0; } ShowDetail($row); $loadid = $row['UOMPTRUCKNUMBER']; $TotalPcs+=round($row['QTY'],0); $loadTotal +=$row['total']; } // OUTPUT TOTALS FOR LAST TRUCK HERE # ADD Quote Link to comment https://forums.phpfreaks.com/topic/312462-hey-phpfreaks-i-need-some-help-creating-a-row-at-the-bottom-of-each-uomptrucknumber-to-show-a-total-for-loadtotal/#findComment-1585764 Share on other sites More sharing options...
kat35601 Posted April 12, 2021 Author Share Posted April 12, 2021 SHow the first and last totals $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } function ShowDetail($row){ echo "<tr><td>".$row['ShipDate']."</td>"; echo "<td>" .$row['UOMPDROPSEQUENCE']."</td>"; echo "<td>" .$row['ompSalesOrderID']."</td>"; echo "<td>" .$row['ompCustomerOrganizationID']."</td>"; echo "<td>" .round($row['QTY'],0)."</td>"; echo "<td>" .($row['UOMPVOLUMETOTAL'])."</td>"; echo "<td align = 'right'>" .$row['total']."</td>"; } //**************************************************************** while ($row = odbc_fetch_array($result)) { echo "<table>"; echo "<tr style='background:#82fbfd'>"; if($row['UOMPTRUCKNUMBER'] != $loadid) { if ($loadid != 0) { echo "<th>$loadTotal</th>"; } echo '<th>'.$row['UOMPTRUCKNUMBER']."</th>"; echo "<th>Drop</th>"; echo "<th>OrderID</th>"; echo "<th>CustID</th>"; echo "<th>QTY</th>"; echo "<th>Cubes</th>"; echo "<th>Total</th></tr>"; $TotalPcs=0; $loadTotal=0; } ShowDetail($row); $loadid = $row['UOMPTRUCKNUMBER']; $TotalPcs+=round($row['QTY'],0); $loadTotal +=$row['total']; } echo "</table>"; //****************************************************************** echo "<td>$loadTotal</td>"; ?> Quote Link to comment https://forums.phpfreaks.com/topic/312462-hey-phpfreaks-i-need-some-help-creating-a-row-at-the-bottom-of-each-uomptrucknumber-to-show-a-total-for-loadtotal/#findComment-1585769 Share on other sites More sharing options...
Barand Posted April 12, 2021 Share Posted April 12, 2021 Most people would output the totals in separate rows Quote Link to comment https://forums.phpfreaks.com/topic/312462-hey-phpfreaks-i-need-some-help-creating-a-row-at-the-bottom-of-each-uomptrucknumber-to-show-a-total-for-loadtotal/#findComment-1585772 Share on other sites More sharing options...
kat35601 Posted April 12, 2021 Author Share Posted April 12, 2021 Yes I would like them on separate rows and I would like a total for each truck not just the first one and last one. Thanks Quote Link to comment https://forums.phpfreaks.com/topic/312462-hey-phpfreaks-i-need-some-help-creating-a-row-at-the-bottom-of-each-uomptrucknumber-to-show-a-total-for-loadtotal/#findComment-1585776 Share on other sites More sharing options...
kat35601 Posted April 12, 2021 Author Share Posted April 12, 2021 I did get the totals on there now how would I move them to the bottom of each truck and put them under the totals. See attached while ($row = odbc_fetch_array($result)) { echo "<table>"; echo "<tr style='background:#82fbfd'>"; if($row['UOMPTRUCKNUMBER'] != $loadid) { echo '<th>'.$row['UOMPTRUCKNUMBER']."</th>"; echo "<th>Drop</th>"; echo "<th>OrderID</th>"; echo "<th>CustID</th>"; echo "<th>QTY</th>"; echo "<th>Cubes</th>"; echo "<th>Total</th>"; echo "<td><br>$loadTotal</td></tr>"; $TotalPcs=0; $loadTotal=0; } ShowDetail($row); $loadid = $row['UOMPTRUCKNUMBER']; $TotalPcs+=round($row['QTY'],0); $loadTotal +=$row['total']; }echo "<td>$loadTotal</td>"; echo "</table>"; Quote Link to comment https://forums.phpfreaks.com/topic/312462-hey-phpfreaks-i-need-some-help-creating-a-row-at-the-bottom-of-each-uomptrucknumber-to-show-a-total-for-loadtotal/#findComment-1585777 Share on other sites More sharing options...
Barand Posted April 12, 2021 Share Posted April 12, 2021 try echo "<table>"; while ($row = odbc_fetch_array($result)) { if($row['UOMPTRUCKNUMBER'] != $loadid) { if ($loadid != 0) { echo "<tr style='background:#eee'> <td colspan='4'>TOTAL</td> <td>$TotalPcs</td> <td> </td> <td>$LoadTotal</td> </tr>"; } echo "<tr style='background:#82fbfd'>"; echo '<th>'.$row['UOMPTRUCKNUMBER']."</th>"; echo "<th>Drop</th>"; echo "<th>OrderID</th>"; echo "<th>CustID</th>"; echo "<th>QTY</th>"; echo "<th>Cubes</th>"; echo "<th>Total</th></tr>"; $TotalPcs=0; $loadTotal=0; } ShowDetail($row); $loadid = $row['UOMPTRUCKNUMBER']; $TotalPcs+=round($row['QTY'],0); $loadTotal +=$row['total']; } echo "<tr style='background:#eee'> <td colspan='4'>TOTAL</td> <td>$TotalPcs</td> <td> </td> <td>$LoadTotal</td> </tr> </table> "; Quote Link to comment https://forums.phpfreaks.com/topic/312462-hey-phpfreaks-i-need-some-help-creating-a-row-at-the-bottom-of-each-uomptrucknumber-to-show-a-total-for-loadtotal/#findComment-1585778 Share on other sites More sharing options...
kat35601 Posted April 12, 2021 Author Share Posted April 12, 2021 see atached please Quote Link to comment https://forums.phpfreaks.com/topic/312462-hey-phpfreaks-i-need-some-help-creating-a-row-at-the-bottom-of-each-uomptrucknumber-to-show-a-total-for-loadtotal/#findComment-1585782 Share on other sites More sharing options...
Barand Posted April 12, 2021 Share Posted April 12, 2021 Sorry, looks like I mis-spelled it. Quote Link to comment https://forums.phpfreaks.com/topic/312462-hey-phpfreaks-i-need-some-help-creating-a-row-at-the-bottom-of-each-uomptrucknumber-to-show-a-total-for-loadtotal/#findComment-1585783 Share on other sites More sharing options...
kat35601 Posted April 13, 2021 Author Share Posted April 13, 2021 The total is only on the first and the last. Quote Link to comment https://forums.phpfreaks.com/topic/312462-hey-phpfreaks-i-need-some-help-creating-a-row-at-the-bottom-of-each-uomptrucknumber-to-show-a-total-for-loadtotal/#findComment-1585803 Share on other sites More sharing options...
Barand Posted April 13, 2021 Share Posted April 13, 2021 Truck numbers beginning with "F" evaluate to (int)0 Change if ($loadid != 0) { to if ($loadid !== 0) { Quote Link to comment https://forums.phpfreaks.com/topic/312462-hey-phpfreaks-i-need-some-help-creating-a-row-at-the-bottom-of-each-uomptrucknumber-to-show-a-total-for-loadtotal/#findComment-1585804 Share on other sites More sharing options...
kat35601 Posted April 13, 2021 Author Share Posted April 13, 2021 Perfect, Thanks Quote Link to comment https://forums.phpfreaks.com/topic/312462-hey-phpfreaks-i-need-some-help-creating-a-row-at-the-bottom-of-each-uomptrucknumber-to-show-a-total-for-loadtotal/#findComment-1585806 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.