List of Product Ordered for seasonal promotions

At the osCommerce forum, Heather asked;

What Im basically looking for is a list of customers against what products they ordered, some of my products are seasonal, and I want to be able to email the customers that bought them last year to remind them that they are in stock again.

Ideally:

Product + Customer + email address

My answer was to use phpmyadmin, and run the following SQL query;

[php]SELECT DISTINCT op.products_name, o.customers_name, o.customers_email_address
FROM orders o
LEFT JOIN orders_products op
ON o.orders_id = op.orders_id
ORDER BY op.products_name[/php]

I didn’t test it, but Heather returned to the thread to advise it worked as she wanted it. I’m not all that great at SQL stuff, so was hoping it worked out OK.

Basically, the SQL query is interrogating two osCommerce tables; orders and orders_products. From orders we are getting the “customers name” and the “customers email address”. From orders_products we are getting the “products names” ordered, but because we placed a DISTINCT on that, it only returns one rather than many (as there is no point knowing that Mr XYZ bought 3 of the same item!). Then we link the results from the two tables together, based on the orders_id (which is the same integer in each of the two tables). And finally we present the results ordered by products name.

So, in a real life situation, the output from phpmyadmin would look somewhat similar to this;

Please note I blurred out the email addresses in my graphics program.

What’s next?

I’m thinking that this (obviously with a few amendments) could be made into an automated script, which is run on a daily basis. This script would email each customer to let them know that a year ago (or a month ago or whatever) they ordered XYZ product, and it is now time for them to order again. Completely automated with no additional work needed from the shop owner. Probably make a really good addition to osCommerce, though I guess most shops don’t sell seasonal products?