While in while keeps adding up

[esmeralda]

Hey people!

 

So I'm working on a php code to calculate the sum of the prices and tax of the products belonging to a bill.   

So I first collect all the bills from 'facturering', and I use a 'while'  to print all the bills and just one.

Second, I collect all the prices and tax (btw) of the products on the bill, using the bills Id. There can be more prices and tax in one bill, so I need to get the sum of the prices and tax . Therefore, I use a second while.

 

The first print is lovely. It gives the sum of the prices and of the tax for the first bill.

But the prices of the first bill gets add up to the prices of the second bill, so that the outcome is not the price of the second bill, but the sum of the prices of the first and second bill. That's not what I want.

 

I've looked on the internet, but can't find anything that can help me further.

I gues it is not ok to do a while loop in another while loop. But what else can I use?

Any suggestions?

 

This is my code:

                                    $query = "SELECT * FROM `facturering` ORDER BY `factuurdatum` DESC;";

                                        $result = @mysql_query( $query );

 

                                        while( $factuur = @mysql_fetch_assoc( $result ) )

{

$factuur_id = $factuur['id'];

 

$bedrag = 0;

                                                $btw = 0;

$query_bedrag = "SELECT * FROM `producten` WHERE `factuur_id` = '$factuur_id';";

$result_bedrag = @mysql_query( $query_bedrag );

while ( $row_bedrag = @mysql_fetch_array( $result_bedrag ) )

{

$bedrag = $row_bedrag['bedrag'];

$totaalbedrag += $bedrag;

 

$btw = $row_bedrag['BTW'];

$totaalbedragbtw += $btw;

}

 

$bedragexclusief1 = number_format( $totaalbedrag, 2 , ',' , '.' );

echo "$bedragexclusief1<br />";

 

$bedragbtw1 = number_format( $totaalbedragbtw, 2 , ',' , '.' );

echo "$bedragbtw1<br />";

 

}

[ToonMariner]

be careful!!!!

 

querying the database inside any kind of loop is not efficient OR desirable - some servers are set up to limit the number of queries per page...

 

grab all the info you will need into arrays and loop through them instead..

 

anyway your code...

 

you need to reset $totaalbedrag and $totaalbedragbtw once they have been used....

 

$query = "SELECT * FROM `facturering` ORDER BY `factuurdatum` DESC;";
                                        $result = @mysql_query( $query );
   
                                        while( $factuur = @mysql_fetch_assoc( $result ) )
               {
                  $factuur_id = $factuur['id'];

$totaalbedrag = 0;
$totaalbedragbtw = 0;
                  
$bedrag = 0;
                                                $btw = 0;
                  $query_bedrag = "SELECT * FROM `producten` WHERE `factuur_id` = '$factuur_id';";
                  $result_bedrag = @mysql_query( $query_bedrag );
                  while ( $row_bedrag = @mysql_fetch_array( $result_bedrag ) )
                  {
                     $bedrag = $row_bedrag['bedrag'];
                     $totaalbedrag += $bedrag;

                     $btw = $row_bedrag['BTW'];
                     $totaalbedragbtw += $btw;
                  }   
                  
                  $bedragexclusief1 = number_format( $totaalbedrag, 2 , ',' , '.' ); 
                  echo "$bedragexclusief1
"; 

                  $bedragbtw1 = number_format( $totaalbedragbtw, 2 , ',' , '.' ); 
                  echo "$bedragbtw1
"; 
                  
               }   

[esmeralda]

wouw, it always is a small thing that keeps the code from working  :-\

Thanx voor your help, the code works greet now.

 

So loping the database is not desirable? And it is better to get arrays and then get the information from those? I'm gonne look into this, don't now yet how that's done, but I will figure it out. Thanx for this comment!

 

greetz,

Esmeralda