Increase price on all products universally?

Admin, please delete posts 58, 59 as there were code errors there I could no longer edit. Thanks.

Here's a bit of a tricky one. We've just gone VAT registered so we had to increase all our prices to add on tax. I didn't want to add the full 20% so we reduced by 5% (*0.95) and then switched tax on via locations/taxes.

Now, all our prices are really weird like £6.53 and £61.47.

Is there a way to round all Products Price (Gross): to the nearest dollar/pound?

limelites:

Is there a way to round all Products Price (Gross): to the nearest dollar/pound?
Hi.
Just did it myself the easy way...
Admin > Currency (under 6th flag) > nr. of decimals :smile:

Troll:

Hi.
Just did it myself the easy way...
Admin > Currency (under 6th flag) > nr. of decimals :smile:

OK, works well generally throughout the site but the product page itself shows prices like £69 and £44 instead of prices like £69.00 or £44.00.

How do you force the product page to show two zeros after the decimal point. Example, here:

https://www.silkblooms.co.uk/index.php?main_page=product_info&products_id=2332

Hi, I have just tried to increase my prices using UPDATE products SET products_price = products_price * 1.05; but I get the dreadded Warning an error occured message. My hosting agents say that it is because they use MariaDB so the syntax is wrong.

They say You need to see if there's a MariaDB alternative

Can anyone help with this please zen vrs 1.5.5a

post the contents of any debug logs along with your sql statement. that would be a good starting point....

another example of the tech support at the host not really providing useful information, from https://seravo.fi/2015/10-reasons-to-migrate-to-mariadb-if-still-using-mysql:

9. Compatible and easy to migrate
   MariaDB 5.5 is a complete drop-in-replacement for MySQL 5.5. Migrating to MariaDB is as easy as running apt-get install mariadb-server or the equivalent command on your chosen Linux flavor (which, in 2015, is likely to include MariaDB in the official repositories).

I just tried your statement on my local db: MySQL 5.5.5-10.1.13-MariaDB using the Admin->SQL patch tool with no problems, two multiplications and a division to get it back to where it started.
I thought everything that works on mysql works on mariadb, that is the idea, it's a straight replacement, but not necessarily the other way round as mariadb has extra funcionality.

error log below. patch was UPDATE products SET products_price = products_price * 1.05;

[30-Nov-2016 13:47:27 Europe/London] Request URI: /zcadmin/sqlpatch.php?action=execute, IP address: 212.56.109.224
#1 trigger_error() called at [/home/extingshop/public_html/includes/classes/db/mysql/query_factory.php:167]
#2 queryFactory->show_error() called at [/home/extingshop/public_html/includes/classes/db/mysql/query_factory.php:139]
#3 queryFactory->set_error() called at [/home/extingshop/public_html/includes/classes/db/mysql/query_factory.php:266]
#4 queryFactory->Execute() called at [/home/extingshop/public_html/zcadmin/sqlpatch.php:304]
#5 executeSql() called at [/home/extingshop/public_html/zcadmin/sqlpatch.php:672]

[30-Nov-2016 13:47:27 Europe/London] PHP Fatal error: 1064:You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE##products##SET##products_price##=##products_price####1.05' at line 1 :: UPDATE##products##SET##products_price##=##products_price####1.05; ==> (as called by) /home/extingshop/public_html/zcadmin/sqlpatch.php on line 304 <== in /home/extingshop/public_html/includes/classes/db/mysql/query_factory.php on line 167

i'm guessing you have a prefix for your tables.

what happens when you try:

select * from products;

what would the complete syntax be? where do i put select * from products; prefix seems to be zen3r_

try:

UPDATE zen3r_products SET products_price = products_price * 1.05;

getting really confused now. Tried that and got ERROR: Cannot execute because table zen3r_products does not exist. CHECK PREFIXES!

see screen grab attached, unless I am blind it does exist

you must have multiple databases. possibly....

while looking at the phpMyadmin file, the prefix is there; when trying to run it from the install sql patches, it is saying it is not...

in re-looking at the original error log you posted, you have a bunch of '##' in the error log. where did those come from?

honestly, this should not be that difficult. if you are sure about having the correct database, you could run the sql statement from phpMyadmin.

good luck.

Thank you

there is only one database on that domain. Thanks for trying. Will post back here with solution when fixed

the ## seem to of been added when i pasted into this forum

Congerman:

the ## seem to of been added when i pasted into this forum
... that happens when blacklisted characters or words are used.

So it suggests maybe the "space" character that you're using is also incorrect, and as such the SQL command is failing. In such cases it's usually because you've copy-and-pasted the SQL line from some other website (which picked up hidden characters), instead of typing it out yourself.

Thanks for that, whatever I tried it would not update via admin. I had to go to phpmyadmin and it worked fine

I'm about to decrease all prices by a percentage across a ZC website but is there a way to ROUND the result and deduct 1p from the answer? In Excel the formula is::

=(ROUNDUP((EXISTINGPRICE*0.9),0))-0.01

How would I make this work with the MYSQL script below, arithmetically::

UPDATE products SET products_price = products_price * 0.9; ?

limelites:

I'm about to decrease all prices by a percentage across a ZC website but is there a way to ROUND the result and deduct 1p from the answer? In Excel the formula is::

=(ROUNDUP((EXISTINGPRICE*0.9),0))-0.01

How would I make this work with the MYSQL script below, arithmetically::

UPDATE products SET products_price = products_price * 0.9; ?
Considering that you appear to be wanting to find the next integer value for the price of the product after multiplying it by 0.9 and from that subtract 0.01, then you could use the following to accomplish the task:

UPDATE products SET products_price = CEILING(products_price * 0.9) - 0.01;

This will round any resulting value to the next integer and then subtract 0.01, so if the multiplication result is 3.0001, the result will be 4 and then subtracting 0.01 will be 3.99... (which could mean that there is no change in price if the existing price is low enough as in at or below 8.99 originally and with the original value ending in .99...)

You can also see an answer in your 2012 question here #58: https://www.zen-cart.com/showthread.php?79927-Increase-price-on-all-products-universally&p=1100321#post1100321