[Resolved] Nested while statment calculations?

[move3rd]

In the code below I have while statment within a while statment. For each row in my store_inventory table it clalculates the '$balance' value for every row in my store_order_inventory with the coresponding product column value.

In the first while statment I set the starting vlaues to 0 so that the value for the previous product is not added to the next product. My problem is the balance value for the last product displayed is not calculated since the if statment within the secound while stament does not return true and so the value displayed is '0'.

////////// Start Account Summary	
	if(!isset($_GET['$accounts_a']) && !isset($_POST['$accounts_a']))
	{				  

		$sql_inventory = "SELECT * ".
        "FROM `store_inventory` WHERE `cat_id` = '".$_SESSION['artist_id']."'";
        $result_inventory = mysql_query($sql_inventory);

    // Report Db errors
    if (!$result_inventory) {
    $content = "Could not successfully run query ($sql) from DB: " . mysql_error();
                              }

    while ($row_inventory = mysql_fetch_array($result_inventory))
                  {


				  $title = $row_inventory['title'];
				  $product = $row_inventory['product'];
				  $promotion = $row_inventory['promotion'];
				  $price = $row_inventory['price'];




 // Set starting values
$balance = '0';
$amount_recieved = '0';
$order_quantity = '0';
$release_profit = '0';
$release_sales = '0';}

	$sql_order_inv = "SELECT * ".
    "FROM `store_order_inv` WHERE `product` = '$product'";
    $result_order_inv = mysql_query($sql_order_inv);

    // Report Db errors
    if (!$result_order_inv) {
    $content = "Could not successfully run query ($sql) from DB: " . mysql_error();
                              }

 while ($row_order_inv = mysql_fetch_array($result_order_inv))
             {
			 $quantity = $row_order_inv['quantity'];
			 $status = $row_order_inv['status'];
			 $payroll_status = $row_order_inv['artist_payroll_status'];
			 $order_price = $row_order_inv['price'];

	if($status == 'Complete'){
	     $release_price = $order_price - $fee; // Claculate profit per CD for release
		 $profit = $release_price * $quantity; // calculate oder profit						   
	     $release_profit = $release_profit + $profit; // Claculate total profit for release

		 $release_sales = $release_sales + $quantity;

		if($payroll_status == 'Pending' || $payroll_status == 'Queued')
					{
					 $pay = $order_price - $fee;
	                 $ow = $pay * $quantity;
				     $balance = $balance + $ow;
					} 

										    } // End if($status == 'Complete')

		           if($payroll_status == 'Queued'){$transfer_in_progress = 'y';} 

				   }// End FROM `store_order_inv`


			}// End FROM `store_inv`

Sorry I couldnt think of a better way to explain my problem. Hope somone can help...:queasy:

[Weedpacket]

Well ... you've actually got three if statements there ... I presume you mean one of the first two, but which?

To be honest, I think all this could be handled by SQL itself. I'm not sure of the precise statement; since I'm not clear about what the existing code is doing, I can't make a translation, but I am certain that a bit more planning in the SQL (rather than just "SELECT *") will simplify the PHP a fair bit. For example, I guess the $balance for a given row could be found by

"SELECT
	SUM(((price - $fee) * quantity)) AS balance
FROM
	store_order_inv
WHERE
	product='$product'
	AND (artist_payroll_status='Pending'
		OR artist_payroll_status='Queued')
	AND status='Complete'"

.That's just a sketch. I can see there's a bit more going on in there which would need to be accommodated - calling on constructs like "GROUP BY" and joins and whatnot - but that does help to illustrate how SQL and databases are designed for making calculations like these.

[move3rd]

Thanks Weedpacket

I think it's best for me to start from scratch with this.