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

[webbusiness23]

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!

[vekia]

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! :-)

[webbusiness23]

I've run the above SQL code on my "ps_customer" table but the result was: MySQL returned an empty result set (i.e. zero rows)

I'm using Presta 1.5.3.1.

 

Should i run the code elsewhere?

[vekia]

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?

[webbusiness23]

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!

[vekia]

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

[webbusiness23]

The IN command works perfectly. 

The C,O and OD things were chosen by you, in order to be able to cycle trough tables? (I'm trying to fully understand the entire code, in order to be able to use it for other purposes in the future ).

[vekia]

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)

[webbusiness23]

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?

[vekia]

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)

[webbusiness23]

Thank you very much for your help!

[vekia]

you're welcome

glad to hear that i could help you in this case

so we have not well explained topic related to what you expected

 

i marked topic title as solved

with regards,

Milos

[jazzin]

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 February 21, 2015 by jazzin (see edit history)