Get Your Monthly Zen Cart Sales With Just One Line of Code

[Quail Valley]

I was talking on the phone to client today and we found we needed a quick schedule of her monthly online sales figures.

Ideally we’d have simply gone into her Zen Cart Admin area and found them there, but that’s not currently an option. There wasn’t time to write a report, or even test and install the existing sales report add-in module (of which curiously I’ve never previously had need).

But this is exactly the sort of situation where SQL, the most commonly database query language can extract information with amazing speed and efficiency.

Happily the following little snippet of SQL pulled and prepared this information from her database in a matter of seconds – almost all of which were due to the slow speed of my typing!



Here’s the query that I used:

SELECT LEFT(date_purchased,7), SUM(order_total), SUM(order_tax)
FROM orders
GROUP BY LEFT(date_purchased,7);

So how does this work?

Let’s start in the middle. The data that we needed is held in Zen Cart’s orders table, so that’s where I get it “FROM”. If it was spread across two or more tables (a not uncommon situation), then this would be more complicated – but that’s a story for another day. If the table had a database prefix, that would have to be included, e.g. FROM zen_orders.

The last line of this query used the SQL key phrase “GROUP BY”. This tells the database that I’m not looking for individual pieces of data, but rather the results of grouping, in this case, each month’s worth together.

The LEFT(date_purchased,7) function, used in both the first and last lines, is the least obvious part of the query. Dates can be stored in a number of formats within MySQL. Probably the easiest to read is international date format, and this is the one that Zen Cart uses. This means that dates are held as structured strings or text with the following template “YYYY-MM-DD”. So LEFT(date_purchased,7) is extracting the year and month from the date_purchased field (technically known as a database “column”) of the orders table.

The final (and first!) row of the query defines the information that is to be returned and, if you’re using phpMyAdmin or a similar database management tool, displayed in a nice table. By comparison to the first data item, SUM(order_total) and SUM(order_tax) simply add together the all the contents of order_total and order_tax columns that share a common year and month.