Category Archives: loaded commerce

USPS fix for November 2013 for oscommerce based carts

USPS, in an unannounced move last Friday 8th November, decided to change the way it supplied rates information via its servers to the many ecommerce carts around the global who request realtime USPS quotes. They changed the transfer-encoding to chunked. This is a more efficient way for this data to be transferred, so not really before time.

However it caught out a number of old usps modules which relied on an ever growing number of filters to clean up the rates data xml, because these scripts relied on the old http_client class which didn’t support dechunking this data cleanly.

For those modules that called upon curl this change would have made no difference, eg zencart, whose usps script uses curl primarily and only fallsback to http_client as a last resort.

Free code download I’ve modified the oscommerce usps USPS Rate V4 Intl Rate V2 module to check and use curl. This is written for Loaded Commerce 6.5 but can be modified for other version use.

 

how to optimize and speed up the database of your oscommerce-based store

There are a lot of things going on in the mysql database of an oscommerce-based store. Data is getting written into table rows, other data is getting updated, some deleted. Over time all of these operations leave behind old markers for rows, empty blocks that could be used but are being ‘avoided’ etc that can build up to the point of causing weird errors in the cart.

Optimization is the jargon for cleaning up the debris in the database so the read/write processes run more efficiently, giving you quicker query times and more accurate, trouble free store operations. It’s the server equivalent of defragmenting a disk drive of your computer.

The optimization process is simple and I’d recommend performing this regularly. Here’s how:

If you have a cPanel hosting interface, look for the link to the database utility phpmyadmin:

phpmyadmin

Select the database you want to work on, then check the size of what’s referred to as ‘overhead’ – ie junk that prevents the database from working as smoothly and efficiently as it could:

overhead figure

If there is some, remove it by selecting All Tables (1) and then from the dropdown in the middle, Optimize Tables (2) :

select all tables and optimize

After the procedure has completed, the overhead count on the lower right should show 0 bytes. Job done :

overhead removed

How often do you have to optimize a database?
I recommend every 3 – 4 weeks.

Anything else we should do when there?
Never hurts to run Analyse Table as well as this is like a wheel alignment for the indexes and keys used on tables.

I’d also recommend truncating (ie emptying) the frequently updated tables like whos online, visual_verify_code and sessions.

If you need help with this contact me or alternatively I can provide maintenance like this (and more) on a support contract for your site

products attributes not showing on confirmation email

This bug which causes the product attribute information not to appear on order confirmation emails sent by the Cre Loaded or Loaded Commerce admin has been around forever on Cre Loaded (and now Loaded Commerce 6.5.x) carts.

Replicate as follows:
1. Admin >> Configuration >> Download … set ‘Enable Download’ to false


2. Place order for product with attributes
3. Receive email without mention of product attributes

The fix:
Find the file /checkout_process.php, backup first, then anywhere from line 450 through to 510 (depends on your version) replace with the block as shown

$products_ordered_attributes = '';
  if (isset($order->products[$i]['attributes'])) {
    for ($j=0, $n2=sizeof($order->products[$i]['attributes']); $j<$n2; $j++) {
      $sql_data_array = array('orders_id' => $insert_id,
                              'orders_products_id' => $order_products_id,
                              'products_options' => $order->products[$i]['attributes'][$j]['option'],
                              'products_options_values' => $order->products[$i]['attributes'][$j]['value'],
                              'options_values_price' => $order->products[$i]['attributes'][$j]['price'],
                              'price_prefix' => $order->products[$i]['attributes'][$j]['prefix'],
                              'products_options_id' => $order->products[$i]['attributes'][$j]['option_id'],
                              'products_options_values_id' => $order->products[$i]['attributes'][$j]['value_id']);
      tep_db_perform(TABLE_ORDERS_PRODUCTS_ATTRIBUTES, $sql_data_array);
      if (DOWNLOAD_ENABLED == 'true') {
        $attributes_query = "select pad.products_attributes_maxdays, pad.products_attributes_maxcount , pad.products_attributes_filename
                               from " . TABLE_PRODUCTS_ATTRIBUTES . " pa,
                                    " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad
                             where pa.products_id = '" . $order->products[$i]['id'] . "'
                               and pa.options_id = '" . $order->products[$i]['attributes'][$j]['option_id'] . "'
                               and pa.options_values_id = '" . $order->products[$i]['attributes'][$j]['value_id'] . "'
                               and pa.products_attributes_id = pad.products_attributes_id";
        $attributes = tep_db_query($attributes_query);
        $attributes_values = tep_db_fetch_array($attributes);
        if ( isset($attributes_values['products_attributes_filename']) && tep_not_null($attributes_values['products_attributes_filename']) ) {
          $sql_data_array = array('orders_id' => $insert_id,
                                  'orders_products_id' => $order_products_id,
                                  'orders_products_filename' => $attributes_values['products_attributes_filename'],
                                  'download_maxdays' => $attributes_values['products_attributes_maxdays'],
                                  'download_count' => $attributes_values['products_attributes_maxcount']);
          tep_db_perform(TABLE_ORDERS_PRODUCTS_DOWNLOAD, $sql_data_array);
        } // download filename loop ends
      }  // download attributes loop ends

        if( (!empty($order->products[$i]['attributes'])) ){
          $products_ordered_attributes .= "\n\t" . (isset($order->products[$i]['attributes'][$j]['option']) ? $order->products[$i]['attributes'][$j]['option'] : '') . ' ' . (isset($order->products[$i]['attributes'][$j]['value']) ? $order->products[$i]['attributes'][$j]['value'] : '') . ' ' .
          ( (isset($order->products[$i]['attributes'][$j]['price']) && ($order->products[$i]['attributes'][$j]['price'] > 0) ?
           $order->products[$i]['attributes'][$j]['prefix'] . ' ' . $currencies->display_price($order->products[$i]['attributes'][$j]['price'], tep_get_tax_rate($products[$i]['tax_class_id']), 1): ''));
     //number_format($order->products[$i]['attributes'][$j]['price'],2,'.','')

      } // attributes not empty loop
    } // loop through the attributes
  }  // attributes set?