I am performing the following SQLSRV Query using PHP 5.6 & 7.0.29 and when I get the results I can not seem to add (SUM) the total results of "grand_total" using array_sum or even when I try to loop it.
$sql = "SELECT inv_trx.trx_date, datepart(day, datediff(day, 0, inv_trx.trx_date)/7 * 7)/7 + 1 AS WEEKNUMBER , DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,'".$datetime."'), 0))+ 1 AS WEEK_OF_MONTH, inv_trx.trx_qty, inv_trx.unit_price, inv_trx.ord_qty, item.prod_cat, inv_trx.slsman_1, SUM(inv_trx.trx_qty * inv_trx.unit_price) AS grand_total
FROM dbo.customer customer, dbo.inv_trx inv_trx, dbo.item item, dbo.ord_hedr ord_hedr
WHERE datepart(day, datediff(day, 0, inv_trx.trx_date)/7 * 7)/7 + 1 = '1' AND customer.cust_no = inv_trx.cust_no AND customer.cust_no = ord_hedr.cust_no AND inv_trx.order_no = ord_hedr.order_no AND inv_trx.item_no = item.item_no AND
inv_trx.manu_no = item.manu_no AND ((ord_hedr.ord_type='O') AND (inv_trx.trx_type='S') AND (ord_hedr.ord_class<>'M' And ord_hedr.ord_class<>'P') AND (customer.exclude_sa=0) OR (ord_hedr.ord_type='C') AND (inv_trx.trx_type='S') AND (ord_hedr.ord_class<>'M' And ord_hedr.ord_class<>'P') AND (customer.exclude_sa=0)) AND YEAR(inv_trx.trx_date) = '2018' AND MONTH(inv_trx.trx_date) = '7' GROUP BY inv_trx.trx_date, DATEPART(WEEK,inv_trx.trx_date), inv_trx.trx_qty, inv_trx.unit_price, inv_trx.ord_qty, item.prod_cat, inv_trx.slsman_1 ";
$params = array();
$result = sqlsrv_query( $conn, $sql );
if( $result === false) {
die( print_r( sqlsrv_errors(), true) );
}
while( $row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC) ) {
?> <tr>
<td><?php echo $row['trx_date']->format('m/d/Y')?></td>
<td><?php echo $row['WEEKNUMBER']?></td>
<td><?php echo $row['trx_qty']?></td>
<td><?php echo $row['unit_price']?></td>
<td><?php echo $row['ord_qty']?></td>
<td><?php echo $row['prod_cat']?></td>
<td><?php echo $row['slsman_1']?></td>
<td><?php echo $row['grand_total']?></td>
</tr>
<?php
$group = array($row['grand_total']);
print_r(array_sum($group));
//echo $row['grand_total']; //result is the same
The result of Array_sum is the same is merely echoing the grand_total itself.
example: 4.8 6 109.4 1.197 17.8 45.5 89 instead of total = 184.XX
Is this because I am already using SUM to total inv_trx.trx_qty * inv_trx.unit_price to form grand_total?
I suspect it is an issue with my array but I am not sure. Any advise would be greatly appreciated.
The query Produces the following
Trx Date Week Trx QTY Price Quantity Category Salesman Total
09/02/2018 1 1 4.8 80 MAG Mickey 4.8
09/02/2018 1 3 2 50 BOB Donald 6
09/02/2018 1 4 27.35 4 POW Goose 109.4
What I am trying to do is get the sum of total which is grand_total.
If your T-SQL statement returns rows and you want to sum the values from every row in the resultset, you need to define your array before while () {...} and then add each row's value:
<?php
...
# Statement
$sql = ' ... your statement ...';
$params = array();
$result = sqlsrv_query($conn, $sql, $params);
if ($result === false) {
die( print_r(sqlsrv_errors(), true));
}
# Results
$total = array();
while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
?>
<tr>
<td><?php echo $row['trx_date']->format('m/d/Y')?></td>
<td><?php echo $row['WEEKNUMBER']?></td>
<td><?php echo $row['trx_qty']?></td>
<td><?php echo $row['unit_price']?></td>
<td><?php echo $row['ord_qty']?></td>
<td><?php echo $row['prod_cat']?></td>
<td><?php echo $row['slsman_1']?></td>
<td><?php echo $row['grand_total']?></td>
</tr>
<?php
$total[] = $row['grand_total'];
echo 'Grand total after current row: '.array_sum($total).'<br>';
}
# Total after all rows
echo 'Grand total after all rows: '.array_sum($total).'<br>';
...
?>