mySQL Calculations

[nikomou]

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**

[Twey]

Er...
SELECT price + line_rental * 12 - discount - cost FROM some_table;

[nikomou]

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



// 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...

[Twey]

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:
$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.

[nikomou]

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



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.

[Twey]

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.

[nikomou]

i didnt know this was possible,

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