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?

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!