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!

Admin Star Rating System

So, got an email from a potential client who wants to be able to make star ratings on a per product basis from within the admin;

I will like only myself (the admin or owner of the website) to grade the items that I’m selling with stars or with numbers. Let’s say that i have 5 stars icons and if the item is in good condition I will light up or show the 5 stars if it not so good maybe only 4 stars, and so on

So, I came up with a simple system.

In the Admin area;

As the only star rating is made by the shop owner, the only database entry is a single vote (0 or 1 or 2 or 3 or 4 or 5), so an “int” added to the products table is quite enough.

In the shop side;

Draw the value from the database and show a nice graphic.

Files changed were;

  • admin/categories.php (and language file)
  • product_info.php

And a few new files to show the stars.

Conclusion

And the cost for all this was just $75, and the time to get it from initial email to finished and working was less than a couple of hours. As you can see, it is sometimes preferable to pay a little amount to someone who can easily come up with code, than to waste a day or two trying to work out how to do something yourself.

Ship in Cart Reloaded

Been working quite a bit recently on a site that I performed a “makeover” some months ago. The site owner has been steadily adding in extra things to make the site much more responsive to himself (the shop owner) and the customer – it really is turning out to be a very nice osCommerce shop.

One of the most recent tasks I was asked to work on was the implementation of “ship in cart”, which shows a customer as estimate of how much shipping would be, prior to starting the checkout process. It’s well known in osCommerce circles that the lack of display of shipping costs is a major cause of abandoned carts!

There are already 3 or 4 “ship in cart” contributions, but none of them work exactly right, and most of them are ugly! So, the task was to come up with something that works more slickly and looks better.

Firstly, found what consider the better “ship in cart” contribution; http://www.oscommerce.com/community/contributions,7329 – however it seemed that the whole system only worked for Australian postcodes.

Some recoding time later and the system now works with multiple countries, based on postcode (eg for fedex, usps and so on).

The output looks like this;

And after searching the country/postcode, looks like this;

Obviously, if the shop had other shipping options (than Fedex) these would show.

I then sent the files on to my client, who changed them by removing the country (he ships only within the USA), and by adding a “throbber” image while the fedex quotes are returned to the page.

The whole system is powered by jQuery and as such does not need a page refresh to display the shipping quotes. I recoded the javascript in the existing contribution from around 20 lines of javascript to just 1 line. I’m very pleased with how this project turned out, and how the shop for which I made it is being built by it’s owner.

Warehouse Pick n Pack

Had email from a previous client yesterday who wanted the ability to list the items ordered for the previous day, but grouped by items. The reason for this is to get the warehouse staff to easier collect the items to be posted.

So, instead of a list like this:

1 x dvd-tsab
2 x msimexp
1 x dvd-tsab
1 x dvd-tsab
3 x msimpro
1 x msimpro

It would list like this;

4 x msimpro
3 x dvd-tsab
2 x msimexp

I quoted on the project, but the client found some base code to work with. I requoted on the project and am awaiting reply.

In the meantime, I continued to make the new module, which now looks like this;

Here you can plainly see that I list the orders, and at the side a combined list as required for picking and packing. Then I thought to add two extras;

1. a date picker, so that the shop owner can also look at other dates to be picked and packed.
2. a status chooser to drill down the items (they might already have been picked and packed for example).

In my testing, it seems to work quite well. I don’t know how useful it could be to the majority of shop owners.

Video