steve-doherty Posted October 23, 2013 Share Posted October 23, 2013 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 More sharing options...
♥kymation Posted October 23, 2013 Share Posted October 23, 2013 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 More sharing options...
steve-doherty Posted October 23, 2013 Author Share Posted October 23, 2013 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 More sharing options...
♥kymation Posted October 23, 2013 Share Posted October 23, 2013 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 More sharing options...
steve-doherty Posted October 23, 2013 Author Share Posted October 23, 2013 Got it! Thank you very much for your help. I appreciate it. Steve Link to comment Share on other sites More sharing options...
wiese Posted January 5, 2014 Share Posted January 5, 2014 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 More sharing options...
Oza Posted January 6, 2014 Share Posted January 6, 2014 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 More sharing options...
burt Posted January 6, 2014 Share Posted January 6, 2014 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 More sharing options...
wiese Posted February 5, 2014 Share Posted February 5, 2014 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.