Results 1 to 7 of 7

Thread: mySQL Calculations

  1. #1
    Join Date
    Aug 2005
    Posts
    174
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default mySQL Calculations

    Hello,

    How would i go about performing this calculation using mysql?

    $TotalCost = $Price+($LineRental*12)-$Discount-$Cost;

    All of the above of fields from a table **except for TotalCost**

  2. #2
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,878
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    Er...
    Code:
    SELECT price + line_rental * 12 - discount - cost FROM some_table;
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends français | entiendo español | tôi ít hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  3. The Following User Says Thank You to Twey For This Useful Post:

    nikomou (01-19-2009)

  4. #3
    Join Date
    Aug 2005
    Posts
    174
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default

    thanks twey, but i forgot to mention that $discount is not a field in the table, and it is calculated as so:

    PHP Code:
    // Show linerental

    if($Monthsfree != "0") {
       
    $InfoString "<strong>$Monthsfree Months</strong> Free Line Rental";
       
    $Discount $Monthsfree*$LineRental;
    }


    if(
    $Monthshalf != "0") {
       
    $InfoString .= "<br /><strong>$Monthshalf Months</strong> ½ Price Line Rental";
       
    $Discount $Monthshalf*($LineRental/2);
    }

    if(
    $Cashback != "0") {
       
    $InfoString .= "<br /><strong>$Instant";
       
    $InfoString .= "&pound;$Cashback Cash Back!</strong>";
       
    $Discount $Cashback;

    knew it wasnt as easy as u made out! thanks for your help...

  5. #4
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,878
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    That <br /> looks like a) pseudo-XHTML and b) element abuse for presentational purposes. Take it out — wrap the line in a <div> or <p> if necessary.

    How come you can use a ½ sign but not a £ sign in your code? Don't use HTML entities where they aren't necessary.

    In PHP, as in many languages, initial capitals are, by convention, reserved for classes.

    It is still just as easy as I made out:
    Code:
    $query = sprintf('SELECT price + line_rental * 12 - %d - cost FROM some_table', $discount);
    Of course, PDO and bound variables are preferable, but if this is the only query you're making might be somewhat overkill. If you've got user input, though, it could be handy.

    Also, make sure that you define $discount = 0; before those ifs, or a user could, on a server with register_globals enabled, specify whatever discount they wanted by adding ?discount=100 to the end of the URL.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends français | entiendo español | tôi ít hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  6. #5
    Join Date
    Aug 2005
    Posts
    174
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default

    Thanks twey..

    the <br />'s were just temporary, i will be replacing these with bullet points..

    twey, the $Monthsfree, $Monthshalf and $Cashback are fields in the table? sorry for being so unclear about this.

    here is my full query

    Code:
    SELECT deals.*, tariffs.*, retailers.* FROM deals LEFT JOIN tariffs ON tariffs.TariffID = deals.DealsTariffID LEFT JOIN retailers ON retailers.RetailerID = deals.DealsRetailerID WHERE deals.DealsHandsetID = '$HandsetID' AND tariffs.ContractType != 'pp' ORDER BY tariffs.LineRental LIMIT 0, 100
    deals.Monthsfree, deals.Monthshalf, deals.Cashback

    the reason i want to perform this calculation is so that i can 'order by' the cheapest overall cost.

  7. #6
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,878
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    Well, since you're also giving output, clearly you need two separate queries: one to get the monthsfree, monthshalf, and cashback, some calculation, then a final one using the calculated $discount.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends français | entiendo español | tôi ít hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  8. #7
    Join Date
    Aug 2005
    Posts
    174
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default

    i didnt know this was possible,

    twey, could you please show me an example? thank you

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
  •