Urgent help please

[stebut05]

Hi all,

I have a problem counting or looping through a variable and i am hoping you can help. Really appreciate any help with this problem as i need it resolved for tomorrow morning.

I need to produce a report that lists shipping details and counts the total product weight of each order on the report.

	$query = "SELECT Orders.order_id, Orders.dispatch_date, Customers.customer_title, Customers.customer_firstname, Customers.customer_lastname, Product_Description.product_weight,
					 Shipping.s_company, Shipping.s_building, Shipping.s_address_line_one, Shipping.s_address_line_two, Shipping.s_city, Shipping.s_county, 
					 Shipping.s_country, Shipping.s_post_code, Shipping.s_telephone, Shipping.s_comments
			  FROM Shipping, Customers, Orders, Product_Description, Order_Details 
			  WHERE Product_Description.product_id = Order_Details.product_id AND Order_Details.order_id = Orders.order_id AND Shipping.order_id = Orders.order_id AND Customers.customer_id = Orders.customer_id AND dispatch_date = '$dispatchdate'";
	$results = mysql_query($query) or die(mysql_error());
	$rows = mysql_fetch_assoc($results);

That is my query, i need to be able to calculate the total weight of products ordered, but it seems to be just counting all the weights for all the orders

Here is my html

      <?php if ($rows > 0) { // Show if recordset not empty ?>
      <table width="574">
	  <?php do { ?>
        <tr>
          <td width="104" height="20" class="headingbackgroundtwo">Order Number </td>
          <td width="120" class="headingbackground"><?php echo $rows['order_id']; ?></td>
          <td width="106">&nbsp;</td>
          <td colspan="2" class="headingbackgroundtwo">Shipping Details </td>
        </tr> 
        <tr>
          <td height="22" class="headingbackgroundtwo">Customer</td>
          <td class="headingbackground"><?php echo $rows['customer_title']; ?>&nbsp;<?php echo $rows['customer_firstname']; ?>&nbsp;<?php echo $rows['customer_lastname']; ?></td>
          <td>&nbsp;</td>
          <td width="108" class="headingbackgroundtwo">Company</td>
          <td width="112" class="headingbackground"><?php echo $rows['s_company']; ?></td>
        </tr>
        <tr>
          <td height="22" class="headingbackgroundtwo">Product Weight </td>
          <td class="headingbackground">

<?php
$weight = $rows['product_weight'];
$totalweight += $weight;
echo $totalweight;
?>
</td>
<td>&nbsp;</td>
<td class="headingbackgroundtwo">Building</td>
<td class="headingbackground"><?php echo $rows['s_building']; ?></td>
</tr>
<tr>
<td height="22">&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td class="headingbackgroundtwo">Address One </td>
<td class="headingbackground"><?php echo $rows['s_address_line_one']; ?></td>
</tr>
<tr>
<td height="22" colspan="2" class="headingbackgroundtwo">Shipping Comments</td>
<td>&nbsp;</td>
<td class="headingbackgroundtwo">Address Two </td>
<td class="headingbackground"><?php echo $rows['s_address_line_two']; ?></td>
</tr>
<tr>
<td colspan="2" rowspan="4" class="headingbackground"><?php echo $rows['s_comments']; ?></td>
<td height="22">&nbsp;</td>
<td class="headingbackgroundtwo">City</td>
<td class="headingbackground"><?php echo $rows['s_city']; ?></td>
</tr>
<tr>
<td height="22">&nbsp;</td>
<td class="headingbackgroundtwo">County</td>
<td class="headingbackground"><?php echo $rows['s_county']; ?></td>
</tr>
<tr>
<td height="22">&nbsp;</td>
<td class="headingbackgroundtwo">Post Code </td>
<td class="headingbackground"><?php echo $rows['s_post_code']; ?></td>
</tr>
<tr>
<td height="22">&nbsp;</td>
<td class="headingbackgroundtwo">Telephone</td>
<td class="headingbackground"><?php echo $rows['s_telephone']; ?></td>
</tr>
<tr>
<td height="22" colspan="5">---------------------------------------------------------------------------------------------------------------------------------------------</td>
</tr>
<?php } while ($rows = mysql_fetch_assoc($results)); ?>

Really appreciate any help, advice given thanks

Regards,

Steven

[paulnaj]

Hi,

Not sure what's wrong with your code ... try this pared-down version and see what happens ...

<?php
$query = "
SELECT 	Orders.order_id, Orders.dispatch_date
	, 	Customers.customer_title, Customers.customer_firstname, Customers.customer_lastname
	, 	Product_Description.product_weight
	,	Shipping.s_company, Shipping.s_building
	, 	Shipping.s_address_line_one, Shipping.s_address_line_two, Shipping.s_city, Shipping.s_county
	,   Shipping.s_country, Shipping.s_post_code, Shipping.s_telephone, Shipping.s_comments
FROM 	Shipping, Customers, Orders, Product_Description, Order_Details 
WHERE Product_Description.product_id = Order_Details.product_id
	AND Order_Details.order_id = Orders.order_id
	AND Shipping.order_id = Orders.order_id
	AND Customers.customer_id = Orders.customer_id
	AND dispatch_date = '".$dispatchdate."'
";

$results = mysql_query($query) or die(mysql_error());
$num_rows = mysql_num_rows($results);
if(0 <$num_rows){
	$total_weight = 0;
	echo 'Start: total_weight = 0';
	while($rows = mysql_fetch_assoc($results)){
		$total_weight += $rows['product_weight'];
		echo '<br> + ['.$rows['product_weight'].'] = ['.$total_weight.']';
	}
}
?> 

Paul 🙂

PS. If it's still not giving you what you want, try removing all the 'detail' table from your query, leaving exactly those tables required to make this calculation. Once you've got that sorted, you can put the details back in.