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!