MySQL Query Advice

Hi Chris

Please could you advise how I would output the answer to 2 decimal places.

The query below works but gives a precise answer to nth decimal places. As this a cost column the number needs to be rounded.

ie £10.499 needs to round up to £10.50

Thanks

Hi Jeffncou,

You can use the Mysql ROUND() function to do this, giving it the number of decimal places you want the number rounded to (2):

UPDATE cms_products SET price = ROUND(price * 1.1, 2)

Does that help?

Hi Chris

That helped a lot, thanks. It hasn't output all of the values perfectly because it doesn't force two decimal places if they are not needed.

For example £7.50 displays as £7.5

For the shopping cart to work correctly it doesn't matter, but visually £7.50 would be better.

It took an hour to manually adjust any issues but it would have taken two days to change all of the prices without the MySql query - so happy days :)

Thanks for your help 

I'd recommend leaving the numbers as-is in your database, and add the extra zeros in with PHP anywhere you want to display prices.

<?php echo number_format($product['price'], 2) ?>

Note that number_format(), by default, will also add commas to numbers greater than 999.

Hope this helps!