The goal of this code is to find the total quantity purchased for a product.

The problem I'm having is the number is almost always wrong. It's usually 6 off and I can't figure out why! Can someone help with this or provide a better solution?

$numsales is the variable that is supposed to hold the total quantity, I echo it later in the page.

$sales = mysql_query("SELECT `quantity` FROM `products` WHERE `product_id` = '{$productid}'");
while($r=mysql_fetch_array($sales))
{
$iquantity=$r["quantity"];
$numsales += ($iquantity);
}

I think because $iquantity is wraped in parentheses it is evaluating to a boolean value which will equal to only one (if quantity is not zero) if it sees it as true. Try:

$numsales += (int)$iquantity;

Still the same result, but here's an interesting fact.

The first product returns the actual results + 6. The second product returns the actual results + the first results. Here's an example:

Product Actual Results $numsales Result
1 20 26
2 20 40


Here is the entire code:


<?
$result = mysql_query
("
SELECT
products.product_name,
products.download_location_7,
products.product_price,
products.product_status,
products.download_location_8,
products.download_location_4,
products.download_location_3,
products.download_location_6,
products.product_id,
FROM
clients_users,
clients_products,
products
WHERE
clients_users.id = clients_products.client_id AND
clients_products.client_product_id = products.product_id AND
clients_users.id = $_SESSION[client_id]
");

while($r=mysql_fetch_array($result))
{

$deal=$r["product_name"];
$value=$r["download_location_7"];
$pay=$r["product_price"];
$savings = ($value - $pay);
$pstatus=$r["product_status"];
$expiration=$r["download_location_8"];
$thumbnail=$r["download_location_4"];
$productid=$r["product_id"];
$comm=$r["download_location_6"];
$promoend=$r["product_promoend"];

$sales = mysql_query("SELECT `quantity` FROM `invoice_products` WHERE `product_id` = '{$productid}'");
while($r=mysql_fetch_array($sales))
{
$iquantity=$r["quantity"];
$numsales += (int)$iquantity;
}

$payments = mysql_query("SELECT payment_amount, payment_product FROM clients_payments WHERE `payment_product` = '{$productid}'");
while($r=mysql_fetch_array($payments))
{
$ipaymentamount=$r["payment_amount"];
$numpayments += ($ipaymentamount);
}

?>
<div class="container">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="23%"><img src="<? echo $thumbnail; ?>" width="120" height="90"/></td>
<td width="77%" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td> <h3><? echo $deal ;?><? if ($expiration > $today) { ?><span class="blue"> - STILL AVAILABLE!</span><? ;} ?></h3> </td>
</tr>
<tr>
<td>
<div class="infos">
<!-- sof price -->
<div class="price">
<a href="/?pid=<? echo $productid ?>&recent=1" title="" class="btn-buynow" id="buy-now">$<? echo $pay; ?></a>
<ul>
<li><span>Value</span> $<? echo $value; ?></li>
<li><span>Pay</span> $<? echo $pay; ?></li>
<li class="last"><span>Save</span> $<? echo $savings; ?></li>
</ul>
</div></div> </td>
</tr>
</table></td>
</tr>
<tr>
<td>&nbsp;</td>
<td valign="top">&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td valign="top"><table width="68%" border="0" cellspacing="0" cellpadding="0">
<tr bgcolor="#999999">
<tr>
<td><strong>Sales</strong></td>
<td>&nbsp;</td>
</tr>
<tr bgcolor="#999999">
<td>Deals Sold </td>
<td><? echo $numsales; ?></td>
</tr>
<tr>
<td>Payout Per Product Sold </td>
<td>$<? echo ($pay - $comm); ?></td>
</tr>
<tr bgcolor="#999999">
<td>Payments Sent </td>
<td>$<? echo $numpayments; ?></td>
</tr>
<tr>
<td>Balance Remaining </td>
<td>$<? echo ($numsales * ($pay - $comm)) - $numpayments; ?></td>
</tr>
<tr>
<td><form id="client_product" name="client_product" method="post">
<label>
<input type="hidden" name="client_product" value="<? echo $productid ?>" />
<input type="submit" name="Submit" value="View Sales" />
</label>&nbsp;
</form></td>
<td>&nbsp;</td>
</tr>
</table> </td>
</tr>
</table>
</div>

You're using the same variable on both query result loops: $r

I fixed that and still get the same problem. The $numsales value is still +6 of the actual value.

Try using this debugger and see what prints at the bottom of the page:

PHP Code:

<? 
$result = mysql_query
("
SELECT 
products.product_name,
products.download_location_7,
products.product_price,
products.product_status,
products.download_location_8,
products.download_location_4,
products.download_location_3,
products.download_location_6,
products.product_id,
FROM
clients_users,
clients_products,
products
WHERE
clients_users.id = clients_products.client_id AND 
clients_products.client_product_id = products.product_id AND
clients_users.id = $_SESSION[client_id]
");
$debug = array();
for($i = 0; $r=mysql_fetch_array($result); $i++)
{
$deal=$r["product_name"];
$value=$r["download_location_7"];
$pay=$r["product_price"];
$savings = ($value - $pay);
$pstatus=$r["product_status"];
$expiration=$r["download_location_8"];
$thumbnail=$r["download_location_4"];
$productid=$r["product_id"];
$comm=$r["download_location_6"];
$promoend=$r["product_promoend"];
$debug[$i] = array(
  'product_id' => $productid,
  'quantity' => array(),
  'payment_amount' => array(),
);
$sales = mysql_query("SELECT `quantity` FROM `invoice_products` WHERE `product_id` = '{$productid}'");
while($row=mysql_fetch_array($sales))
{
$iquantity=$row["quantity"];
$numsales += (int)$iquantity;
$debug[$i]['quantity'][] = $iquantity;
}
$payments = mysql_query("SELECT payment_amount, payment_product FROM clients_payments WHERE `payment_product` = '{$productid}'");
while($row=mysql_fetch_array($payments))
{
$ipaymentamount=$row["payment_amount"];
$numpayments += ($ipaymentamount);
$debug[$i]['payment_amount'][] = $ipaymentamount;
}
?>
<div class="container">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="23%"><img src="<? echo $thumbnail; ?>" width="120" height="90"/></td>
<td width="77%" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td> <h3><? echo $deal ;?><? if ($expiration > $today) { ?><span class="blue"> - STILL AVAILABLE!</span><? ;} ?></h3> </td>
</tr>
<tr>
<td>
<div class="infos">
<!-- sof price -->
<div class="price">
<a href="/?pid=<? echo $productid ?>&recent=1" title="" class="btn-buynow" id="buy-now">$<? echo $pay; ?></a>
<ul>
<li><span>Value</span> $<? echo $value; ?></li>
<li><span>Pay</span> $<? echo $pay; ?></li>
<li class="last"><span>Save</span> $<? echo $savings; ?></li>
</ul>
</div></div> </td>
</tr>
</table></td>
</tr>
<tr>
<td>&nbsp;</td>
<td valign="top">&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td valign="top"><table width="68%" border="0" cellspacing="0" cellpadding="0">
<tr bgcolor="#999999">
<tr>
<td><strong>Sales</strong></td>
<td>&nbsp;</td>
</tr>
<tr bgcolor="#999999">
<td>Deals Sold </td>
<td><? echo $numsales; ?></td>
</tr>
<tr>
<td>Payout Per Product Sold </td>
<td>$<? echo ($pay - $comm); ?></td>
</tr>
<tr bgcolor="#999999">
<td>Payments Sent </td>
<td>$<? echo $numpayments; ?></td>
</tr>
<tr>
<td>Balance Remaining </td>
<td>$<? echo ($numsales * ($pay - $comm)) - $numpayments; ?></td>
</tr>
<tr>
<td><form id="client_product" name="client_product" method="post">
<label>
<input type="hidden" name="client_product" value="<? echo $productid ?>" />
<input type="submit" name="Submit" value="View Sales" />
</label>&nbsp;
</form></td>
<td>&nbsp;</td>
</tr>
</table> </td>
</tr>
</table>
</div>
<?php
}
?>
<pre>Debug:<br /><?php echo htmlentities(print_r($debug, true)); ?></pre>

If your first loop is not working why dont you check what is the array that the query fetches for you. It is possible that there are some additional row is also fetched along with the records.

<?php
$a=array(0=>"5",1=>"15",2=>"25");
echo array_sum($a);
?>