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> Quote Link to comment 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 Quote Link to comment 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> Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted October 22, 2015 Solution 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) 1 Quote Link to comment 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.