Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

export list of customers who bought from a certain category


steve-doherty

Recommended Posts

I am trying to export a list of customer names/emails that purchased products from a certain category. I tried the below SQL query, but the error is "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$query = tep_db_query("SELECT o.orders_id , ' at line 1".

 

I think I am close, anybody know how I can make this work?

 

Here's what I tried:

$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

, ptc.products_id

, c.categories_id

, ptc.categories_id

FROM

orders AS o, orders_products AS op, categories AS c, products_to_categories AS ptc

WHERE

op.products_id = ptc.products_id AND c.categories_id = ptc.categories_id AND c.categories_id = 40");

 

 

I know the following works if I know the product number, (73 in this example), but there's too many products in the category to do this one product at a time, I really want the customers who purchased anything in a particular category.

 

 

$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");

 

So I tried to alter this to lookup the products category, but I got the SQL syntax error. Anybody know how to do this?

 

Thanks very much

Link to comment
Share on other sites

You have nothing linking the orders table to the others. Add AND op.orders_id = o.orders_id to your WHERE clause.

 

Regards

Jim

See my profile for a list of my addons and ways to get support.

Link to comment
Share on other sites

That makes sense Jim, thanks! I missed that. However, I still get the error.

 

I used:

 

$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

, ptc.products_id

, c.categories_id

, ptc.categories_id

FROM

orders AS o, orders_products AS op, categories AS c, products_to_categories AS ptc

WHERE

op.products_id = ptc.products_id AND c.categories_id = ptc.categories_id AND c.categories_id = 40 AND op.orders_id = o.orders_id");

Link to comment
Share on other sites

I tested that and got no error. The error message you are getting looks like you tried to paste the PHP code into your database admin tool. That won't work. If you want to use the query in your database tool, use just the part inside the double quotes.

 

Regards

Jim

See my profile for a list of my addons and ways to get support.

Link to comment
Share on other sites

  • 2 months later...

Hi There,

 

I need the same feature, but i´m not an Expert so my question is, where to get an installation-file that can do the same?

And where can I find such a script?

 

Stephan

Link to comment
Share on other sites

Hi There,

 

I need the same feature, but i´m not an Expert so my question is, where to get an installation-file that can do the same?

And where can I find such a script?

 

Stephan

That would be useful :)

Link to comment
Share on other sites

You can use the code directly in phpmyadmin. If you need to set up some sort of report in the admin area, that is also possible, but is a lot more in-depth. It might already exist as an addon, have you searched addons.oscommerce.com

 

If you need to get it made, you can ask for quotes at http://www.oscommerce.com/forums/forum/79-commercial-support-enquiries/ - just because you ask for a quote does not mean you have to accept it, and if you do accept a quote maybe you will then share the resulting code into the Addons area.

Link to comment
Share on other sites

  • 5 weeks later...

Hi there again,

 

I´ve tried this script, but got this error.

Could somebody help me quickly. My DB Name is nbgf.dk

 

Error = #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$query = tep_db_query("SELECT o.orders_id , ' at line 1

 

Script I used.

 

$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

, ptc.products_id

, c.categories_id

, ptc.categories_id

FROM

orders AS o, orders_products AS op, categories AS c, products_to_categories AS ptc

WHERE

op.products_id = ptc.products_id AND c.categories_id = ptc.categories_id AND c.categories_id = 40 AND op.orders_id = o.orders_id");

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...