Calculate Subtotal

[coxdabd]

Hi all, I'm trying to calculate the subtotal of items in my database. It's doesn't seem to be working. Any help would be appreciated as always.

function dupSub(){
    while ($rows=mysql_fetch_array($prods)){
        $price = $rows['product_price'];
        $counter++;
    }
    echo $counter;
} 

[AnthonySterling]

Are you sure that's the 'unaltered' function you're using? 'cause it makes no sense.

You could ask the DB directly for the sub total though.

SELECT SUM(product_price) AS 'sub_total' FROM order WHERE id = 1

[coxdabd]

Hey Anthony, I also need to calculate the quantity of items. Any idea what I could do? Each quantity and price is stored in the database. Your above solution would be perfect if I did not need to work out quantities - apologies I should have mentioned that.

[coxdabd]

Actually, I'm, wondering whether I should calculate the total of each row (price x quantity) and enter that into the database as well as 'subtotal' and then just use your solution to display the grand total.

[Cups]

function dupSub(){

$counter = 0 ;
$price = 0 ;
$total = array();

    while ($rows=mysql_fetch_array($prods)){

        $price += $rows['product_price'];

        $counter++;

    }

$total['price'] = $price ;
$total['count'] = $counter ;
    
return $total ;

} 

// usage

$totals = dupSub();

// final formatting for price should be done here, money_format() etc
echo $totals['count'] . ' items, total price: ' . $totals['price'] ; 

#untested#

Yeah, you should be using the db but if you cannot, then something similar to the above might give you a work-around - but be very careful about how you are storing price in your database, try and use a number rather than text/varchar.

(must be a theme today)

http://www.php.net/manual/en/function.money-format.php

[coxdabd]

Hey Cups, thank you for your reply. Think that should do that job. I'm guessing the best way to store the price in the database is to use DECIMAL 10,2? Seems to be doing the job ok

[Cups]

I think much depends upon what kind of processing you intend to do on the numbers later. Some use an integer and divide by 100 just prior to display ( ie 12345 - 123.45) or use a float with 2 decimal points so that entering 1 results in 1.00.

What is best for 100% accurate addition/division I am not entirely sure, as long as you aren't using varchar or text you should be OK - that was my main message.

[coxdabd]

Ok, perfect, I think all should be ok then. Thank you again for your help, appreciated

[Cups]

select count(*) as cnt, sum(val) as total from money;

I did not know that would work, based off that link in previous post from today.

The figures don't seem to work out (it is using varchars) but in principle, if you are using numbers then it should.

Well...

[AnthonySterling]

SELECT SUM(product_price * product_quantity) AS 'sub_total' FROM order WHERE id = 1

Sorry, mad rush today.

[r937]

What is best for 100% accurate addition/division I am not entirely sure...

let me help you out a bit on that -- FLOAT is ~not~ an appropriate datatype if you want accuracy

[Cups]

Ah do tell.

If you were storing a money value in a database and you sometimes wanted penny-accuracy what would you store $12.34 as then?

1234 cents?

And then leave the formatting as a final domain-specific decision? (round to dollars, convert to pounds and so on)

[Cups]

Ah do tell.

If you were storing a money value in a database and you sometimes wanted penny-accuracy what would you store $12.34 as then?

1234 cents?

And then leave the formatting as a final domain-specific decision? (round to dollars, convert to pounds and so on)

[r937]

me? i'd use DECIMAL(n,2)

some apps require DECIMAL(n,4), though, for eighths of a cent

what would you do, store 123400?

that's too hackish for me

[Cups]

I really have no idea, hence I asked.

All I know is the incredible problems I faced with lat/lng coordinates stored as floats and math with PHP - that was why I was quite guarded in my advice really.

[r937]

latitude and longitude are just fine as floats, because the inherent inaccuracy of floats doesn't come into play (the error would be too small to make a difference in geographical location)