Jump to content

[SOLVED] How to find all customers who bought a specific product?


Recommended Posts

Hello all!

 

I need to accomplish this on my Prestashop 1.5.3.1:

 

I need to find all customers (their emails) who bought a specific product, in order to send them an email about important updates regarding that specific product.

 

Can you help me out with an SQL query, maybe? (or any other method that works).

 

Thank you!

Link to comment
Share on other sites

for example, if product id = 3 and database prefix = ps_

SELECT C.email FROM ps_customer C 
INNER JOIN ps_orders O on C.id_customer = O.id_customer
INNER JOIN ps_order_detail OD on O.id_order = OD.id_order
WHERE OD.product_id =3

result: email address of customer who bought product id=3 

 

enjoy! :-)

  • Like 1
Link to comment
Share on other sites

yoy have to run this query ANYWHERE you want in your shop database.

 

make sure that you changed product id to any other product which is available in your store.

in addition, you use ps_ prefix too?

 

I've changed the ID of the product with an existing one and now it works perfectly.

(I don't know why i assumed that the ID=3 does exist on my DB) :)

 

Can I ask for an improvement of the above code? 

Can you update the code in order to let me find the emails of customers who bought products that are listed in an array?

For example, not only product_id=3, but also product_id=45, for example.

 

So, at the end, i need to have all the emails of the customers who bought product_id=3 OR product_id=45.

I suppose that the modification of the code is at the final part, OD.product_id=3 OR OD.product_id=45 ? Something like that?

 

Thank you a million times!

Link to comment
Share on other sites

I've changed the ID of the product with an existing one and now it works perfectly.

(I don't know why i assumed that the ID=3 does exist on my DB) :)

 

Can I ask for an improvement of the above code? 

Can you update the code in order to let me find the emails of customers who bought products that are listed in an array?

For example, not only product_id=3, but also product_id=45, for example.

 

So, at the end, i need to have all the emails of the customers who bought product_id=3 OR product_id=45.

I suppose that the modification of the code is at the final part, OD.product_id=3 OR OD.product_id=45 ? Something like that?

 

Thank you a million times!

 

yes you can use OD.product_id=3 OR OD.product_id=45

you can also use IN command:

SELECT C.email FROM ps_customer C 
INNER JOIN ps_orders O on C.id_customer = O.id_customer
INNER JOIN ps_order_detail OD on O.id_order = OD.id_order
WHERE OD.product_id IN (3,45,47,94,102)

3,45,47,94,102 - products id numbers

  • Like 1
  • Thanks 1
Link to comment
Share on other sites

C, O and OD are aliases for these tables.

 

C.email means "email" field from "ps_customer" table (C is an alias of ps_customers table)

O.id_customer means "id_customer" field from "ps_orders" table (O is an alias of ps_orders table)

OD.product_id means field product_id from ps_order_detail table (OD is an alias of ps_order_detail table)

Link to comment
Share on other sites

C, O and OD are aliases for these tables.

 

C.email means "email" field from "ps_customer" table (C is an alias of ps_customers table)

O.id_customer means "id_customer" field from "ps_orders" table (O is an alias of ps_orders table)

OD.product_id means field product_id from ps_order_detail table (OD is an alias of ps_order_detail table)

Got it.

 

Now i have to figure out how to "connect" tables one to another, in order to be able to get the information i need. 

Is there a "technique" for that?

Link to comment
Share on other sites

you define connections "JOINS" based on table fields

INNER JOIN ps_orders O on C.id_customer = O.id_customer

this query connects "ps_orders" table based on id_customer field from two tables (ps_customer alias C, ps_orders alias O)

Link to comment
Share on other sites

  • 10 months later...

for example, if product id = 3 and database prefix = ps_

SELECT C.email FROM ps_customer C 
INNER JOIN ps_orders O on C.id_customer = O.id_customer
INNER JOIN ps_order_detail OD on O.id_order = OD.id_order
WHERE OD.product_id =3

result: email address of customer who bought product id=3 

 

enjoy! :-)

 

This code works perfectly! Is there anyway to export the customers first name along with their email?

 

Maybe something along these lines:

SELECT C.firstname, C.email FROM ps_customer C 
INNER JOIN ps_orders O on C.id_customer = O.id_customer
INNER JOIN ps_order_detail OD on O.id_order = OD.id_order
WHERE OD.product_id =3

EDIT: Just tested the above on my test server, and it executed perfectly.

Edited by jazzin (see edit history)
Link to comment
Share on other sites

×
×
  • Create New...