Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

SQL Query - Customers & Products


Mort-lemur

Recommended Posts

Posted

Hi,

 

I think the easiest way to achieve this is some form of SQL query to download into a CSV / Excel sheet.

 

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

 

Then I can sort by product and off we go :-)

 

Thanks

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Posted

phpmyadmin, should get you close to what you want, untested:

 

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

Posted

Brilliant - Just what I wanted, worked a treat.

 

Thank You

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Posted

Would be nice to extend this to automate it on a daily basis of those customers who (a year ago, say) ordered XYZ. It's now a year later and an email goes to them lettign them know that they should come back to the site and buy again.

 

Good idea for a modification to osCommerce that is all about getting the customer back, so who wants to make it. Once a person is already a customer I suspect that the odds of them buying again is much higher than trying to find a a completely new customer. If that makes sense.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...