webbusiness23 Posted April 21, 2014 Share Posted April 21, 2014 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 More sharing options...
vekia Posted April 21, 2014 Share Posted April 21, 2014 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! :-) 1 Link to comment Share on other sites More sharing options...
webbusiness23 Posted April 21, 2014 Author Share Posted April 21, 2014 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? Link to comment Share on other sites More sharing options...
vekia Posted April 21, 2014 Share Posted April 21, 2014 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? Link to comment Share on other sites More sharing options...
webbusiness23 Posted April 21, 2014 Author Share Posted April 21, 2014 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 More sharing options...
vekia Posted April 21, 2014 Share Posted April 21, 2014 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 1 1 Link to comment Share on other sites More sharing options...
webbusiness23 Posted April 21, 2014 Author Share Posted April 21, 2014 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 ). Link to comment Share on other sites More sharing options...
vekia Posted April 21, 2014 Share Posted April 21, 2014 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 More sharing options...
webbusiness23 Posted April 21, 2014 Author Share Posted April 21, 2014 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 More sharing options...
vekia Posted April 21, 2014 Share Posted April 21, 2014 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 More sharing options...
webbusiness23 Posted April 21, 2014 Author Share Posted April 21, 2014 Thank you very much for your help! Link to comment Share on other sites More sharing options...
vekia Posted April 21, 2014 Share Posted April 21, 2014 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 Link to comment Share on other sites More sharing options...
jazzin Posted February 21, 2015 Share Posted February 21, 2015 (edited) 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) Link to comment Share on other sites More sharing options...
Recommended Posts