Calculation in PHP form error

[gavin1512]

Good Evening All,

 

I am working on an internal order system and I am currently trying to input some reporting features, I am using a mySQL database and I have got the report working so its groups the orders by department and counts the number of order and now I am trying to get the total spending, unfortuntely this field is not in the database so for each individual order I am going into each individual product and multiplying price by quantity. Here is the code I am trying to use, but the total are coming up as £0 presently:

 

.....$date1 = $_POST['date1'];
$date2 = $_POST['date2'];


			$query="select COUNT(orders.orderid) as num_orders, department from productorder, orders, employee where productorder.orderid = orders.orderid AND employee.username = orders.username AND `date` BETWEEN '" . $date1 . "' AND '" . $date2 . "' Group BY department Order BY num_orders DESC";
			$result=@mysql_query($query,$connection)
			or die("Unable to perform query<br>$query");......

<?php
while($row= mysql_fetch_array($result))
{
?>
                          <tr> 
                            <td><?php echo $row['department']?></td> 
                            <td><?php echo $row['num_orders'] ?></td>
						<?php
			$query2="select * from productorder, orders, employee where productorder.orderid = orders.orderid and employee.username = orders.username AND employee.department= '".$row['department']."' and productorder.orderid= '".$row['orderid']."'";
			$result2=@mysql_query($query2,$connection)
			or die("Unable to perform query<br>$query2");

			while($row2=mysql_fetch_array($result2))
			{ ?>
						<?php $total = $row2['quantity'] * $row2['price'] ?>
						<?php $total2 = $total2 + $total ?>
				  <?php
			}
?>


                            <td>£<?php echo $total2 ?></td>
                            <td><?php $avg = $total2/$row['num_orders'] ?>£<?php echo $avg ?></td>
                          </tr>
					  <?php
	}
?>