Category Archives: osCommerce / oscMax

osCommerce and its derivatives. I am working mostly with osCMax these days.

osCmax 2.5 thumbnail images not showing after upload case sensitive on Linux

The issue here was that loading some images as extra’s were not appearing as thumbnails in the product display. At least some of them were not. The key was that the ones that worked had a lower case file extension and the ones that did not had upper letters. i.e. .jpg and .JPG

I tracked this to being that the Dynamic Mopics for extra product images is case sensitive. Loading an image from another system (Windows perhaps?) with an upper case .JPG extension will not display the thumbnail without specifying the upper case extension in the admin settings for Dynamic Mopics which has all lower case extensions by default.

Using the default installation admin settings. Load a main product image and an extra jpg type file image with the extension as uppercase .JPG
Using the catalog user view inspect the product and there are no thumbnails displayed.
Modify the admin setting to include JPG (as distinct from jpg) in the viewable formats and the images display.

A simple fix is to force lower case extensions for all image uploads. This is a simple change.

Edit the file catalogadminincludesmodulesproduct_image_upload.php

line 23
[text] $ext = substr(strrchr($main_image, ‘.’), 0);[/text]
becomes
[text] $ext = strtolower(substr(strrchr($main_image, ‘.’), 0));[/text]
(adding the additional function as a wrapper, do not forget the closing parenthesis)

Updating an existing database is also relatively simple in phpMyAdmin or similar select the products table and run the following:
[text]UPDATE products SET products_image = REPLACE(products_image, ‘.JPG’,’.jpg’);[/text]

if the user wants to test the impact of this first display the table with the changes with:
[text]SELECT `products_id`, REPLACE(products_image, ‘.JPG’,’.jpg’) AS ‘products_image’ FROM products;[/text]

osCmax svn and reporting which revision is in use

So I started this morning looking to download the latest svn version of osCmax. I am sure the elves have been grinding at the various parts of osCmax providing general users with an improved system for ecommerce.

So I go to http://code.google.com/p/oscmax2/source/checkout and grab the svn command line and use it on my dev linux server to get the latest version.

Start up a fresh site and run the Installer. Which politely informs me that I am using osCMax Power E-Commerce v2.0.25.

WTF? It is 2.5RC2 in the latest download release, why is it still displaying v2.0.25? Hummphhh! I’ve got multiple svn versions and I try to use the code and templates that I develop across the latest public release and more recent svn’s in order to get the best from my sites.

EDIT: The above 2.0.25 was correct because the oscmax2 svn source page gives the latest release version information resulting in that version being downloaded by svn. The more appropriate command line is for a linux testing svn download is
[text]
#svn checkout https://oscmax2.googlecode.com/svn/branches/dev/2.1/ oscmax2-read-only
[/text]

So is there a better way? How about getting the svn version number and publish it alongside the PROJECT_VERSION.

To keep this as a short post this was my way around this:

Create a new file in catalog/includes/svn.php and paste in the following code:

[php]
<?php
/*
$Id: svn.php

osCMax Power E-Commerce
http://howden.net.au/thowden/

Released under the GNU General Public License
*/
// A quick’n’dirty test of publishing to a developer the svn version that is being used

// svn numbering if .svn directory exists then assume that the developer would like to see
// the current version in addition to the PROJECT_VERSION which is consistently out of date
if (!defined(‘REVISION’)) {
if (file_exists(‘.svn/entries’)) {
$svn = file(‘.svn/entries’);
if (is_numeric(trim($svn[3]))) {
$version = $svn[3];
} else { // pre 1.4 svn used xml for this file
$version = explode(‘"’, $svn[4]);
$version = $version[1];
}
define (‘REVISION’, trim($version));
unset ($svn);
unset ($version);
} else {
define (‘REVISION’, 0); // default if no svn data avilable
}
}

?>
[/php]
Save the file and change to edit the install/includes/application.php:
At line 37 which shows

[php]
define(‘PROJECT_VERSION’, ‘osCmax v2.5.0’);
[/php]

and change or insert the following in place of line 37, noting that this is a if else wrap around for the line.

[php highlight=”3″]
require(‘../includes/svn.php’);
if (REVISION == 0) {
define(‘PROJECT_VERSION’, ‘osCmax v2.5.0’);
} else {
define(‘PROJECT_VERSION’, ‘osCmax v2.5.0 svn: ‘.REVISION);
}
[/php]

Then repeat for the main admin application catalog/admin_dir/includes/application_top.php at line 32.

Then repeat again for the main application edit catalog/includes/application_top.php at line 45 except that you removed the ../ from the require() so it is
[php]
require(‘includes/svn.php’);
[/php]

What this does is check for the .svn directory to exist. It will not(should not?) exist in a packaged install like 2.5RC2 but will exist in an svn download. Appending the REVISION to the PROJECT_VERSION constant means that it will appear in all the relevant places in the osCmax site.

Thanks to AD7six at the Bakery for the svn code snippet.

Edit: Thanks to Michael for correcting my understanding of the release version being available from the svn by default and not the latest dev version. However, since writing this post version 2.5 has been released !!

PHP mySQL osCmax and converting databases

This week I started looking at the conversion of some older osCommerce databases and moving to osCmax. I’m almost on top of the templating but need to look at getting live data into the system to work with the templates.

This post is not about that but about the bits of code that I’ve worked on to get my converter working.

Connecting to two databases

Stackoverflow came to my aid again! The post on PHP connecting to two databases seemed fine but I did not read completely the first (or second) time and only after doing a decent read realised that there was the additional parameter for a new connection. Setting that worked fine.

There was also interesting reading on the logic and best practice around opening and closing database connections.

Over at http://net.tutsplus.com/ there are some best practices for php and mysql coding.

Altering a column in a table

I picked up a script from EdmondsCommerce (nice play on eCommerce!) and the script worked fine. I then liked the concept of the script posted by Arnan in the comments to the post but that did not work. I also noted the comment to use ‘IF NOT EXISTS’ except that is only valid for tables not for columns, at least not in my reading.

Adding a new column in a table

Following on from the alter script I created a new function and modified Arnan’s code to work and dealt with some potential issues with columns existing or not. Which led me to……

Logging Whats Happening

Using ‘echo’ to the screen is the common debug process for PHP code. But in this case I am building something a bit more robust and potentially big once I get to grips with over 100 tables in the databases.

I think an interactive script presenting information to the screen is good but not as a long scrolling list. I’d prefer to see a screen that sits and presents the current processing. In addition I want a log file to report on all actions and a separate log for the data line items just in case.

So I am now on the hunt for error logging and reporting and a great start is this post about a Logging class from http://www.redips.net/php/write-to-log-file/ that I have left as is and it works a treat.

Next I will need to come back to progress indicators like http://valums.com/ajax-upload/ and maybe here http://www.tutorialized.com/view/tutorial/PHP-GD-Progress-Bar-Demo/779 or http://davidwalsh.name/progress-bar-animated-mootools. The issue with this is how to update client side the progress occurring server side during a database table pass that in theory is either 0% or 100% and no feedback in between without splitting the query into segments and passing a client-side update. Needs more thought.

Converting osCommerce to osCmax mySQL database structure

I am in the process of converting some older osCommerce based web sites to osCmax and the underlying database structure is different. The following is the step by step that I have worked through for one site.

Updated: 24th Aug 2011: Embedded updates for tables in the following as I worked through the conversion.

In preparing for this I have exported the structure only from the old site and from the current osCmax svn version that I have running on my system. I use Beyond Compare from Scooter Software for my file / folder comparison work and I have opened up the two structure files for comparison.

Note: the old osCommerce is based on an early 2.1 version updated and modified for unique requirements over the 10 years or so it has been in operation. It’s been a reluctance to even start contemplating this change that has led it to be so long.

Table: address_book:

The first two fields are in reverse order and the address_book_id is now a unique auto_increment field.
The address_book_id field in the old db will need to be dumped and the auto_increment value accepted.
entry_company_tax_id is a new field
entry_telephone is no longer used – checking my data they are all NULL so not required

Tables: address_book and address_book_new both exist in the old osCommerce database but not in osCmax. Both tables are populated so I’ll need to compare the data in the two.
Update: address_book_new is from an attempt to convert to creloaded some years ago, it is not an osCommerce table and can be ignored.

Table: anti_robotreg does not exist in osCmax and is empty in the old system. Dump this.

Tables: admin, etc. through to authors_info do not exist in the old system. These can be ignored.

Table: banners and banners_history no change.
Update: There is data in the new system that can be cleared.

Table: cache does not exist in the old.

Table: categories. Need to add new fields to old to align with new.
after sort_order
`categories_featured_until` date default NULL,
`categories_featured` tinyint(1) default ‘0’,
after last_modified add
`categories_hide_from_groups` varchar(255) NOT NULL default ‘@’,

In old remove the fields
`categories_customizable` tinyint(1) DEFAULT ‘1’,
`tpl_file` varchar(30) DEFAULT NULL,
these are not in the new

Table: categories_description
Add two new fields to old to align with new
`categories_description` text,
`categories_heading_title` varchar(64) default NULL,

Update: the categories column order changed to match and confirmed that data in new can be cleared.

Tables: configuration, configuration_group, counter, & counter_history are the same barring some minor formatting and default settings that should not impact.

Update: configuration table has a huge difference in the number of entries. The data content needs to be compared and merged rather than overwritten.

Table: countries
Add a new field to old to align with new
`active` tinyint(3) unsigned default ‘1’,

Table: currencies, no change.

Table: customers
Add new field after customers_id
`purchased_without_account` tinyint(1) unsigned NOT NULL default ‘0’,

Change `customers_default_address_id` int(5) to `customers_default_address_id` int(11)
Add new fields after customers_newsletter
`customers_newsletter_type` varchar(32) NOT NULL default ‘html’,
`guest_account` tinyint(1) NOT NULL default ‘0’,
`customers_login` varchar(96) default NULL,
`customers_group_name` varchar(27) NOT NULL default ‘Retail’,
`customers_group_id` int(11) NOT NULL default ‘0’,
`customers_group_ra` enum(‘0′,’1’) NOT NULL default ‘0’,
`customers_payment_allowed` varchar(255) NOT NULL,
`customers_shipment_allowed` varchar(255) NOT NULL,
`customers_paypal_payerid` varchar(20) default NULL,
`customers_paypal_ec` tinyint(1) unsigned NOT NULL default ‘0’,

Table: customers_basket no change.

Table: customers_basket_attributes
add new field after products_options_value_id
`products_options_value_text` varchar(32) default NULL,

Tables: customers_groups is new customers_info no change.

Table customers_new is in old but not new. Need to validate data in this and customers prior to export.

Tables: customers_notes and wishlist related, extra fields, and feeds are only in new.

Table: geozones no change, new tables for google etc only in new.

Table: languages
Add new field after sort_order
`meta_keywords` varchar(255) default NULL,

Table: manufacturers
Add new fields after manufacturers_image
`manufacturers_featured_until` date default NULL,
`manufacturers_featured` tinyint(1) default ‘0’,
`manufacturer_featured_until` date default NULL,
`manufacturer_featured` tinyint(1) default ‘0’,

Table: manufacturers_info
Add new field after manufacturers_url
`manufacturers_description` text,

Table: orders
Add new field after customers_address_format_id
`customers_dummy_account` tinyint(3) unsigned NOT NULL,

Remove `delivery_telephone` varchar(32) DEFAULT NULL, from old table as not in new data is all null.
Remove `billing_telephone` varchar(32) DEFAULT NULL,

Old field `cc_ccv` varchar(3) DEFAULT NULL COMMENT ‘CCV 3 Digits’, is not in new but seems like a necessary field. Need to include for the moment and dump later if not required. Some values are present.

Old field `payment_method` varchar(32) NOT NULL DEFAULT ”, is changed to
`payment_method` varchar(255) NOT NULL DEFAULT ”
Not sure how this interacts with payment processes. Will retain field as payment_method_old leaving the new field as payment_method. Refer to table orders_pay_methods which is also retained and linked to this field but is not in the new structure.

Three fields in old can be dropped as they are all empty.
`account_name` varchar(32) NOT NULL DEFAULT ”,
`account_number` varchar(20) DEFAULT NULL,
`po_number` varchar(12) DEFAULT NULL,

New fields to add after currency_value
`paypal_ipn_id` int(11) NOT NULL default ‘0’,
`fedex_tracking` varchar(20) NOT NULL,
`purchased_without_account` tinyint(1) unsigned NOT NULL default ‘0’,
`shipping_tax` decimal(7,4) NOT NULL default ‘0.0000’,
`shipping_module` varchar(255) default NULL,

Table: orders_pay_methods not in new. Retain in new until determining how this works.

Table: orders_premade_comments not in old.

Table: orders_products
Add new field after `products_model`
`products_code` varchar(40) NOT NULL,

Add new field after `products_quantity`
`products_stock_attributes` varchar(255) default NULL,

Table: orders_products_attributes
Add new field after `products_options_values`
`code_suffix` varchar(16) NOT NULL,

Add new fields after `price_prefix`
`weight_prefix` char(1) NOT NULL,
`options_values_weight` decimal(6,3) NOT NULL default ‘0.000’,

Table: orders_products_download no change.

Table: orders_ship_methods not in new. appears to relate to new field in orders regarding shipping_module. Retain and test post change.

Table: orders_status
Add new fields after `orders_status_name`
`public_flag` int(11) default ‘1’,
`downloads_flag` int(11) default ‘0’,

Tables: orders_status_history and orders_total no change

Table: packaging is new

Table: paypalipn_txn appears the same / similar to paypal_ipn. Will need to look at data changes.

There are many fields in old and not in new and vice-versa. Trying to match the data will be time-consuming. Need to assess if this needs to be done or not. Retain old table as itself for the time being.

Table: pm_configuration is new.

Table: products is also complex in the changes. Need to review.

Table: products_attributes
Add new fields after `options_values_id`
`code_suffix` varchar(16) NOT NULL,
`suffix_sort_order` int(4) NOT NULL default ‘0’,
and Add new fields after `price_prefix`
`weight_prefix` char(1) NOT NULL,
`options_values_weight` decimal(6,3) NOT NULL default ‘0.000’,
`products_options_sort_order` int(10) unsigned NOT NULL default ‘0’,

Table: products_attributes_download no change

Table: products_description
Add new field after `products_name`
`products_short` text,

Add new fields after `products_description` text,
`tab1` text,
`tab2` text,
`tab3` text,
`tab4` text,
`tab5` text,
`tab6` text,

Table: products_groups is new

Table: products_notifications no change

Table: products_options

Add new fields after `products_options_name`
`products_options_track_stock` tinyint(4) NOT NULL default ‘0’,
`products_options_type` int(5) NOT NULL default ‘0’,
`products_options_length` smallint(2) NOT NULL default ’32’,
`products_options_comment` varchar(32) default NULL,
`products_options_sort_order` int(10) unsigned NOT NULL default ‘0’,

Table: products_options_products is not in new.

Table: products_options_description is new

Tables: products_options_values, products_options_values_to_products_options are the same

Tables: products_price_break & products_stock are new

Table: products_to_categories is the same

Tables: products_to_discount_categories, products_xsell, & quick_links are new

Table: reviews
Add new field after `reviews_rating`
`approved` tinyint(3) unsigned default ‘0’,

Tables: scart, all search*, shipping_manifest and slideshow are new.

Table: sessions no change.

Table: specials
Add new field after `status`
`customers_group_id` int(11) NOT NULL default ‘0’,

Table: suppliers is custom to the old system. links in products and currencies need to be managed. relates to proposed module to manage outbound purchase orders and product costing.

Table: specials_retail_prices is new

Tables: tax_class, tax_rates no change

Table: theme_configuration is new

Table: tep_meta_tags is not in new

Tables: topics* and usu_cache are new

Table: whos_online
Add new fields after `ip_address`
`hostname` varchar(255) NOT NULL,
`country_code` varchar(2) NOT NULL,
`country_name` varchar(64) NOT NULL,
`region_name` varchar(64) NOT NULL,
`city` varchar(64) NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,

Add new fields after `last_page_url` text NOT NULL,
`http_referer` varchar(255) NOT NULL,
`user_agent` varchar(255) NOT NULL,

Tables: zones* are no change

Next is to build a script to make the structure changes and then extend it to convert the data. I need this in order to do some trial runs before a live cut-over. It will be useful for the creLoaded stores that I need to do as well.