How to resolve the Zen Cart Illegal mix of collations error

We had deployed a Zen Cart website and we came up with the following error in the Zen Cart admin panel when clicking on the Tools -> Send Email link.

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation 'locate'

After checking the table collation in PHPmyAdmin I found that the subscribers table (created when the Newsletter Subscribe module was installed) collation was different to the configuration table.

Most people on the internet advised that the collation for the subscribers table and email_address field should be changed to that of the remaining tables in the database. I tried to change the collation of the subscribers table and the email_address field to that of the configuration table but met with no success.

I then changed it to ‘utf8_unicode_ci‘ and the page displayed without any problem or error.

The exact SQL I ran in mySQL was

ALTER TABLE subscribers CHANGE email_address email_address varchar( 96 ) NOT NULL default '' UNIQUE COLLATE 'utf8_unicode_ci';
ALTER TABLE customers COLLATE 'utf8_unicode_ci';
ALTER TABLE subscribers COLLATE 'utf8_unicode_ci';
ALTER TABLE customers CHANGE customers_email_address customers_email_address varchar( 96 ) NOT NULL default '' UNIQUE COLLATE 'utf8_unicode_ci';

The first statement in the SQL above is thanks to DrByte.

Hope the above helped someone.

Integrate Zen Cart store to eBay with eBay Zen Cart module

Store owners who use Zen Cart for running their stores on the web can now enjoy the luxury of listing, revising and de-listing products on eBay all from their Zen Cart admin panel. Gone are the days when you needed to add products twice (one in each Zen Cart and eBay) and then constantly worry that you do not oversell an item on eBay if the stock inventory has exhausted for that product.

In short, you will need to list your products once and earn forever*.

This facility is tried and tested and running in production environments on Zen Cart stores based in the USA and UK and clients have been more than happy with the results.

You will be able to do the following when you use this module

1. List/revise/delist items on eBay from your Zen Cart admin panel.
2. Maintain separate product title, product price and product description fields for your products on eBay in your Zen Cart Add/Edit product page.
3. Automatic download of orders from eBay to your Zen Cart orders listing page.
4. Automatic creation of customer accounts on your Zen Cart website as soon as order is downloaded from eBay.
5. Automatic synchronization of product stock and price on your eBay store with the price in your Zen Cart store.

Please see attached snap shot for more detail

List/revise/delist items on eBay from your Zen Cart admin panel

eBay order downloaded to your Zen Cart orders listing page

If you would like to have more information regarding this then please contact me today and I will be more than happy to help you out.

* As long as you have inventory for that product -:)

Wish happy birthday to your customers from Zen Cart

Zen Cart by default requests a customer to enter his/her date of birth during registration. This is optional of course and can be removed from the Zen Cart admin panel. However one can make good use of this to regularly wish their customers a happy birthday thereby keeping in touch with them.

I have developed a script which will send a birthday wish to the customer when their birth date comes along. You will need to setup a cron job on your hosting server to run the script each day. You are also free to make changes to the script according to your needs.

Here is the Zen Cart birthday script and hope it helps some one.

Remember to copy it to your root folder and then setup a cron job to execute it daily.

Bulk generate search engine friendly URLs in Zen Cart using Fast Updates

Our SEO department had recently made a request to implement CEON URI mapping module on one of our leading furniture retailer store in the UK. The module was implemented by the developer assigned to the project in a couple of hours and the website was handed over to the concerned department so they could continue adding the SEO friendly URLs to the e-commerce website.

Now the website had over 5,000 products and if they had to go by the default way of going into  each product edit page and updating that product page to generate the URL then it would have taken quite some time to complete this task and then continue on with their main job of actually optimizing the website.

I decided on using Fast Updates for this purpose. I added an input box on the page where the user could add his own version of the URL or he could leave it empty and let the system generate it for him. To generate the SEO friendly URL I had to use the concerned functions from the CEON URI mapping module. After some search I found the function needed for this purpose.

The actual function which generates the SEO friendly URL can be found in the following class file


The name of the function which does the job is


The function expects the following parameters in the order given

id – product id of the product
type – category or product. As we are generating the URLs for the products I will just enter ‘product’ here
category – the category id against which this product is associated
name – the product name of the product
language code – just enter ‘en‘ here as it is for an English language website
language id – the Zen Cart language id for the website. In my case it will be 1 as it is for an English language website

The function returns the URL which I saved into the ceon_uri_mappings table and the problem was resolved. I handed it over to SEO department and they generated all the URLs within a couple of hours.


How to implement Lightbox effect in Zencart

Lightbox effect is now the defacto standard for displaying product, category and other images on your website. Javascript popups are a thing of the past and really don’t look that cool. As more and more website content management and e-commerce frameworks provide this feature I will today discuss how we can use this in Zencart.

Zencart provides a very easy to install module known as the “Zen Lightbox” which you can download from the Free Software Add Ons section.

After downloading the module you can run the install.sql and copy the files from the images, classes, functions and languages (do change the folder name to your template where necessary) to your Zencart folder as they are all new files and do not overwrite any existing file. You will need to be more cautious for the files in the modules and templates folders as they will overwrite your existing files.

In my case I just needed to display the lightbox effect for the images on the product detail page so I made the following customizations in the tpl_product_info_display.php file.

1. First I placed a check to make sure whether Zen Lightbox was enabled or not. If it was then I include the necessary files.

if (ZEN_LIGHTBOX_STATUS == 'true') {
  require_once(DIR_FS_CATALOG . DIR_WS_CLASSES . 'zen_lightbox/slimbox.php');
  require_once(DIR_FS_CATALOG . DIR_WS_CLASSES . 'zen_lightbox/autoload_default.php');		

2. Then I check whether the image on which the lightbox effect is to be applied is an individual image or part of a gallery.

	$rel = 'lightbox-g';     //in case of image to be part of gallery
else {
        $rel = 'lightbox';       //in case of individual image

3. Finally I call the zen_lightbox function which generates the necessary code for the lightbox to take effect.

<a href="<?php echo zen_lightbox('path to product image', 'product name', width, height); ?>" rel="<?php echo $rel; ?>"><img src='path to product image' alt='product name' width='width' height='height'/></a>

Looking above at the 3 steps you can see how easy it is to implement lightbox effect in Zencart. If you would like to view a live example then please click here.

How to implement CAPTCHA on Zencart registration forms?

A client of ours was facing an issue with spam registrations on his website. We had developed his website on Zencart. The issue was that spam bots were registering on his website in the hundreds and the client wanted us to put a stop to this.

I decided on implementing CAPTCHA on the registration forms so that these unwanted registrations are stopped. I browsed the Zencart website and found the CAPTCHA Anti-Robot Registration mod.

I downloaded and, as is the practice, copied the files to the relevant directories. After copying over the files, however, it was not working smoothly. I then made some changes to the includes/modules/create_account.php file and it started working on our local development machine.

After checking several times I moved the files to our production server. Our production server was running PHP 4.3.3 and the code stopped working online as it was developed keeping in mind PHP 5. I again made the changes in the includes/modules/create_account.php and includes/templates/<your_template_folder>/templates/tpl_modules_create_account.php file and it started working online too.

Just beware of PHP and its versions or you’ll get into a lot of trouble 🙂

Hope the above helped

Redirect from SSL URLs to non-SSL URLs in Zencart

A client of the company I work for pointed out a problem in which some how the SSL version of his website got indexed into Google and other search engines.  Now this posed a problem for the client as search engines don’t rank web pages well which can be referenced from two or more different urls.

As the website was in Zencart I knew that there was no option than to write code to effect redirection from SSL to non-SSL url.

So, I opened the html_header.php file present in the includes/templates/<Your_Template>/common folder and wrote the following code at the top:

if ($_SERVER['HTTPS'] == 'on') {
if (isset($_GET['main_page'])) {
$strMainPage = $_GET['main_page'];
switch($strMainPage) {
case 'index':
case 'product_info':
case 'privacy':
$strURL = "https://". $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'] . '?' . $_SERVER['QUERY_STRING'];
$arrFrom[] = "https://";
$arrTo[] = "http://";

$strURL = str_replace($arrFrom,$arrTo,$strUrl);
header("Location: $strUrl");

The above code helped redirect those pages whose SSL version got indexed by mistake into the search engines. If you note from above I have not redirected post shopping cart pages as they need to be running in secure mode so that customer can enjoy safe and secure shopping.