Adding 20% within mysql

Databases
1

Hey

I have a table of products with a price excluding tax. The client now wants to display the price with tax (however the net/tax needs to be kept separtely for processing reasons)
The products are displayed 50 per page, hence i was wondering if its possible to add to the msql call rather than changing the echo in php.

…is it possible?

thanks in advance

2 

SELECT
      price AS price_without_tax
    , price + ((price/100) *20) AS price_with_tax
FROM
    the_products_table

That example would get the price without the tax and and the price with tax (assumed to be 20% for the example).

3

Cheers, ive got it working…

Just one little thing about the decimal places that i dont if can be overcome.

i.e. orginal price (without tax) was 492.00 euros, now it comes in at 590.4, which is of course correct, but could it include another ‘0’?

4

if you’re displaying directly from mysql you can use the format function:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_format

assuming you want 2 decimals:

SELECT
format(price,2) AS price_without_tax
, format(price + ((price/100) *20),2) AS price_with_tax
FROM
the_products_table

5

Or the less complex: “price * 1.2 as price_with_tax”

6

Thanks all, got it working…theres over 3k products, so its saved quite a bit of time!