Database interrogation using PHPMyAdmin

Although I have not logged into the osC forum for a few weeks, I have been directed to a number of threads where users are wanting help. I’ll log in the forum sometime soon.

In the meantime, someone for whom I have previously done some consulting asked this;

I need to run an SQL query against the database to enable me to download a file with the following data for each order:

Date
Order Number
Postage Cost (exc Tax)

I need this for a tax return.

Very simple in PHPMyAdmin. What we have to do is draw out the info from the “orders” table and the “orders_total” table;

SELECT o.date_purchased AS ‘Purchase Date’, o.orders_id AS ‘Order ID’, ot.value AS ‘Postage Cost’
FROM `orders_total` AS ot
JOIN `orders` AS o
ON ot.orders_id = o.orders_id
AND ot.class = ‘ot_shipping’
ORDER BY o.orders_id DESC

What this produces is a list like this;

which shows the information needed.

What if you want to limit the results to the last x months?

Easy enough, just add an extra line to the SQL like this:

SELECT o.date_purchased AS ‘Purchase Date’, o.orders_id AS ‘Order ID’, ot.value AS ‘Postage Cost’
FROM `orders_total` AS ot
JOIN `orders` AS o
ON ot.orders_id = o.orders_id
AND ot.class = ‘ot_shipping’
AND o.date_purchased > DATE_SUB(NOW(), INTERVAL 4 MONTH)
ORDER BY o.orders_id DESC

Now you get a list limited to the last 4 months, counting back from now. Change the 4 to any number of months. You can also use years, days, even seconds.

What if you want to limit the results to only show those amounts under 10?

Add in a line of code like this;

SELECT o.date_purchased AS ‘Purchase Date’, o.orders_id AS ‘Order ID’, ot.value AS ‘Postage Cost’
FROM `orders_total` AS ot
JOIN `orders` AS o
ON ot.orders_id = o.orders_id
AND ot.class = ‘ot_shipping’
AND ot.value < '10' ORDER BY o.orders_id DESC Again, very simple - we are limiting the result set to those entries that are less than 10, using the ot.value line. Change the 10 to any figure. And finally, let’s limit the results to the last 3 months, and only show the items that are 5.99 or greater;

SELECT o.date_purchased AS ‘Purchase Date’, o.orders_id AS ‘Order ID’, ot.value AS ‘Postage Cost’
FROM `orders_total` AS ot
JOIN `orders` AS o
ON ot.orders_id = o.orders_id
AND ot.class = ‘ot_shipping’
AND o.date_purchased > DATE_SUB(NOW(), INTERVAL 3 MONTH)
AND ot.value > ‘5.99’
ORDER BY o.orders_id DESC

Isn’t that easy? Certainly a lot less work than downloading and working with excel or installing some contribution that takes an hour and ends up not doing what you want it to do!

Add Customer Reviews in Admin

A fellow osCommerce professional asked if I had heard of an add-on that allows the Shop Owner to add a customer review. I was sure that I had created something similar in the recent past, but could not find it, so I set out to code it up.

Step 1: add a “add a new review button”.

When clicked, this leads to;

Step 2: the “adding a new review” form.

Here the Shop Owner can choose the product being reviewed, chosoe the customer doing the review, insert the review text and the review rating. Obviously, sometimes customers will send Shop Owner email about the product they purchased, and this makes it easy to add the review…without having to ask the customer to sign and recreate the email sent to Shop Owner.

As you can see, fairly straightforward. No bells and whistles, just does what it needs to do. Let me know if you have any ideas to make it better?

Turn off Payment method based on currency used

Had an email overnight from an osCommerce user who needs to turn on and off payment methods based on what currency is being used in the checkout. I thought I had covered this already in the blog, but could not find it. So here we go;

1. Add an extra input to each payment method used

This allows us to add a list of currencies supported by the payment method.

As you can see in the example above, I have set the currencies to GBP (British Pounds) and EUR (European Euros).

Code changes (taking cod.php as the example):

Find:
function install() {

Add an extra line of code, as so:
tep_db_query(“insert into ” . TABLE_CONFIGURATION . ” (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) values (‘Currencies available for this payment method.’, ‘MODULE_PAYMENT_COD_CURRENCIES_ACCEPTED’, ”, ‘Comma separated list of accepted Currencies.’, ‘6’, ‘0’, now())”);

Find:
function keys() {

Add:
‘MODULE_PAYMENT_COD_CURRENCIES_ACCEPTED’,

After:
‘MODULE_PAYMENT_COD_ZONE’,

Turn off the COD module if you already have it turned on. Turn it on again to reset it. You now have the necessary input box to allow you to add the currencies acceptable.

2. Turn the module off if an unacceptable currency is used

Still working on cod.php ….

Find:
// disable the module if the order only contains virtual products

Above this, add:
[php]// disable the module based on available currencies
$good_currencies = explode(‘,’, MODULE_PAYMENT_COD_CURRENCIES_ACCEPTED);
if (!in_array($order->info[‘currency’], $good_currencies)) {
$this->enabled = false;
}[/php]

Here, we create an array of acceptable currencies using explode. We then compare the currency being used to those acceptable currencies. If the currency being used is NOT found in the list, then the module is turned off.

Easy as 123.

You would need to do similar for every payment module that you use. Make sure to change this: _COD_ to reflect the name of the module you are amending.

Upcoming Project – Mega Drop Down Menu into osCommerce

Upcoming project is to take the Mega Drop Down Menu of Soh Tanaka and integrate it into osCommerce.

It is very straightforward in basic HTML (I managed to get it working within a couple of minutes) based on the examples given, but because I want the category structure to come direct from the database I needed to create some flavour of osCommerce standardised interface to grab the content from the DB, and output it in the required HTML.

It’s always a problem to work with the category structure of osCommerce – especially if the output is required in a certain format. There are a few “css” based category files floating about, but none of them output the HTML in the way that the Mega Drop Down requires, hence the need for a new way to do it, I’m almost there, but not quite right at the moment.

Anyway, should be an interesting project when it does go ahead – here’s proof of concept…

So, we can see that it will work, what I need to do now is make sure that the category structure is correctly outputted automatically rather than my proof of concept which was done manually.

WordPress and osCommerce – Admin 404

I cleaned and secured a osCommerce site the other day which is run in a subdirectory of a hosting account, like so /public_html/shop/.

The admin area is at /public_html/shop/admin/ [ actually it’s in a randomly named admin folder, but for the blog post, let’s assume it is at /shop/admin/ ]

In the root of the website hosting or in other words /public_html/ is an installation of WordPress. With me so far?

/public_html/ <-- wordpress /public_html/shop/ <-- oscommerce /public_html/shop/admin/ <- oscommerce admin area Part of the security lockdown is to protect the admin area by htaccess and htpasswd. This is server level security that ALL osCommerce sites MUST have.

I then delivered the finished site to the client and all was well. Fast forward a day or two, and I get an email stating that the admin area was returning a 404 error. That is odd, as it is definitely there.

So, what changed from when I delivered a working shop and admin area? Only 1 thing – the htaccess in the public_html that controls the wordpress URL rewriting.

The client went ahead and removed the htpasswd protection from the osCommerce admin area, which allowed access. Obviously this is undesirable and I let the client know this. I was shocked and amazed at this response from the clients host to the client;

The htaccess and htpassword files are a security measure against a problem that does not exist anymore in the newest version of osC.

What an absolute joke – this host really needs to get a clue!

What is needed is a way for WordPress to do it’s rewriting AND for osCommerce Admin area to be secured via htpasswd…

The Solution

With some Google digging, it transpires that WordPress sometimes has problems with htpasswd’d folders higher up in the website hosting account. Hence the 404 error.

With a bit more Google digging, the solution is as presented on this page. Simply make a couple of error pages, and add in the two ErrorDocument directives to the WORDPRESS htaccess file.

So, next time your osCommerce Admin area presents a 404, and you are running WordPress on the same hosting account, this could be the solution for you.

Important note; I don’t really know enough about WordPress to state for certain just exactly what the problem is. What I am able to do is hunt Google, copy and paste, and write a blog post about it for people having similar problems in the future.

Reviews in Product Page?

Just a quick post as I wanted to remind myself to write more on this. Presently working on some ideas for a client, one of his needs is to have the “reviews” adding functionality in the product page.

As we know that to place a review the customer MUST be logged in, I decided to test if the customer is logged in and then present the information like this;

If NOT logged in;

The form is not active, and I lowered the opacity, as well as put a “you must login” message underneath.

If they ARE logged in;

The form becomes active, the opacity is removed, and the button appears instead of the message!

A nice little mod, that did not take too long to code up, as most of the code already exists in other pages, so just a matter of copy/paste. I also made a little graphic to link to already written reviews – this does not show if there is no reviews on the product being looked at.

Remove all Items from Shopping Cart

In this post, I’m going to show you how to add a new button into the shopping cart page that will allow your customers to remove all items from their shopping cart in just one click…

In standard osCommerce, each product has it’s own individual “remove” link – which is fine and works well. My idea is to remove the whole lot in one go…

It looks like this:

Step 1 – add the new button to the shopping cart page

All we need to do here is add a new button just like the existing “checkout” button in shopping_cart.php – but we make it different by using different parameters;

Explanation of parameters;

text – the text that the button shows, see step 2.
icon – the tiny icon that shows in the button, I’ll cover this in a future post, but you can see the available icons at the bottom of this page.
action – what the button does, see step 3

Step 2 – add the language define to the english language file

Should be completely self explanatory…

Step 3 – making the “action”

This is where we delve a little bit into the “engine” of osCommerce. Open up the file includes/application_top.php and find this line of code;

switch ($HTTP_GET_VARS[‘action’]) {

This is where we can set up the new action called “clear_cart”. Without going into too much depth, the action needs to do the following;

1. Clear the Cart
2. Redirect back to the shopping_cart.php page
3. Stop the script continuing by “breaking” it

To clear the cart, we can use the inbuilt function “remove_all” from the shopping cart class. To use it, we need a line of code like this; $cart->remove_all();

To redirect, we can use the inbuilt redirect function called “tep_redirect” – we just need to tell it where to redirect to.

To stop the script continuing to process, we use the php break command.

So the portion of new code looks like this;

And here it is in action;

Recently Viewed infoBox in 2.3.1

I needed to code up a feature for a client which tracks a visitor “product_info.php” page views to basically show them what they recently viewed. I have to say that although I never thought about it, this is quite handy to have, as I have often been on the point of buying, looked at something else, then forgotten what product I was about to buy.

So, as the client is on 2.3.1 and we all know that there are barely any well written contributions for it, I decided to hack up an older contribution from kymation (Jim Keebaugh) who I know writes code in a very decent way…his contribution is here (5 Nov 2008 download).

The parts I used were the changes in product_info.php and logoff.php, this gives enough information to build an array of product IDs that have been visited. The array of product IDs is stored as part of the browsing session.

I then created a new infoBox to take that array and build up a gallery of recently viewed products. It looks like this;

I particularly made the images quite small in order to fit 4 in a row. The images are also linked to their respective product pages.

If there is nothing in the array (or, in plain english, the person viewing your site has not looked at any products) then this box does not show at all, no point in showing a box that has no contents!

I’m still fine-tuning the system, but overall, it seems to be solid.

Give me your views on it, please. Can it be changed to make it better, more interesting? I did think about changing the code showing the images to make it more like a fade in and out type effect, but then thought “whats the point” – after all this is a navigation history so everything needs to be seen at the same time.

960 Grid Tutorial – Understanding Alpha and Omega classes

Number three in my short series explaining a little bit about how the 960 Grid system works in osCommerce…

Alpha and Omega Classes

The Alpha and Omega class is used to align the first and last “child” divs of a parent div to the outermost margins of the page…what is a child div? Have a look at this code;

Which produces this layout;

The next image shows the same code with a bit of explanation;

If I remove the alpha and omega class, the display reacts badly;

Why is this? It is because the “alpha” and “omega” classes “remove” the gutter left and right (not exactly but it’s the easiest way to explain it). In the image below I have shown this in red to illustrate the area that adding the “alpha” class changes;

Adding the “alpha” class back in;

shifts the yellow div back over to it’s rightful place;

Yet the pink child div is still in the wrong place. This is because we need to add the “omega” class to it. Once done, everything lines up perfectly;

Summary

The “alpha” class is used on the FIRST “child” div. It shifts the div to the left to make it line up with your design.

The “omega” class is used on the LAST “child” div. It shifts the div to the right to make it line up with your design.

You should use “alpha” and “omega” classes when the div’s you use them on are also grid_* divs, and are inside a parent grid_* div.

As always, if anything looks wrong, let me know – I am by no means an expert on 960 grid, and always looking to learn more about it. These tutorials are only made by my trial and error over the past few weeks.

960 Grid Tutorial – Understanding Push and Pull classes

Number two in a mini-series explaining a little bit about how the 960 Grid system works in osCommerce.

Push and Pull Classes

The Push class is used to “push” the DISPLAY of a div, and the Pull class is used to “pull” the DISPLAY of a div. Imagine an anchor at the left hand edge of your page, and then imagine yourself pushing content away from that anchor, or pulling content towards that anchor…

Take this standard code;

Which we know will produce a 2 column layout; 25% menu and 75% content area, like this;

That should be really easy to understand what is going on, right? Now, what if you want those columns to be the other way around? So, you end up with the menu on the left hand side and the content area on the right?

You could do this;

which would be just fine, and does exactly what we require;

But, the downside of this is that the menu is being rendered before the content, which is undesirable for Search Engine Optimisation (SEO), and undesirable for Accessibility (with regards to screen readers etc).

Instead, we can simply PUSH and PULL the display of our menu and content div’s to achieve the effect we need…and it is very simple…I’m reverting to the code back we started with and will instead add an extra PULL class to the menu div;

This has PULLed the menu div 18 blocks towards our “anchor” (the left hand edge of the page);

However, as you can see, it now displays on top of the content, which is undesirable. So what do we have to do?

The obvious answer is to PUSH the content div away from our anchor by 6 blocks;

so the display now shows;

which is exactly how we want it. And we have achieved this nicely in terms of SEO and of Accessibility. The content is being rendered in our underlying code BEFORE the menu, but the menu is displaying first in our output.

Hopefully this intro to PUSH and PULL classes will help you to design your osCommerce site with thought to SEO and to Accessibility.

Please remember that I am far from expert when it comes to 960 Grid, so if anything looks wrong, please let me know!