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 😉