Export a list of all clients who have made a purchase between a date range

[Thane]

Hi Team,

 

I need a little SQL help please (or if there is an easier way feel free to fill me in).

 

I need to export a list of all my clients including: first name, last name, phone number, email, last purchase, last purchase date.

 

I have been digging around but having issues finding what tables each are included in. I have found the clients name and email under ps_customer but no phone number. I have also found an order history with client ID that I can link back to ps_customer via id_customer but that only shows me total cost of the order not what was purchased.

 

I am pretty new to sql its self so I really appreciate the help.

 

Thanks,

[SITOLOG - F Bugnet]

Hi,

 

There is a nice module doing exactly this (and a bit more as you can also filter by the products or categories or products purchased): "Who has purchased this"

 

Rgds

Franck

[Thane]

Thanks Franck but currently I have no money in my budget so I am trying to do this on my own. I really dont need anything super tricky or pretty just he client's name, purchase history (with dates), email, and phone number. That may be easier than having to filter by date.

 

Please any help would be appreciated.

[ademEklabul]

Try exporting your tables ps_orders , then filter the date field so that you're in your timespan , then you'll have the ID of the clients that made purchases on said dates, then you can use the ps_customers* tables to find the names and adresses, maybe you'll have to go through ps_adresses too not too sure. It can all be done using phpmyadmin and MS excel .

[Thane]

Ya right now I am running the following queries to get the information but the problem is I then need to match it in excel and there are quite a few missing entries since not all clients entered phone numbers. Anyone have a grasp on sql and can combine them to all come out together I would be greatly appreciated.

 

 

select id_customer, firstname, lastname, phone, phone_mobile from ps_address

 

select id_customer as customer id, email from ps_customer

 

select in_customer, id_order from ps_order_return

 

select id_order, product_name from ps_order_detail

 

select id_order, id_customer, invoice_date from ps_orders