i4s.dk

Once in a while I set a discount for lots of products at once and the default interface for specials  in the osCommerce Admin interface simply sucks – who has the time to manually add hundreds of products to the list of specials? It’s fine for a single product I guess, but in my usual cases it simply doesn’t cut it.

So if I want to have an opening sale, putting a 40% discount on all products added to the shop after a specific date(let’s say – today, May 2nd), I can do the following (after I make a backup of my database!):

insert into specials (`products_id`,`specials_new_products_price`,`expires_date`) select products_id, products_price * 0.6, “2013-05-11 23:59:59” from products where products.products_date_added > “2013-05-02 00:00:00”

What this does is to add every single product added to the database after May 2nd 2013 at 00:00:00 to the specials table with a products price of 0.6 * the products original price (=a 40% discount). You’ll most likely also want to set an expiry date for the special price and as you can see in the query I set the expiry to the end of May 11th 2013.

Another variation of the above query is to have a sale on all products that are in stock at the moment. This can be accomplished by running the query below.

insert into specials (`products_id`,`specials_new_products_price`,`expires_date`) select products_id, products_price * 0.6, “2013-05-11 23:59:59” from products where products.quantity > 0;

Again, please remember to make a backup of your database before you tinker with it like this. A single typo could completely ruin your shop for good!

That’s it for today. Have fun and please leave a comment if the tips provided here have helped you or you have questions about handling the MySQL queries or making modifications.

Best regards,

Anders