Jump to content

Archived

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

steve-doherty

export list of customers who bought from a certain category

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

Share this post


Link to post
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.

Share this post


Link to post
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");

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
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 :)

Share this post


Link to post
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://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.


This is a signature that appears on all my posts.  
IF YOU MAKE A POST REQUESTING HELP...please state the exact version
of osCommerce that you are using. THANKS

 
Get the latest Responsive osCommerce CE (community edition) here

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×