How to export list of customers who bought certain product?

[NewBudda]

Hi,

we need to do an urgent recall of one of our products. To do this, I need to extract a list of all the customers who bought a certain product (article no. xyx) complete with adress and email. I am sure this can be done but searching here didn't help. Could someonepoint me in the right direction?

Thank you!

[hughesca]

Run this SQL query to get a list of customers who ordered the specific product:

 

SELECT
         o.orders_id
       , o.customers_id
       , o.customers_name
       , o.customers_company 
       , o.customers_street_address 
       , o.customers_suburb
       , o.customers_city
       , o.customers_postcode
       , o.customers_state
       , o.customers_country
       , o.customers_telephone 
       , o.customers_email_address
	, op.products_id
	, op.orders_id
FROM
	orders AS o, orders_products AS op
WHERE
	op.orders_id = o.orders_id AND op.products_id = 73;

 

Replace 73 with the product_id of the item you are recalling. If a customer ordered more than 1 of the item, you'll get a duplicate line for each one in the order. ;)

 

Hope that helps!

 

Peace,

Chris

[NewBudda]

Wow thank you that is a huge help!

[NewBudda]

Wow, that really helped! Thank you!

I have a script which I use to export all customers. Could I change this:

 

<?php

 require('includes/application_top.php');
 $export = '';
 $query = tep_db_query("SELECT customers_firstname, customers_lastname, customers_email_address FROM " . TABLE_CUSTOMERS . " WHERE customers_newsletter = 1");
  while($nempf = tep_db_fetch_array($query)) {
   $export .= $nempf['customers_firstname']. ';' . $nempf['customers_lastname']. ';' . $nempf['customers_email_address'] . "\n";
  }

 header("Content-type: application/vnd.ms-excel");
 header("Content-disposition: attachment; filename=customers_newsletters.csv");
 header("Expires: 0");
 echo $export;
 die();

?>

to the things you wrote?

[hughesca]

Sure, change:

 

$query = tep_db_query("SELECT customers_firstname, customers_lastname, customers_email_address FROM " . TABLE_CUSTOMERS . " WHERE customers_newsletter = 1");

 

to

 

$query = tep_db_query("SELECT
         o.orders_id
       , o.customers_id
       , o.customers_name
       , o.customers_company 
       , o.customers_street_address 
       , o.customers_suburb
       , o.customers_city
       , o.customers_postcode
       , o.customers_state
       , o.customers_country
       , o.customers_telephone 
       , o.customers_email_address
               , op.products_id
               , op.orders_id
FROM
               orders AS o, orders_products AS op
WHERE
               op.orders_id = o.orders_id AND op.products_id = 73");

[NewBudda]

Thank you! Very helpful!

I did this:

<?php

 require('includes/application_top.php');
 $export = '';
 $query = tep_db_query("SELECT
         o.orders_id
       , o.customers_id
       , o.customers_name
       , o.customers_company 
       , o.customers_street_address 
       , o.customers_suburb
       , o.customers_city
       , o.customers_postcode
       , o.customers_state
       , o.customers_country
       , o.customers_telephone 
       , o.customers_email_address
               , op.products_id
               , op.orders_id
FROM
               orders AS o, orders_products AS op
WHERE
               op.orders_id = o.orders_id AND op.products_id = 147");
  while($nempf = tep_db_fetch_array($query)) {
   $export .= $nempf['customers_firstname']. ';' . $nempf['customers_lastname']. ';' . $nempf['customers_email_address'] . "\n";
  }

 header("Content-type: application/vnd.ms-excel");
 header("Content-disposition: attachment; filename=customers_produkte.csv");
 header("Expires: 0");
 echo $export;
 die();

?>

The first and last names are not inserted, but thats ok i am in a hurry. Maybe someone else will polish this and share it? Its a nice little script!

[sevenfifty]

The first and last names are not inserted, but thats ok i am in a hurry. Maybe someone else will polish this and share it? Its a nice little script!

 

 

Corrected code to display name, email, phone and open correctly in Excel. Just change the product ID in this line

op.orders_id = o.orders_id AND op.products_id = 29");

to the correct one for your database

 

<?php
 require('includes/application_top.php');
 $export = '';
 $query = tep_db_query("SELECT
	  o.orders_id
	, o.customers_id
	, o.customers_name
	, o.customers_company
	, o.customers_street_address
	, o.customers_suburb
	, o.customers_city
	, o.customers_postcode
	, o.customers_state
	, o.customers_country
	, o.customers_telephone
	, o.customers_email_address
			, op.products_id
			, op.orders_id
FROM
			orders AS o, orders_products AS op
WHERE
			op.orders_id = o.orders_id AND op.products_id = 29");
  while($nempf = tep_db_fetch_array($query)) {
$export .= $nempf['customers_name']. ',' . $nempf['customers_email_address']. ',' . $nempf['customers_telephone'] . "\n";
  }
 header("Content-type: application/vnd.ms-excel");
 header("Content-disposition: attachment; filename=customers_produkte.csv");
 header("Expires: 0");
 echo $export;
 die();
?>