MySQL Query Advice

Hi Jeffncou,

That looks correct, yes.

If you want to be extra safe, I'd add an extra field, maybe called "old_price", then before you do your *1.1 update, copy the old prices:

UPDATE cms_products SET old_price = price

This way, if something unexpected happens, you can just copy them back.

You can also use the Database Backup feature in Admin > General Settings to backup and restore just your Products table.

Hope this helps!

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?

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!