PupStar Posted November 5, 2016 Share Posted November 5, 2016 I have added this addon http://addons.oscommerce.com/info/5907 to give me the ability to hide categories from certain parts of the store I am struggling with integrating this with products_new.php and also the manufacturers box. Can anyone point me in the right direction of which category query to add 'and categories_status = 1' to as I have tried the ones I can find but with no change. I am now think I need to add an extra query or am I over thinking ? Thanks Mark Link to comment Share on other sites More sharing options...
Dan Cole Posted November 5, 2016 Share Posted November 5, 2016 @@PupStar Mark...I don't use that add on but since the query(ies) in products_new.php simply deal with product data and don't depend or use category details I think you are going to have to do a lot more than just add a condition like 'and categories_status = 1'. I think you'll need to determine the category the product appears in and see if the category status is set. I think you'll need to work a little harder for this one. Maybe you can post the actual query so that one of our database wizards can suggest changes to the query that might accomplish that for you. Dan Need help? See this thread and provide the information requested. Is your version of osC up to date? You'll find the latest osC community version (CE Phoenix) here. Link to comment Share on other sites More sharing options...
PupStar Posted November 5, 2016 Author Share Posted November 5, 2016 @@PupStar Mark...I don't use that add on but since the query(ies) in products_new.php simply deal with product data and don't depend or use category details I think you are going to have to do a lot more than just add a condition like 'and categories_status = 1'. I think you'll need to determine the category the product appears in and see if the category status is set. I think you'll need to work a little harder for this one. Maybe you can post the actual query so that one of our database wizards can suggest changes to the query that might accomplish that for you. Dan @@Dan Cole Thanks Dan, I knew its was not going to be an easy one when I started lol This is the query from products_new.php although I am not entirely sure it is that query that should be changed $listing_sql = "select " . $select_column_list . " p.products_id, SUBSTRING_INDEX(pd.products_description, ' ', 20) as products_description, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'"; Regards Mark Link to comment Share on other sites More sharing options...
PupStar Posted November 6, 2016 Author Share Posted November 6, 2016 queries are not my forte but I had a go at changing the producs_new.php query by joining the category table but it just throws an error $listing_sql = "select " . $select_column_list . " p.products_id, SUBSTRING_INDEX(pd.products_description, ' ', 20) as products_description, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join " . TABLE_CATEGORIES . " c using(categories_id) where p.products_id = pd.products_id and products_status = '1' and categories_status = 1 and pd.language_id = '" . (int)$languages_id . "'"; 1054 - Unknown column 'categories_id' in 'from clause'select count(p.products_id) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join specials s on p.products_id = s.products_id left join categories c using(categories_id) where p.products_id = pd.products_id and products_status = '1' and categories_status = 1 and pd.language_id = '1'[TEP STOP] As I previously said I am not even sure I am modifying the right query in the right file lol Link to comment Share on other sites More sharing options...
PupStar Posted November 6, 2016 Author Share Posted November 6, 2016 I have spent hours looking and trying things and I am no bloomin further forward. Any of the gurus available to chip in? Thanks Mark Link to comment Share on other sites More sharing options...
Dan Cole Posted November 6, 2016 Share Posted November 6, 2016 @@PupStar Mark I think you are on the right track and if you keep working at this you'll be one of those wizards. :thumbsup: :) I think the basic problem is that the products category id is not in the categories table but in the product to categories table so you'll need to modify the query to pick up the category id....I have an old query that uses this....it might help... left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id = p2c.products_id left join " . TABLE_CATEGORIES . " c using(categories_id) Dan Need help? See this thread and provide the information requested. Is your version of osC up to date? You'll find the latest osC community version (CE Phoenix) here. Link to comment Share on other sites More sharing options...
PupStar Posted November 6, 2016 Author Share Posted November 6, 2016 @@PupStar Mark I think you are on the right track and if you keep working at this you'll be one of those wizards. :thumbsup: :) I think the basic problem is that the products category id is not in the categories table but in the product to categories table so you'll need to modify the query to pick up the category id....I have an old query that uses this....it might help... Dan @@Dan Cole I was trying the same sort of thing from other queries I found, however yours seem to do the trick on the top level category. Once I had gone in and turned off the sub categories and products it worked like a charm :thumbsup: I have also applied it to the index new products module (w00t) Eternally grateful for giving clarity to my befuddled brain lol :- Mark Mark Link to comment Share on other sites More sharing options...
Dan Cole Posted November 6, 2016 Share Posted November 6, 2016 Wizard status is on the horizon. Well done Mark. :thumbsup: Dan Need help? See this thread and provide the information requested. Is your version of osC up to date? You'll find the latest osC community version (CE Phoenix) here. Link to comment Share on other sites More sharing options...
PupStar Posted November 7, 2016 Author Share Posted November 7, 2016 @@Dan Cole More of a Ronald Weasley than a Harry Potter mate lol Link to comment Share on other sites More sharing options...
PupStar Posted November 7, 2016 Author Share Posted November 7, 2016 @@Dan Cole ok spoke to soon :blush: I am having problems with the code in index.php for the manufacturers, I have modified the query to this // We show them all $listing_sql = "select " . $select_column_list . " p.products_id, SUBSTRING_INDEX(pd.products_description, ' ', 20) as products_description, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id = p2c.products_id left join " . TABLE_CATEGORIES . " c using(categories_id) where c.categories_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'"; } but this produces the error 1054 - Unknown column 'p.products_id' in 'on clause'select count(p.products_id) as total from products p left join specials s on p.products_id = s.products_id, products_description pd, manufacturers m left join products_to_categories p2c on p.products_id = p2c.products_id left join categories c using(categories_id) where c.categories_status = '1' and pd.products_id = p.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '11'[TEP STOP] now am I correct in thinking that this is because the product_id is not in the manufacturers table? and if 'yes' then what is the solution because I honestly have no idea! Regards Mark Link to comment Share on other sites More sharing options...
Dan Cole Posted November 7, 2016 Share Posted November 7, 2016 @@PupStar Mark I don't have time right now to take a proper look at this (on my way out) but at a glance I would say that you have the left join you added in the wrong place. Take a closer look at that. Dan Need help? See this thread and provide the information requested. Is your version of osC up to date? You'll find the latest osC community version (CE Phoenix) here. Link to comment Share on other sites More sharing options...
PupStar Posted November 8, 2016 Author Share Posted November 8, 2016 @@Dan Cole I will take another look this evening Thanks Link to comment Share on other sites More sharing options...
Dan Cole Posted November 8, 2016 Share Posted November 8, 2016 @@PupStar Mark...I'll leave you to it but I have a tip for you. When I struggle with a query I find it helpful to lay it out in this format. $sql = "SELECT DISTINCT c.customers_email_address, c.customers_firstname, c.customers_lastname, c.customers_telephone, c.customers_newsletter FROM ". TABLE_ORDERS . " o INNER JOIN ". TABLE_ORDERS_PRODUCTS ." op ON (o.orders_id = op.orders_id) LEFT JOIN ". TABLE_CUSTOMERS . " c ON (o.customers_id = c.customers_id) LEFT JOIN " . TABLE_ORDERS_STATUS_HISTORY . " s ON (o.orders_id = s.orders_id) INNER JOIN ". TABLE_PRODUCTS ." p ON (op.products_id = p.products_id) LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ON (p2c.products_id = p.products_id) WHERE o.orders_status = s.orders_status_id AND s.orders_status_id = '" . (int)$status . "' AND s.date_added >= '" . $date_from . "' AND s.date_added <= '" . $date_to . "' AND p.products_model LIKE '" . $sku . "' GROUP BY o.orders_id DESC "; Dan Need help? See this thread and provide the information requested. Is your version of osC up to date? You'll find the latest osC community version (CE Phoenix) here. Link to comment Share on other sites More sharing options...
PupStar Posted November 8, 2016 Author Share Posted November 8, 2016 @@PupStar Mark...I'll leave you to it but I have a tip for you. When I struggle with a query I find it helpful to lay it out in this format. $sql = "SELECT DISTINCT c.customers_email_address, c.customers_firstname, c.customers_lastname, c.customers_telephone, c.customers_newsletter FROM ". TABLE_ORDERS . " o INNER JOIN ". TABLE_ORDERS_PRODUCTS ." op ON (o.orders_id = op.orders_id) LEFT JOIN ". TABLE_CUSTOMERS . " c ON (o.customers_id = c.customers_id) LEFT JOIN " . TABLE_ORDERS_STATUS_HISTORY . " s ON (o.orders_id = s.orders_id) INNER JOIN ". TABLE_PRODUCTS ." p ON (op.products_id = p.products_id) LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ON (p2c.products_id = p.products_id) WHERE o.orders_status = s.orders_status_id AND s.orders_status_id = '" . (int)$status . "' AND s.date_added >= '" . $date_from . "' AND s.date_added <= '" . $date_to . "' AND p.products_model LIKE '" . $sku . "' GROUP BY o.orders_id DESC "; Dan @@Dan Cole Cheers mate as soon as I saw the query again I knew what I had done wrong, the query now looks like this // We show them all $listing_sql = "select " . $select_column_list . " p.products_id, SUBSTRING_INDEX(pd.products_description, ' ', 20) as products_description, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id = p2c.products_id left join " . TABLE_CATEGORIES . " c using(categories_id) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m where c.categories_status = '1' and p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'"; } and thanks for the top top in breaking it down to be more manageable :thumbsup: Mark Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.