Price-Range Percentage Profit Markup [SEMI-WORKING] Need Help!

[AndyVI]

Hi there,

I searched high and low for a way to set a profit margin based on the product price.

For example if you are importing from a distributors stock list then you may need to calculate how much profit you make from the "cost price" in the stock list.

ie:
£0 - £20 = 20%
£21 - £40 = 15%
£41 - £60 = 10%

---------------------------------

Having not found a solution I decided to try and edit the values in the database.

Here is what ive got so far:

UPDATE `products` SET `products_price` = (`products_price` * 1.20) WHERE `products_price` >= 0 AND `products_price` <= 20 ;
UPDATE `products` SET `products_price` = (`products_price` * 1.15) WHERE `products_price` >= 21 AND `products_price` <= 40 ;
UPDATE `products` SET `products_price` = (`products_price` * 1.10) WHERE `products_price` >= 41 AND `products_price` <= 60 ;

It does appear to be working for some of the handle values ive tried, like products between 41-60 seem to work correctly and have the new price but others like the 0-20 and 21-40 dont seem to have changed.

Am I doing this correctly? or is there a better way to add a cost based markup on products.

I would really appreciate some assistance on this, im no expert in mysql but I do have a little knowledge but hopefully someone can point me in the right direction.

Im sure many others would benefit from this mod too :)

Thanks in advance.

Andy

[Ajeh]

One problem I see is aren't you updating the prices for the 0 - 20 and that puts it in the range for the next update 21 - 40 ...

Perhaps you should run that backwards and do the higher priced first and work your way backwards in the price ...

[AndyVI]

Ah yes that did the trick!

Thankyou 1000 times Ajeh :)

The final SQL commands I made were :

UPDATE `products` SET `products_price` = (`products_price` * 1.05) WHERE `products_price` >= 201 AND `products_price` <= 500 ;
UPDATE `products` SET `products_price` = (`products_price` * 1.07) WHERE `products_price` >= 81 AND `products_price` <= 200 ;
UPDATE `products` SET `products_price` = (`products_price` * 1.10) WHERE `products_price` >= 61 AND `products_price` <= 80 ;
UPDATE `products` SET `products_price` = (`products_price` * 1.13) WHERE `products_price` >= 41 AND `products_price` <= 60 ;
UPDATE `products` SET `products_price` = (`products_price` * 1.15) WHERE `products_price` >= 21 AND `products_price` <= 40 ;
UPDATE `products` SET `products_price` = (`products_price` * 1.18) WHERE `products_price` >= 0 AND `products_price` <= 20 ;

All seems to be in order and calculated correctly :)

I will probably expand on this and make it into a addon, or at least a php or sql file and cronjob instructions or something publicly available.

Thanks again :)

[Ajeh]

You are most welcome ... thanks for the update that this worked for you and for posting what you used ...