Category Archives: MySQL

MySQL osCommerce

How to create multiple specials at once in osCommerce using MySQL

2. May 2013 – 10:46

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

Tags , , | Permalink | Comment (1)
MySQL osCommerce

Managing osCommerce tables using PHPMyAdmin / MySQL

1. May 2013 – 12:44

When managing a store with thousands of products, thousands of customers and a bucketload of specials, the standard osCommerce admin interface simply isn’t practical. So instead of clicking, changing a value and clicking again and repeating the process thousands of times I access the osCommerce database directly – dangerous I know, but the only way to change the sort order of every single product at once for instance. So… before doing anything to your osCommerce database in PHPMyAdmin remember to always!(Yes, every time!) make a backup of your database. If you’d like to know the simple way to do it, then leave a comment about it and I’ll post a guide, though Google can probably help you with instructions on how to backup a MySQL database 😉

 

How to change the sort order of thousands of products at once

Changing the default sort order of every product in your store is a good example of what you can do with direct access to your osCommerce MySQL database – it would take days to do manually if you have more than a few products.

Today I decided to do some maintenance and move some of the items I don’t have in stock down the list so my customers see the things I have in stock first. It’s pretty simple as you’ll see below.

So if that sounds appealing to you, just do the following. Open up PhpMyAdmin or log into your MySQL database another way, backup your database and run the following SQL query:

UPDATE `products` SET `products_sort_order` = `products_sort_order` +1 WHERE `products`.`products_quantity` <1;

What the query does is to increase the sort order of all products that aren’t in stock by one. Remember – the higher the sort order, the lower down on the list the products will be listed on your shop.

Obviously you could do the opposite to the same effect and just decrease the sort order for all products that are in stock. Like this:

UPDATE `products` SET `products_sort_order` = `products_sort_order` -1 WHERE `products`.`products_quantity` >0;

 

That’s all I have for you today – I hope it’ll be helpful to someone. If so, please leave a comment below – questions are more than welcome too 🙂 To prove you’re not  a spambot you can use the word bedazzle somewhere in your comment 😉

 

Tags , , , | Permalink | Comment (0)