Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Help with running sql script to discount prices per customer group


chas2013

Recommended Posts

Posted

hi guys

 

i am tring to discount all products but for a specifc customer

 

IE group 2 15%

group 3 20%

 

....

 

update `products` set `products_price` = (`products_price` * '0.8') WHERE `customers_group_id` = '3';

 

however i get this error?

 

#1054 - Unknown column 'customers_group_id' in 'where clause'

 

the column is there fine in phpmyadmin

 

i have SPPC installed and been using it fine for over a year , i just need to sent a new discount group and dont ahve time to add 300+ prices in,

 

hope someone can help.

 

chas

Posted

I'm pretty sure that the price would not be stored in the products tablle if you have multiple groups with different prices

 

so update products at least needs to be changed to whatever table name it is where these prices are stored

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Posted

You wouldn't be updating the database table's price -- it would keep shrinking the price until it's less than a penny! What you would have to do is determine a new price (i.e., the discount) on the fly and use that discount in all presentations of the price (product listing, in the cart, etc.), rather than changing the database price. It would probably be a new function you'd have to write to take the base price and apply the discount based on the customer group id, returning the modified price.

Posted

hi got the correct group but still cant get it to work:

 

update `products_groups` set `customers_group_price` = (`customers_group_price` * '0.8') WHERE `customers_group_id` = '3'

 

it updates but as there are no prices on group 3 yet i need the above to actaully discount the rrp by 20% for group 3?

 

 

any ideas been trying this for nearly tow days and no luck?

Posted

If you have nothing in the "customers_group_price" for the group_id then trying to multiply 0 (or null or whatever) by 0.8 will result in zero. You need to take the price from products multiply by 0.8 then insert the result into the products_group table

 

Note: at no point should you be amending the products_price in the products table using phpmyadmin.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...