Upgrading to RC2a

As Liuk rightly pointed out, upgrading from an older version of osCommerce to the latest version is not straightforward. There is at least 5 steps that need to be done:

multiple version upgrades, database upgrades. database transfers etc.

The existing shop that I am upgrading is based on the osCommerce 2.2-MS2 architecture (which is now at least a few years old), but has been highly modified by myself with all manner of code changes to make it more functional. As this particular shop as only 17 live products I am NOT going to do a manual upgrade…

What I am going to do is simply use a new RC2a, with some contributions – and then add in the 17 products manually. I only have to make sure that the old product ID matches the new product ID and that any category ID’s in the old shop match the ones in the new shop. This is so that all product URLs remain the same from old to new.

So…first up is to use a “pre loaded” (by myself) version of RC2a – you can find more details about this pre loaded version in this previous blog post. What I will do is load this up on localhost (aka my home computer) to get the products and category structures correct.

For posterity sake here is a screenshot of how the existing site looks at this point in time:

And if you wish to visit the site to have a look around, it is here. It’s actually based on a theme I purchased a few years ago, which does look good, but I think is a bit tired now.

Task to complete…

Install pre-loaded version onto localhost and duplicate the category, product, and page names – in order to preserve the URLs of the site exactly as they already are.

Watch out for the next installment of the Makeover!

Live rework of an osCommerce site

Here’s an idea that might be interesting to some of you who struggle with how to design osCommerce. How about if I take a LIVE osCommerce site and update it to rc2. Then day by day, add a design feature until I eventually end up with a unique site.

I’ll blog about it daily, and each design feature that I add, I will tell you why I added it and how it was done.

Interesting, or not?

Attributes, Options, osCommerce

In standard osCommerce, the product options system (aka attributes) is almost completely useless. The reason for this is obvious – attributes cannot be linked together to make a meaningful “sub-product”.


T-shirt – 12 in stock, of which:
3 are blue, large, round neck
4 are red, large, v-neck
2 are blue, small, round-neck
3 are blue, xxl, round-neck

In that example, you have only 1 product, but you need to set up “colour” (red and blue), “size” (s, l, xxl) and “style” (round neck, v-neck). In the standard attribute system, this will give you three dropdown menu’s showing the available options. Follow me so far?

What it means is that a potential buyer could choose a bunch of options that are simply not available – example: xxl, red, round-neck. And they would be able to check out. When they check out, overall stock is reduced by 1 bringing your number of available shirts down to 11.

But you have just sold something that does not exist…are you with me?

OK, so there are some steps you can take to combat this:

1. Install something called “QT Pro” – which is PITA to install and has many bugs.
2. Install “Master and Slave Products” – which is easier to install, but also has problems – it’s ubnwieldy in real life use.

Here’s my idea for a rework of the attribute system…

1. Allow the store owner to insert options as per normal.
2. Have an extra “options” page which will allow the store owner to make “attribute sets” (explanation below)
3. Assign these “attribute sets” to products.

What is an attribute set?

In the example above, this:

blue, large, round neck

is an attribute set. The store owner would link 3 options together and be able to insert the quantity available. He would also be able to give the “set” a SKU.

Then, in the product_info page, SKU’s would be shown, like this:

Description, blah blah
In stock:
( ) blue, large, round neck [SKU: qwerty123] – 3 in stock
( ) red, large, v-neck [SKU: qwerty124] – 4 in stock
( ) blue, small, round-neck [SKU: qwerty125] – 2 in stock
( ) blue, xxl, round-neck [SKU: qwerty126] – 3 in stock

Now, the next part of this modification, would be subtracting stock, this would be done in two stages. Let’s say the buyer purchases 1 of “qwerty123”. The overall stock of t-shirts would decrease from 12 to 11 and the stock of “qwerty123” would decrease from 3 to 2.

How does that sound? Can any of you see any flaws in my thinking? Anyone got any ideas to add to this?

Make the New Products for Month more useful

I’ve previously posted about this particular module, about replacing it with featured products, or special offers or whatever. Here is another idea that I came up with last night…

How about only showing products that were added to your store within the last 30 days? And having those 30 days “update” automatically? Well, it’s very simple, and takes only 1 small code change in the New Products Module.

So, open up /includes/modules/products_new.php and find this code:

[php]$new_products_query = tep_db_query(“select p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, if(s.status, s.specials_new_products_price, p.products_price) as products_price from ” . TABLE_PRODUCTS . ” p left join ” . TABLE_SPECIALS . ” s on p.products_id = s.products_id, ” . TABLE_PRODUCTS_DESCRIPTION . ” pd where p.products_status = ‘1’ and p.products_id = pd.products_id and pd.language_id = ‘” . (int)$languages_id . “‘ order by p.products_date_added desc limit ” . MAX_DISPLAY_NEW_PRODUCTS);[/php]

Change it to this:

[php]$new_products_query = tep_db_query(“select p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, if(s.status, s.specials_new_products_price, p.products_price) as products_price from ” . TABLE_PRODUCTS . ” p left join ” . TABLE_SPECIALS . ” s on p.products_id = s.products_id, ” . TABLE_PRODUCTS_DESCRIPTION . ” pd where p.products_status = ‘1’ and p.products_id = pd.products_id and pd.language_id = ‘” . (int)$languages_id . “‘ AND p.products_date_added > date_sub(NOW( ), INTERVAL 1 MONTH) order by p.products_date_added desc limit ” . MAX_DISPLAY_NEW_PRODUCTS);[/php]

Save the file and upload it to replace the older one (you might want to backup your older modile first). The pertinent pieve of code I added is:

[php]AND p.products_date_added > date_sub(NOW( ), INTERVAL 1 MONTH)[/php]

which takes the “products date added” date and compares it to the date 1 month ago. If the “product date added” date is NEWER than 1 month, the product will show in the module output.

Learn More

If you look at this MySQL page, you can learn more about the INTERVAL settings which can allow you to display your products appropriately. So if you add more products each day or week, use a “DAY” or “WEEK” INTERVAL…

Remember that running an online shop is all about making things as easy as possible for your potential buyers, and your previous customers.

Take It Further

How about having some links near the module output, which will allow a potential buyer to see what products you have added in the past hour (or day, or week, or month or whatever). Something like (obviously the links below are not real, they are example of what I am talking of):

Show products added in the last daythe last weekthe last month

When these are clicked the module refreshes to show your products as appropriate. How cool would that be?

Multiple Per Item Shipping Modules

Kalidas asks;

I am using oscommerce2.2 default package for e-commerce site development.

One of my client wants to ship his product in Per Item module but costing in two modes like Ground Delivery $5.00 and Rush Delivery $10.00.

In Per Item module, there is only one Shipping Cost named “Best Way” (can be changed to Ground Delivery) but how can I put another Shipping Cost named Rush Delivery?

It’s an easy answer – you use 2 different shipping modules! In standard osCommerce there are (I think) 5 or 6 shipping modules included, here’s an idea of what you could do:

Use “Per Item” shipping for your “Ground Delivery, and change the wording in the language file to “Ground Delivery”. You could then use “Table Rates” based on WEIGHT – give each of your products a weight of 1 – and set up the prices to increment by 1 – then rename (in the language file) to “Rush Delivery”…not a perfect solution by any means as it is possible you won’t have enough parameters in your shipping for a large order.

Alternatively, and what would obviously be much better, would be to spend a little amount of money and get the exact shipping methods that you need, coded specifically for your needs. As this is for a client, simply pass the cost along to him. Easy as 123.

There might also be an existing contribution that you can use, but generally, it takes less time to code from scratch than it does to find a contribution, install, configure, test it, etc etc.

Shipping Modules are fun to code

Here is a question from a client;

Im setting a a new oscommerce website and cant seem to get the shipping working. I would need to have about 5 zones. Each zone would contain different countries. For each zone there is a basic rate per pound. eg $35 . Each additional pound cost $5 etc per zone. That cost differs per zone. Also i would need packaging cost.

I’ve not seen anything quite like this available as a contribution – so instead of spending hours looking for it, I decided it would be more cost effective to code this up from scratch. It was actually fairly straightforward. Here’s the interesting info;

In the Admin section, I simply made an input form for the module. For each Zone that is set up, the client can insert 3 different prices. In the image below, I have set up 2 zones…

As you can see, each Zone has 3 inputs: “1st”, “rest” and “handling”. “1st” takes care of the first unit of weight, and “rest” takes care of any subsequent units. Handling is obvious! In Zone 1 (in the image) I set up 3 countries: AG,BB,DM. So the costings inserted in the three price fields will be used for those 3 countries. In Zone 2, similarly, 3 countries US,CA,FR with different prices for “1st” etc.

You will also notice above these Zones, an area entitled “Cost for all other Zones” – this is a fail-safe method of making sure that each country gets a quotation for postage. A country that is not in any existing zone will default to this.

In the actual codebase for the module, the relevant piece of code that works out the actual price is as follows:

[php]$shipping_cost = $shipping;
if ($shipping_weight > 1) $shipping_cost += ($shippingtoo * ($shipping_weight – 1));
$shipping_cost += $handling;[/php]

Which basically says;

[php]$shipping_cost = $shipping;[/php]

This is for items of a total of 1 unit of weight or less

[php]if ($shipping_weight > 1) $shipping_cost += ($shippingtoo * ($shipping_weight – 1));[/php]

Here we are saying IF the total weight is GREATER than 1, then $shipping_cost should be INCREASED by the “rest” ($shippingtoo) amount multipled by the $shipping_weight -1. Remember that we have to do -1 as the first unit has already been worked out!

[php]$shipping_cost += $handling;[/php]

Easy and straightforward, add the handling fee on top!

Code hints

You might notice me using something weird in these calculations, the += sign. What this does is simply add something onto an existing total. It’s a shorthand way of saying;

[php]$shipping_cost = $shipping_cost + $handling;[/php]

It’s a bit less work to just say

[php]$shipping_cost += $handling;[/php]

So, there you have it. I thought it might be interesting for you to see a bit about how I code things up – I try to make things as simple as possible.

Why not just use “Zone Rates” module?

If you have a bit of knowledge about osCommerce, you are probably thinking “he could have just used Zones Rate”. Well, that’s true, but there are good reasons why you should not…

1. Using Zones Rate you would have to set up EVERY country into a Zone to get a shipping quote. In mine you do not need to do this. Each country NOT in a Zone will default to “all other zones”.

2. Zones Rate cannot handle an open-ended system of weight, as the database field is simply too small – so if the customer ordered 400 units, with each unit costing say $5, my version can work this out very easily. Using the standard Zones Module would be IMPOSSIBLE (under normal use).

Interesting Code Question

Over in the osCommerce forum, Stephan asked;

Take gflash01.gif gflash02.gif gflash03.gif … gflash10.gif

Then line up the 10 pics next to each other with a 5px space between them and make the website dispay them in a random order. They are white flash/fading gifs with the same name but different flash/fade times. My goal is to create a random patter emerging for each person who visits my site.

Think of it as a footer on a music composing website that helps people composing songs to get ideas from emerging patterns.

I actually have done something very similar to this on a previous project, however my previous project was text files (in a list) rather than gif files side by side. So it was easy for me to supply the code, however as I did not read the question properly, my first code suggestion was as follows:


What this does is find all the filenames that start with “gflash” and end in “.gif” and print them off as images. What’s wrong about this? It won’t work in random order. Another forum user came up with a solution based off the above code – which works, but uses more code than is necessary.

So I then came up with:


This looks very similar to the previous code, but introduces a couple of new elements. For starters I use the “shuffle” to randomise the order of the array of images, then use a loop to print them out.

Deconstructing the code

[php]$filename = glob(“gflash*.gif”);[/php]

The “glob” finds all the filenames that start with “gflash” and end in “.gif” and puts those filenames into an Array called $filename.


Pretty obvious! This randomises (or shuffles) the contents of the Array.

[php]for ($i = 0; $i <= 9; $i++) { echo '‘;

Use a for loop to iterate through the Array, printing out the HTML to show each image. I use $i as the count, and so the $filename[$i] will show each entry in the Array.

Sounds more complicated than it really is, I think.

Stop People Checking Out? Huh?

Interesting question posed at the osCommerce forum;

All my products are 5lbs each. Is there a way to set a minimum order of 10lbs. This would mean they have to purchase a minimum of two items (2 of the same product or 2 different products).

Is there a way in the USPS shipping module or any other file to set a minimum order of 10lbs or 2 products?

Easiest way is to do it on the 2 product minimum purchase, as I have already covered this in a previous post. cart->count_contents() is the code to use in this case…

So here is my answer to the question…

Add this in checkout_shipping.php, underneath require(‘includes/application_top.php’);

[php]if ($cart->count_contents() < 2 ) tep_redirect(tep_href_link(FILENAME_SHOPPING_CART, 'error_message=' . urlencode(ERROR_NOT_ENOUGH_IN_CART)));[/php] How simple was that? The line of code basically says:

IF the number of products in the cart at checkout time is LESS THAN 2, then REDIRECT the user back to the Shopping Cart and DISPLAY an error message to tell him/her so.

Obviously you also need to make the define in the language file (includes/languages{your language}/shopping_cart.php):

[php]define(‘ERROR_NOT_ENOUGH_IN_CART’, ‘You have not purchased enough stuff. Buy more you fool.’);[/php]

Nice, quick and easy solution – always best to keep things as simple as possible in case you ever want to change it in the future.

Helping your customers to make a buying decision

Have you ever thought how easy it is for potential customers NOT to buy from you? How do you get around that? Today I received this email from a shop:

It is soon Christmas. We are counting down to the great day, 24th of December.
Today only 16 days left, that give you 16% discount on any order you make today, tomorrow you will get 15%, next day 14% and so on. Therefore, make your order already today, as will give you the best discount. Use this discount code “abc123” and the days discount will be deducted on your order.

which I thought was a great idea – a sliding scale of prices from now until Xmas eve. The earlier a person buys the product(s) from your site, the less they pay!

Some time ago, I had a similar idea for a website. Each month I was going to offer a downloadable product on an (upwards) sliding price scale;

1st of the month: $1.00
2nd of the month: $2.00
and so on until the 31st of the month: $31.00

so the sooner the person buys, the less they pay. I ran it for only 1 month as other things got in the way. It wasn’t really a success as I never got the word out about it, however, the idea of sliding prices is a good one, and certianly viable during this time of uncertainty…as I recall I made about 90 sales over the course of the month – most of which were on the 1st, 2nd and 3rd of the month.

So, just what have YOU done to help your customers to make the decision to buy from your site?

Coupons v1 to v5 – worth it? Points, Rewards?

Jason asks;

I use Discount Coupon v1.2 on my site and it works and works well – looking at your site its hard to see whats available in version 2.3.4 and now 5 which isn’t in version 1 to see if its actually worth upgrading. Do you have a list of whats new in the later versions?

The main changes are as follows;

Admin changes:
+ ability to see when each coupon was last used
+ ability to view all orders that used each coupon
+ ability to set a “use once, one time” coupon (instead of per customer)
+ ability to set a minimum spend before a coupon can be applied

Shop changes:
+ moved coupon input box to shopping cart (away from checkout process)
+ ability to remove a coupon after it has been applied
+ now works with Tax calcaulations

Coming Soon:
– ability to set an expiry date per coupon

Also, age old question I know but any update on the GV side of the contribution?

As yet, no-one has offered to sponsor development, hence it’s (very) low down on the to-do list, as it’s something that I personally do not need.

Final point then – does this work with Point and Rewards Module – I only ask the instructions for it specifically mention things which need done if using CCGV and, while I obviously know your contrib Is NOT CCGV, it does similar things so was curious to know if this was tested or if you knew of any incompatibilities?

Should work OK with all contributions, IF those contributions are decently coded. I don’t know of any incompatilities with that particular contribution. My coupon system is coded far more cleanly than CCGV so I think that there should be no problem. An idea would be for someone 😉 to install a new osc, then Points and Rewards, then v1.2 of coupons and see if it all plays together nicely.