Jump to content
Sign in to follow this  
bhbilbao

[Contribution] Filter special offer by category

Recommended Posts

When filtering:

 

1054 - Unknown column 'p2c.categories_id' in 'where clause'

select count(*) as total from products p, products_description pd, specials s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '3' and s.status = '1' and p2c.categories_id=11

[TEP STOP]

 

My categories:

categories_id categories_image parent_id sort_order date_added last_modified

901 0 1 08/12/2009 0:43 08/12/2009 0:50

910 0 10 08/12/2009 0:43 04/01/2010 11:23

911 0 11 08/12/2009 0:43 05/01/2010 11:50

912 0 12 08/12/2009 0:43 08/12/2009 0:50

913 0 13 08/12/2009 0:43 08/12/2009 0:50

914 0 14 08/12/2009 0:43 08/12/2009 0:50

915 0 15 08/12/2009 0:43 08/12/2009 0:50

916 0 16 08/12/2009 0:43 08/12/2009 0:50

917 0 17 08/12/2009 0:43 08/12/2009 0:50

918 0 18 08/12/2009 0:43 08/12/2009 0:50

919 0 19 08/12/2009 0:43 08/12/2009 0:50

902 0 2 08/12/2009 0:43 08/12/2009 0:50

920 0 20 08/12/2009 0:43 08/12/2009 0:50

921 0 21 08/12/2009 0:43 08/12/2009 0:50

922 0 22 08/12/2009 0:43 08/12/2009 0:50

923 0 23 08/12/2009 0:43 08/12/2009 0:50

924 0 24 08/12/2009 0:43 04/01/2010 10:17

925 0 25 08/12/2009 0:43 08/12/2009 0:50

926 0 26 08/12/2009 0:43 08/12/2009 0:50

927 0 27 08/12/2009 0:43 08/12/2009 0:50

903 0 3 08/12/2009 0:43 08/12/2009 0:50

904 0 4 08/12/2009 0:43 08/12/2009 0:50

905 0 5 08/12/2009 0:43 04/01/2010 11:23

906 0 6 08/12/2009 0:43 08/12/2009 0:50

907 0 7 08/12/2009 0:43 08/12/2009 0:50

908 0 8 08/12/2009 0:43 08/12/2009 0:50

909 0 9 08/12/2009 0:43 08/12/2009 0:50

4 901 4 08/12/2009 0:43 13/12/2009 20:27

99 909 99 08/12/2009 0:43 08/12/2009 0:50

Share this post


Link to post
Share on other sites

I installed this today and it does not work for me, either. The dropdown menu appears fine on specials.php and is populated with all of my categories. But, when I select a category to filter the specials list with, I get this:

 

1109 - Unknown table 'p2c' in where clause

select count(*) as total from products p, products_description pd, specials s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and s.status = '1' and p2c.categories_id=33

 

I use osc2.2-MS2...PHP 5.2.9 and MySQL 4.1.22. The contrib offers the following query in catalog/specials.php to accomplish this filtering:

 

$specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1'";
 if($categories_id > 0)
 	$specials_query_raw .= " and p2c.categories_id=".(int)$categories_id;
 $specials_query_raw .= " order by s.specials_date_added DESC";

 

Can any of you troubleshoot what may be wrong with the query? I'm thinking that p2c isn't 'defined' in the query. This would be fabulous if it worked.

 

- Andrea

Share this post


Link to post
Share on other sites

Try this, probably a long winded way of doing it but it works for me.

 

FIND

 

$specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1'";

if($categories_id > 0)

$specials_query_raw .= " and p2c.categories_id=".(int)$categories_id;

$specials_query_raw .= " order by s.specials_date_added DESC";

 

 

REPLACE WITH

 

if($categories_id > 0) {

$specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_SPECIALS . " s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' and p.products_id = p2c.products_id and p2c.categories_id=".(int)$categories_id." order by s.specials_date_added DESC";

} else {

$specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by s.specials_date_added DESC";;

}


You can kill the King, but you can't kill the King Riddim...

Share this post


Link to post
Share on other sites

Thank you FOD. That worked for me too. :)

 

Is it possible to filter by categories. ex. I have category called Car. This Car category has alot of sub-categories like Brakes, Tires and windshields and so on... Is it possible if you choose Car in the drop down menu, to list all the specials in Brakes, Tires and Windshields at once ?

Share this post


Link to post
Share on other sites

Thanks from me, too. :thumbsup: That works!

 

I have the same concern as 87up. Is it possible to have that query filter specials when selecting the main category (even though there are no products actually in it) rather than have to select the sub-categories below them to find the specials?

 

Either way, this is terrific help you gave, FOD, Thank You!

 

- Andrea

Share this post


Link to post
Share on other sites

Monkeying with the query and I'm getting somewhere. At present, the specials will only show when you click on the category they're actually listed in. For example, if you have the following category setup:

 

People Magazine
         The 90's
              1991
              1992
              1993

 

The item on special in year 1993 will only appear onscreen when you select 1993 from the dropdown. If you select The 90's you get zero results. Same with the main parent category of People Magazine.

 

I've changed the query, and this enables you to see the specials for 1993 when selecting either 1993 or The 90's. Replace what FOD offered with this:

 

if (isset($categories_id)){
         if (empty($categories_id) && $categories_id==""){
               $specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by s.specials_date_added DESC";
       }
       else{
               $specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s, " . TABLE_PRODUCTS_TO_CATEGORIES . " pcat, " . TABLE_CATEGORIES . " cat where p.products_status = '1' and p.products_id = pcat.products_id and pcat.categories_id = cat.categories_id and ((cat.categories_id = " . $categories_id . " or cat.parent_id = " . $categories_id . ")) and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by s.specials_date_added DESC";
       }
 }

 

Still can't figure out how to select the main category of People Magazine and have it display specials for all the cats beneath it, but...it's better than nothing! Perhaps some query-gurus out there can take a shot at it?

 

- Andrea

Share this post


Link to post
Share on other sites

Hi

 

Great contrib, but is it possible only to list top categories in the list?

 

Eg. I have

 

 

Car

  • Brakes
  • Wheels
  • Windows

 

I only want to show "Car" and not "Brakes, Wheels, Windows" etc.

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
Sign in to follow this  

×