SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Calculate Subtotal

    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.

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


  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.
    Code:
    SELECT SUM(product_price) AS 'sub_total' FROM order WHERE id = 1
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    PHP Code:
    function dupSub(){

    $counter ;
    $price ;
    $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

  6. #6
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

  8. #8
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, perfect, I think all should be ok then. Thank you again for your help, appreciated

  9. #9
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Code:
    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...

  10. #10
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT SUM(product_price * product_quantity) AS 'sub_total' FROM order WHERE id = 1

    Sorry, mad rush today.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Cups View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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)

  13. #13
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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)

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •