export list of customers who bought from a certain category

[steve-doherty 0]

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

[♥kymation 631]

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

 

Regards

Jim

[steve-doherty 0]

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

[♥kymation 631]

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

[steve-doherty 0]

Got it! Thank you very much for your help. I appreciate it.

 

Steve

[wiese 0]

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

[Oza 1]

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

[burt 5,612]

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://forums.oscommerce.com/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.

[wiese 0]

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