How to show more categories of a linked product

In osCommerce, there is a mechanism by which a product can exist in multiple categories. It’s the “copy to” functionality on the product adding/editing page in admin. Obviously, the “copied” product needs to be LINKED (not DUPLICATED) as a linked product has the same ID as the original product whereas a duplicated product gets a new ID…

So, assuming we have 1 linked product across a number of categories we might want to show the customer the other categories in which it resides. There is no in-built way to do this, so we need to make some code to enable it.

Step 1. Add the code to get (and display) the other categories in which the product resides

Open up product_info.php, add:


$catQuery = tep_db_query("SELECT categories_id from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id='" . (int)$HTTP_GET_VARS['products_id'] . "' and categories_id NOT IN ('" . $current_category_id . "')");
while ($cat = tep_db_fetch_array($catQuery)) {
echo sprintf(ALSO_SEEN, $product_info['products_name'], tep_get_path($cat['categories_id']), clubosc_get_category_name($cat['categories_id']));
}

Here we are making a call to the database to get all the categories the product is in, other than the category we are presently looking at. There is no point in letting the customer know it exists “here” where he is looking.

Step 2: Add the language define

Open /includes/languages/english/product_info.php, add


define('ALSO_SEEN', '
%s can also be seen in < a href="' . tep_href_link(FILENAME_DEFAULT, '%s') . '" >%s< /a >');

Here we are passing 3 parameters to the text definition. These 3 parameters are; product name, category path, category name. These are passed by the code in Step 1.

Step 3: Add a new function to get and display the category name

Open /includes/functions/general.php, add


function clubosc_get_category_name($cat, $language = '') {
global $languages_id;

if (empty($language)) $language = $languages_id;

$categories_query = tep_db_query("select categories_name from " . TABLE_CATEGORIES_DESCRIPTION . " where categories_id = '" . (int)$cat . "' and language_id = '" . (int)$languages_id . "'");
$categories = tep_db_fetch_array($categories_query);

return $categories['categories_name'];
}

Here we pass through the category id (in Step 1 code), and use it to get the name of the other category/categories the product is in.

I have covered making functions in the past in this blog and I have also covered in quite some detail how “sprintf” works.

The result of this will be a line (per category the linked product is in) of text like this;

Of course, you can pretty up the text as you see fit, using .css. Enjoy.

Introducing a dedicated osCommerce Email Support Service

After much deliberation and asking many questions of my clients and potential clients, it’s come as no surprise to me that there are a number of business owners who want a more dedicated service than the osCommerce forum can provide.

This is where my new Email Support Plan comes into it’s own.

  1. The Plan is a monthly subscription.
  2. The Plan allows you to email any questions about your osCommerce shop. I will answer them within 1 working day.
  3. This plan does not include any coding or installation of existing addons. If I recommend you need an addon, I will quote for making it, or for installing an already made add-on. You can then choose whether to go ahead or not.
  4. In effect, the Plan is simply to offer a priority “answer my question” service.

The idea is to offer this at very low cost, a “beer fee” each month. If you are interested in learning more, please email me on oscshops AT gmail DOT com.

Individual Shipping Per Product Per Zone

Approached recently by a potential customer who needs to set up a system for charging postage on a per product basis, per geographic zone. This needs to work as a “extra cost” on top of the usual shipping cost. Think in terms of an awkward to send product where you might want to charge $10 extra on top of the usual shipping fee. I’ll illustrate this with an example…

Step 1: Set up a couple of zones (for shipping purposes)
Although this says “tax zones” they can be used for shipping geographically.

Step 2: Edit (or create) a product
On this page we have extra input box asking for the shipping price per zone.

This defaults to zero, in here you can insert different price per zone. In the example, I’ll make the fee 5 for UK and 10 for Europe. I press “Save” to update the details of the product.

Step 3: Set up your Shipping Method(s)

For this example I’ll use the easiest (to look at) shipping module which is “flat rate”. I’ve set the fee to 2.50 flat fee for all orders.

Making an Order

If I now place into my cart a product that has NO extra cost set up, the shipping module will return 2.50 as the shipping cost.

Now if I also add a product that I have placed an extra cost (Unreal Tournament in this example), the shipping should show a total of 7.50 (which is 2.50 flat fee + UK extra cost of 5).

Hopefully, you’ll have read between the lines and understand that I could just as easily have created a new shipping module to only read the value of the extra cost parameter (per zone). In this way, I can set shipping prices per product and not have to have it react as a “surcharge” on top of an existing shipping module (as shown in this example).

Conclusion

Hopefully you can see that osCommerce is fairly easy to modify if you work out what you need to do in advance, understand how osCommerce hangs together and then stick to the project at hand.

For this example, the main files modified are admin/categories.php and the order_class file. The rest is simple arithmetic.

Update all your prices in one go to 1 cent under the dollar

A customer of mine wanted to update all his prices to 1 cent under the dollar, eg go from 15.80 to 15.99. With 1000s of products that’s an utter waste of time, so here is one line of SQL to use in phpMyadmin;

UPDATE `products` SET `products_price` = (ceil(`products_price`)-0.01);

What this does is set the price to the next nearest whole number minus 1 cent.

Enjoy.

Shipping from Ireland EIRE to Ireland Northern – what a pain

I have a customer who owns a shop based in Eire where the postage cost to Northern Ireland (part of the UK) is substantially less expensive than post to the mainland UK. This presents quite a difficulty with getting shipping rates that are spot on, as the UK is England, Scotland, Wales and NI (there is no separated regions for shipping).

In effect, if Northern Ireland had it’s own ISO code things would be simple. To get around this, I set up my own “user defined” ISO code for Northern Ireland and added it to the countries list as “United Kingdom (NI)”. Downside of this approach is that the person creating the new account must select UKNI and not UK as their country. Not really a deal breaker, I think. So, now, the shopowner can separate NI from the UK quite easily, by using the new ISO code “QN” in the zones.php module.

The customer then also needs the ability to have the zones module work on order_total, not weight, and also the ability to insert % costs as well as $£ costs.

All in all, quite a straightforward project.

And then, a spanner in the works

I thought…what about already existing customers in NI, who (because they joined before the new fake UKNI country) still have shipping addresses linked to the UK. As soon as they log back in to order, they will get the more expensive UK shipping prices. Fortunately, there is a way around this also…all postcodes in Northern Ireland start with “BT”, so I need to come up with a piece of SQL to run in phpmyadmin that says; if (country == UK) and (first two letters of postcode == BT) update country to QN

That probably won’t make much sense as it is pseudo-code, all it does is transfer customers shipping addresses from the UK to UKNI, which means they get the proper shipping prices from this point forward.

Thinking Forward

This approach could also possibly be used for shipping to places such as Highlands and Islands, Channel Islands, IOM and so on. Just set up a fake ISO code for each and perhaps put a message on the create_account page to remind customers to choose their correct “country”…

Going back to the problem of the user selecting UKNI rather than UK, this can be accomplished using a line of javascript as we know that ALL postcodes in NI start with BT we can read that value from the postcode input box and update the country dropdown immediately. Video;

It would be very simple to also show a message or such to say we have “pre selected” the country to further aid the user.

Reorder the same

In osCommerce there is native functionality that allows the person to reorder on a product by product basis – it’s the bm_order_history infobox. It pretty much lists the last few products ordered and has a clickable link which either adds the product to the shopping_cart, or goes to the product_information page to allow to select the product options.

I’ve had a plan to write a “let me re-order the exact same again” script, and after having chatted with Matt last night I set out to make it a reality. And so I present to you my effort at a re-ordering script.

Step 1. Add a new button at the account_history page.

Step 2. Make some new code in the shopping cart to grab the orders products, and insert them into the shopping cart.

Notice that the inserted product(s) include the options/attributes if applicable. Now the buyer is ready to continue shopping or checkout.

Video in Action

If you need something similar, please feel free to get in touch by email.

How about something similar, yet completely off the wall ?

Take for example a regular client who decides she really loves a few products she bought from you, but does not want to re-order the whole same order? In this case, why not present all the products purchased from previous orders the client made. Then allow the customer to tick the products to re-order?

And of course, work the re-ordering mechanism into the bm_order_history box to replace the “1 by 1” functionality!