Category Archives: zen-cart

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

how to setup the paypal website payments standard module

The Paypal Website Payments Standard module is a basic ‘button’ type payment facility that allows websites to collect payment from Paypal and non-Paypal customers alike, via hosted pages on the Paypal server.

This post is an outline of settings that work for me using a cre loaded 6.4.1a CE version cart and the default payment module that is supplied with it. No modifications have been made to the original Cre Loaded Paypal files, which are largely based on an old oscommerce version and incorporate the Paypal IPN (Instant Payment Notification) function as well.

There are two parts to setting up the Paypal Website Payments Standard module – firstly the module itself through the admin, and secondly your Paypal account settings. It’s reasonably easy to end up with failed transactions or inventory not being adjusted for sales correctly or Paypal payments but no order in the admin etc, with different settings.

1. Check the Paypal Website Payments Standard module

screenshot of paypal website payments standard bottom half of module settingspaypal module top half of column settings

 

 

  • Check the settings in the cart’s Paypal Website Payments Standard module and note the text in the images (see right) in red – I’ve found that if the settings for these particular entries aren’t as shown, the module may not work as expected.
  • Also, it is a good idea to uninstall and reinstall the module if you are experiencing problems.

2. Check your Paypal Account settings

  • Login to your Paypal account – which should be either a Business or Premier account, as this module will not work with a personal account. As mentioned above, make sure the email addresses in Paypal Website Standard module match exactly the email address used in your PP account. This is often the reason for module problems.
  • Next, go to Profile -> My Selling Tools:

Sales Tax -> Update:
None set.

Website Preferences -> Update:
Auto Return = On
Return Url = https://www.yourstore.com/checkout_process.php
Payment Data Transfer = Off
Encrypted Website Payments = Off
Paypal Account Optional = On (if you want to prevent Paypal from hassling customers into setting up a Paypal account)
Contact Telephone = Off
Express Checkout = No

Instant Payment Notifications -> Update:
Switch on IPN
Notification URL = https://www.yourstore.com/checkout_process.php

Shipping Calculations -> Update:
None set.
Tick “Click here to allow transaction-based shipping values to override the profile shipping settings listed above”

Please note the Auto Return and IPN Notification urls ARE THE SAME: both are checkout_process.php … not checkout_success.php or ipn.php etc.

If you have a Personal Paypal account you can test your settings now with a transaction. Alternatively use the Paypal Developer area and sandbox test accounts. The order should go through smoothly using either the 3-page or 1-page checkout and an IPN result should go through to your store’s email address.

For zen-cart setup try the zen-cart wiki page about setting up the Paypal Website Payments Standard module here
For oscommerce support try this forum how to setup the Paypal Website Payments Standards module here

(If you’ve tried all of the recommendations in this post and it still isn’t working, please use the contact me above for a quote.)