Page 1 of 1

Why??? Trying to SUM query and discount by 10%

Posted: Thu Sep 21, 2006 2:35 pm
by rocknrisk
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hey guys,

Help me please... I'm trying to run a query asking for the sum of a table field that meets another criteria. I then need to remove 10% from that figure and display it...

Here's my code...

Code: Select all

while ($arrProductDetail = mysql_fetch_array($rstProductDetail)) {

		$numSet = $arrProductDetail ['idsSet'];

		if ($numSet < 101)  {
			echo '<tr>';
			echo '<td class="style8">The above piece is classed as a '.$txtSetName.' and is not part of any set';
			echo '<td></tr>';
		} else {


          // Work out price for complete set which is 10% off sum of set totals
			$query_rstSetPrice = ' SELECT curNewPrice
			FROM mtbljewelry
			WHERE numSet = '.$numSet;
			$rstSetPrice = mysql_query($query_rstSetPrice, $damsel) or die(mysql_error());
			$arrSetPrice = mysql_fetch_array($rstSetPrice);
			$numFullSetPrice = array_sum($arrSetPrice);
			$numDiscountedSetPrice = ($numFullSetPrice * 0.9);
			$numFormatedSetPrice = number_format($numDiscountedSetPrice, 2);
			
			echo '$'.$numFormatedSetPrice;

		}	// end MATCHED SET Setup

	}
	mysql_free_result($rstProductDetail);
In my test data... It should add:
64.95+74.95+39.95+49.95+69.95+74.95

Which equals:
374.70

Minus 10% equals:
374.70 - 37.47 = $337.23

Code: Select all

I GET $116.91
What's going on. please? I've shown that it all happens within a loop... that may be that problem?

Thanks in advance.
Clinton


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Thu Sep 21, 2006 2:44 pm
by timvw

Code: Select all

SELECT FORMAT(SUM(curNewPrice) * 0.9, 2) AS amount
FROM mtbljewelry
WHERE  numSet IN ( .... );
to generate the ... in the IN clause i suggest you use something as implode...

Code: Select all

$numSets = new array(10, 9, 142, 14);
$mysql_in = implode(', ', $numSets);

Posted: Thu Sep 21, 2006 3:38 pm
by rocknrisk
Thank you timvw.

Not sure I understand this though.

I get the SQL statement, but:

Tried it but doesn't seem to work. Why implode()?

Thanks,
Clinton