kat35601 Posted October 22, 2015 Share Posted October 22, 2015 I want to get a grand total for the results of price. I do not want to do this in sql I would like to know how to do it in PHP Please. <html> <head> <title>Production</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; } 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> <h1>Kith Furniture </h1> <?php $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); } $sql=" SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, tdate)) AS DATE ,ISNULL(trans, 'NON') AS trans ,ISNULL(Transactions.item, Snumbers.item) AS item ,count(serial) AS qty ,tuser ,convert(varchar,cast(sum(M1_KF.dbo.PartUnitSalePrices.imhUnitSalePrice) as money),1) as TotalPrice FROM Orbedata.dbo.SNumbers LEFT OUTER JOIN OrbeData.dbo.Transactions ON snum = serial INNER JOIN M1_KF.dbo.PartUnitSalePrices ON Orbedata.dbo.transactions.item = M1_KF.dbo.PartUnitSalePrices.imhPartID WHERE CONVERT(DATE, tdate) = CONVERT(DATE, Getdate()) AND trans = 'fpr' GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, tdate)) ,ISNULL(trans, 'NON') ,ISNULL(Transactions.item, Snumbers.item) ,tuser ,Orbedata.dbo.transactions.qty order by tuser,item "; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } echo "Produced Today ". date("m-d-Y") ; echo "<table><tr>"; echo "<th>Line</th>"; echo "<th> </th>"; echo "<th>Item</th>"; echo "<th> </th>"; echo "<th>Qty</th>"; echo "<th>Price</th></tr>"; while (odbc_fetch_row($result)) { $scanner=odbc_result($result,"tuser"); $Item=odbc_result($result,"item"); $Qty=odbc_result($result,"qty"); $Price=odbc_result($result,"TotalPrice"); echo "<tr><td>$scanner</td>"; echo "<td> ◃ </td>"; echo "<td>$Item</td>"; echo "<td> ▹ </td>"; echo "<td>$Qty</td>"; echo "<td align='right'>$Price</td></tr>"; } odbc_close($connect); ?> </body> </html> Link to comment https://forums.phpfreaks.com/topic/298778-grandtotal-for-the-field-price/ Share on other sites More sharing options...
Barand Posted October 22, 2015 Share Posted October 22, 2015 $grandTotal = 0; // initialise to 0 while (odbc_fetch_row($result)) { $scanner=odbc_result($result,"tuser"); $Item=odbc_result($result,"item"); $Qty=odbc_result($result,"qty"); $Price=odbc_result($result,"TotalPrice"); $grandTotal += $price; // accumulate price echo "<tr><td>$scanner</td>"; echo "<td> ◃ </td>"; echo "<td>$Item</td>"; echo "<td> ▹ </td>"; echo "<td>$Qty</td>"; echo "<td align='right'>$Price</td></tr>"; } echo "Grand Total: $grandTotal"; // output grand total Link to comment https://forums.phpfreaks.com/topic/298778-grandtotal-for-the-field-price/#findComment-1524050 Share on other sites More sharing options...
kat35601 Posted October 22, 2015 Author Share Posted October 22, 2015 Thanks-----The total is not correct. What would be my problem. Produced Today 10-22-2015 Grand Total: 6371.88 Line Item Qty Price UL2 ◃ 110-03 ▹ 4 520.00 UL2 ◃ 115-02 ▹ 194 9,310.06 UL2 ◃ 115-04 ▹ 101 6,361.99 UL2 ◃ 115-05 ▹ 101 7,371.99 UL2 ◃ 115-12 ▹ 8 824.00 UL2 ◃ 171-05 ▹ 3 254.97 UL2 ◃ 175-02 ▹ 1 54.99 UL2 ◃ 175-05 ▹ 2 169.98 UL2 ◃ 175-12 ▹ 48 4,560.00 UL2 ◃ 190-03 ▹ 9 1,170.00 UL2 ◃ 190-12 ▹ 6 618.00 UL2 ◃ 193-02 ▹ 59 2,831.41 UL2 ◃ 193-04 ▹ 37 1,849.63 UL2 ◃ 197-04 ▹ 150 7,498.50 UL2 ◃ 197-05 ▹ 136 8,158.64 UL2 ◃ 269-05 ▹ 4 419.96 UL2 ◃ 269-73 ▹ 1 95.00 UL2 ◃ 275-02 ▹ 59 3,539.41 UL2 ◃ 275-05 ▹ 101 10,098.99 UL2 ◃ 275-12 ▹ 86 9,890.00 UL2 ◃ 405-02 ▹ 2 139.98 UL2 ◃ 485-12 ▹ 2 280.00 UL3 ◃ 175-33 ▹ 33 1,155.00 UL3 ◃ 175-53 ▹ 35 1,225.00 UL3 ◃ 230-53 ▹ 1 42.00 UL3 ◃ 269-76 ▹ 17 935.00 UL3 ◃ 405-50 ▹ 2 90.00 UL3 ◃ 405-60 ▹ 14 770.00 UL3 ◃ 405-FP ▹ 3 90.00 UL3 ◃ 485-01 ▹ 96 4,800.00 UL3 ◃ 485-50 ▹ 81 3,645.00 UL3 ◃ 485-51 ▹ 43 1,720.00 UL3 ◃ 485-52 ▹ 74 1,850.00 UL3 ◃ 485-60 ▹ 18 990.00 <html> <head> <title>Production</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; } 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> <h1>Kith Furniture </h1> <?php $grandTotal = 0; $connect =odbc_connect("Removed"); if(!$connect) { exit("Connection Failed: " . $connect); } $sql=" SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, tdate)) AS DATE ,ISNULL(trans, 'NON') AS trans ,ISNULL(Transactions.item, Snumbers.item) AS item ,count(serial) AS qty ,tuser ,convert(varchar,cast(sum(M1_KF.dbo.PartUnitSalePrices.imhUnitSalePrice) as money),1) as TotalPrice FROM Orbedata.dbo.SNumbers LEFT OUTER JOIN OrbeData.dbo.Transactions ON snum = serial INNER JOIN M1_KF.dbo.PartUnitSalePrices ON Orbedata.dbo.transactions.item = M1_KF.dbo.PartUnitSalePrices.imhPartID WHERE CONVERT(DATE, tdate) = CONVERT(DATE, Getdate()) AND trans = 'fpr' GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, tdate)) ,ISNULL(trans, 'NON') ,ISNULL(Transactions.item, Snumbers.item) ,tuser ,Orbedata.dbo.transactions.qty order by tuser,item "; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } echo "Produced Today ". date("m-d-Y") ; echo "<table><tr>"; echo "<th>Line</th>"; echo "<th> </th>"; echo "<th>Item</th>"; echo "<th> </th>"; echo "<th>Qty</th>"; echo "<th>Price</th></tr>"; while (odbc_fetch_row($result)) { $scanner=odbc_result($result,"tuser"); $Item=odbc_result($result,"item"); $Qty=odbc_result($result,"qty"); $Price=odbc_result($result,"TotalPrice"); $grandTotal += $Price; echo "<tr><td>$scanner</td>"; echo "<td> ◃ </td>"; echo "<td>$Item</td>"; echo "<td> ▹ </td>"; echo "<td>$Qty</td>"; echo "<td align='right'>$Price</td></tr>"; } echo "Grand Total: $grandTotal"; odbc_close($connect); ?> </body> </html> Link to comment https://forums.phpfreaks.com/topic/298778-grandtotal-for-the-field-price/#findComment-1524054 Share on other sites More sharing options...
Barand Posted October 22, 2015 Share Posted October 22, 2015 Are you formatting the price fields with commas in the query? If so 1,850.00 is treated as 1 by PHP (up to the first non-numeric character) Link to comment https://forums.phpfreaks.com/topic/298778-grandtotal-for-the-field-price/#findComment-1524065 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.