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