Jump to content
PupStar

Hide Categories & Products in Bootstrap Edge

Recommended Posts

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


Bootstrap 3 ROCKS!

(or it will if I ever get my head around it lol)

(and I think I have now (w00t) )

Share this post


Link to post
Share on other sites

@@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

Share this post


Link to post
Share on other sites

@@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


Bootstrap 3 ROCKS!

(or it will if I ever get my head around it lol)

(and I think I have now (w00t) )

Share this post


Link to post
Share on other sites

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


Bootstrap 3 ROCKS!

(or it will if I ever get my head around it lol)

(and I think I have now (w00t) )

Share this post


Link to post
Share on other sites

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


Bootstrap 3 ROCKS!

(or it will if I ever get my head around it lol)

(and I think I have now (w00t) )

Share this post


Link to post
Share on other sites

@@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

Share this post


Link to post
Share on other sites

@@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


Bootstrap 3 ROCKS!

(or it will if I ever get my head around it lol)

(and I think I have now (w00t) )

Share this post


Link to post
Share on other sites

@@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

Edited by PupStar

Bootstrap 3 ROCKS!

(or it will if I ever get my head around it lol)

(and I think I have now (w00t) )

Share this post


Link to post
Share on other sites

@@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

Share this post


Link to post
Share on other sites

@@Dan Cole

 

I will take another look this evening

 

Thanks


Bootstrap 3 ROCKS!

(or it will if I ever get my head around it lol)

(and I think I have now (w00t) )

Share this post


Link to post
Share on other sites

@@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

Share this post


Link to post
Share on other sites

@@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


Bootstrap 3 ROCKS!

(or it will if I ever get my head around it lol)

(and I think I have now (w00t) )

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×